mysql Return even if inner join returns no result

7xllpg7q 于 2022-12-22 发布在 Mysql

关注(0)|答案(2)|浏览(133)

Good day!
I have the following tables:

devices (id, device_name, device_uid, device_type, account_id)
device_data (id_device, timestamp, value1, value2, value3)

My query:

SELECT * 
FROM devices AS d 
INNER JOIN (SELECT * 
            FROM device_data 
            WHERE timestamp IN (SELECT MAX(timestamp) 
                                FROM device_data)) AS dd ON d.id = dd.id_device

With the above query I get all devices and their last entry from the device_data table. But if the device has no values in the device_data table yet, this is not captured by the query. But I expect an output like this:
| id | device_name | device_uid | device_type | account_id | id_device | timestamp | value1 | value2 | value3 |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | device1 | uid1 | type1 | 1 | 1 | some time | some value | some value | saome value |
| 2 | device2 | uid2 | type1 | 1 | NULL | NULL | NULL | NULL | NULL |
So if there is no data in device_data for device2, the query should capture the device anyway and output the non-existing value from device_data as NULL.

mysql

来源：https://stackoverflow.com/questions/74842783/return-even-if-inner-join-returns-no-result

2条答案

按热度按时间

fhity93d1#

you need LEFT JOIN, to get all device records in output, with all device_data records, if any

select * from devices d left join device_data dd on dd.id_device=d.id

but also I see from your query, that you want to select only latest row from device_data for each device, so you should do it like this:

select *
from devices d
left join device_data dd on dd.id_device_id=d.id
where not exists (select 1 from device_data where id_device=dd.id_device and 
timestamp>dd.timestamp)

(you can place this "not exists" condition in ON clause as well)

赞(0）回复(0）举报 2022-12-22

u0njafvf2#

In the meantime I have come to the following result. This query is much faster (0.0022s vs. 0.1945) than the one mentioned by @kergma.

SELECT * FROM devices AS d 
LEFT JOIN (SELECT * FROM device_data d WHERE d.timestamp = (SELECT max(d2.timestamp) FROM device_data d2 WHERE d2.id_device=d.id_device))
AS dd ON d.id = dd.id_device;

赞(0）回复(0）举报 2022-12-22

我来回答

mysql Return even if inner join returns no result

2条答案

相关问题

热门标签

最新问答