CODEWITHSUNDEEP
sqlpostgresqljoin
mike_grinin
mike_grinin

Reputation: 187

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?

Upvotes: 8

Views: 18720

Answers (4)

Andrew Lazarus
Andrew Lazarus

Reputation: 19320

We can drop the Devices table from @kbball's solution

SELECT L.device_id_1
FROM Links L
WHERE L.device_id_2 = 398
   -- AND L.device_id_1!=398 if self-links are possible
UNION
SELECT L.device_id_2
FROM Links L
WHERE L.device_id_1 = 398
   -- AND L.device_id_2!=398 if self-links are possible
;

Upvotes: 0

kjmerf
kjmerf

Reputation: 4345

This one works and does not contain duplicates. Just run two queries and union them together:

SELECT D.id
FROM Devices D
INNER JOIN Links L
ON D.id = L.device_id_1
WHERE D.id <> 398
AND L.device_id_2 = 398
UNION
SELECT D.id
FROM Devices D
INNER JOIN Links L
ON D.id = L.device_id_2
WHERE D.id <> 398
AND L.device_id_1 = 398

Tested here: http://sqlfiddle.com/#!9/e1269/6

Upvotes: 1

Nemeros
Nemeros

Reputation: 425

In my point of view you are trying to do an existance test.

There's two implementation of it, depending of your load.

The second implementation will be better if you've got lot of data, the 1st one is for really low cardinality database where performance isn't a real problem :

SELECT d.* (put the list of the column and not *)
FROM device d
where exists (select 1 from Links l where l.device_id_1 = d.id and l.device_id_2 = 398 OR l.device_id_1 = 398 and l.device_id_2 = d.id)



SELECT d.*
FROM Device d
WHERE EXISTS (SELECT 1 FROM Links l where l.device_id_1 = d.id and l.device_id_2 = 398)
UNION ALL
SELECT d.*
FROM Device d
WHERE EXISTS (SELECT 1 FROM Links l where l.device_id_2 = d.id and l.device_id_1 = 398)

You might want to transform the UNION ALL to an UNION, depending of your data

Upvotes: 1

xQbert
xQbert

Reputation: 35323

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

Upvotes: 9

Related Questions