sql - Throwing exception when subquery returns null

Question

I have 3 tables; devices, floors and info. Both devices and info has a foreign key to floors When I'm trying to get info by device I'm using following query:

SELECT field1, field2 FROM info 
WHERE info.floor_id = (SELECT floor_id FROM devices WHERE device_uuid = "foo")

If there is no such device, subquery becomes NULL and I don't get any results. When that happens, I can't tell if that's because there is no such device or there is no info in place.

So is there any way like throwing an exception if subquery returns null?

question from:https://stackoverflow.com/questions/65829759/throwing-exception-when-subquery-returns-null

Answer 1

You can use a LEFT JOIN starting with the devices table:

SELECT i.field1, i.field2, d.floor_id
FROM devices d LEFT JOIN
     info i 
     ON i.floor_id = d.floor_id
WHERE d.device_uuid = 'foo'

What does this return?

• If there is a match between the tables, then it returns the rows with a non-NULL third column.
• If there is a device in devices, but no match in info, then it returns a row with the first two columns as NULL.
• If there is no device in devices, then it returns no rows.

I think this satisfies what you need -- to distinguish these three cases.