mike_grinin mike_grinin - 11 months ago 53
SQL Question

SQL: join with OR in condition

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 Source

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