SQL: join with OR in condition

Question

I have 2 tables:

Devices (id (PK))
Links (id (PK), device_id_1 (FK), device_id_2 (FK))

Which represents devices connected by links.

I need to select all devices connected with a given one (which can be device_id_1 or device_id_2). I tried to do it with the following query:

select d2.*
from Devices as d1
left outer join Links as l on d1.id in (l.device_id_1, l.device_id_2)
left outer join Devices as d2 on d2.id in (l.device_id_1, l.device_id_2)
where d1.id = 398 and d2.id <> 398;

But as soon as I added second JOIN the query returns zero rows. What am I doing wrong?

Answer 1

The where clause was effectively making your last left join an inner join.

To correct move the left join filter criteria to the join criteria

select d2.*
from Devices as d1
left outer join Links as l on d1.id in (l.device_id_1, l.device_id_2)
left outer join Devices as d2 on d2.id in (l.device_id_1, l.device_id_2)
and d2.id <> 398
where d1.id = 398;

A much less elegant although generally accepted approach would be...

select d2.*
from Devices as d1
left outer join Links as l on d1.id in (l.device_id_1, l.device_id_2)
left outer join Devices as d2 on d2.id in (l.device_id_1, l.device_id_2)
where d1.id = 398 
  and (d2.id <> 398 OR D2.ID is null)

I generally think of it this way..

When using outer joins I typically want to exclude the rows before the join occurs so the engine doesn't have to generate such a large Cartesian. In addition on outer joins, null records I want returned. However, if I apply the limit in the where clause, all the null records generated from the outer join will be removed unless I account for NULLS as well.

In this case since you're using a <>... <> can't compare to null thus it will exclude desired records as you can't use a equality check on a null value.

1 = NULL returns NULL and 1 <> NULL returns NULL; thus not true