CODEWITHSUNDEEP
sqlpostgresqlsubquery
bca
bca

Reputation: 474

Throwing exception when subquery returns null

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?

Upvotes: 0

Views: 158

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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.

Upvotes: 2

Related Questions