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?

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.