How do I find records that are not joined?

Question

I have two tables that are joined together.

A has many B

Normally you would do:

select * from a,b where b.a_id = a.id

To get all of the records from a that has a record in b.

How do I get just the records in a that does not have anything in b?

albertein · Accepted Answer · 2008-09-29T23:09:35.310

133

select * from a where id not in (select a_id from b)

Or like some other people on this thread says:

select a.* from a
left outer join b on a.id = b.a_id
where b.a_id is null

edited Sep 29 '08 at 23:09

answered Sep 29 '08 at 22:57

albertein

26,396
5
54
57

1

it's better to use left outer join because "in" could have limitation (depends on db) – Person Aug 02 '22 at 22:18

score 27 · Answer 2 · answered Sep 29 '08 at 23:00

27

select * from a
left outer join b on a.id = b.a_id
where b.a_id is null

answered Sep 29 '08 at 23:00

Joseph Anderson

2,838
2
26
26

I think this left outer join will perform much better than the 'in' clause, unless the query optimizer treats them the same... – Codewerks Sep 29 '08 at 23:09
It pretty much does. Check it out. – Amy B Sep 29 '08 at 23:12
Yeah, interestingly, the query plan has an extra strep (Filter) for the left join and the 'where in' is resolved to a 'right anti semi join'...whatever that is... – Codewerks Sep 29 '08 at 23:14
According to answers in [that question](https://stackoverflow.com/questions/2930033/t-sql-left-outer-joins-filters-in-the-where-clause-versus-the-on-clause), this won't work: "If you filter the left outer joined table in the WHERE clause then you are in effect creating an inner join" – O. R. Mapper Jun 18 '20 at 11:16

score 12 · Answer 3 · answered May 12 '20 at 10:28

12

The following image will help to understand SQL LET JOIN :

answered May 12 '20 at 10:28

Monsif EL AISSOUSSI

2,296
19
17

score 6 · Answer 4 · answered Sep 29 '08 at 23:01

6

Another approach:

select * from a where not exists (select * from b where b.a_id = a.id)

The "exists" approach is useful if there is some other "where" clause you need to attach to the inner query.

answered Sep 29 '08 at 23:01

Matt Hamilton

200,371
61
386
320

score 5 · Answer 5 · answered Mar 13 '12 at 10:42

5

SELECT id FROM a
EXCEPT
SELECT a_id FROM b;

answered Mar 13 '12 at 10:42

onedaywhen

55,269
12
100
138

1

The `EXCEPT` keyword is `MINUS` in Oracle. – onedaywhen Mar 13 '12 at 10:43

score 4 · Answer 6 · answered Sep 29 '08 at 23:05

4

You will probably get a lot better performance (than using 'not in') if you use an outer join:

select * from a left outer join b on a.id = b.a_id where b.a_id is null;

answered Sep 29 '08 at 23:05

nathan

4,571
2
27
28

score 2 · Answer 7 · edited Sep 02 '19 at 19:04

2

SELECT <columnns>
FROM a WHERE id NOT IN (SELECT a_id FROM b)

edited Sep 02 '19 at 19:04

Sebastián Palma

32,692
6
40
59

answered Sep 29 '08 at 22:57

BlackWasp

4,933
2
30
42

2

This will be extremely expensive as the engine has to generate the the subquery in its entirety before it can begin to eliminate the tuples from a. Not a good idea in general. – dland Sep 30 '08 at 11:07

score 1 · Answer 8 · edited Aug 13 '18 at 17:09

1

Another way of writing it

select a.*
from a 
left outer join b
on a.id = b.id
where b.id is null

Ouch, beaten by Nathan :)

edited Aug 13 '18 at 17:09

Community

1
1

answered Sep 29 '08 at 23:06

shahkalpesh

33,172
3
63
88

score 1 · Answer 9 · answered Jan 05 '18 at 15:38

In case of one join it is pretty fast, but when we are removing records from database which has about 50 milions records and 4 and more joins due to foreign keys, it takes a few minutes to do it. Much faster to use WHERE NOT IN condition like this:

select a.* from a
where a.id NOT IN(SELECT DISTINCT a_id FROM b where a_id IS NOT NULL)
//And for more joins
AND a.id NOT IN(SELECT DISTINCT a_id FROM c where a_id IS NOT NULL)

I can also recommended this approach for deleting in case we don't have configured cascade delete. This query takes only a few seconds.

score 1 · Answer 10 · answered Jan 16 '18 at 10:09

The first approach is

select a.* from a where a.id  not in (select b.ida from b)

the second approach is

select a.*
  from a left outer join b on a.id = b.ida
  where b.ida is null

The first approach is very expensive. The second approach is better.

With PostgreSql 9.4, I did the "explain query" function and the first query as a cost of cost=0.00..1982043603.32. Instead the join query as a cost of cost=45946.77..45946.78

For example, I search for all products that are not compatible with no vehicles. I've 100k products and more than 1m compatibilities.

select count(*) from product a left outer join compatible c on a.id=c.idprod where c.idprod is null

The join query spent about 5 seconds, instead the subquery version has never ended after 3 minutes.

score 0 · Answer 11 · answered Sep 29 '08 at 23:14

0

This will protect you from nulls in the IN clause, which can cause unexpected behavior.

select * from a where id not in (select [a id] from b where [a id] is not null)

answered Sep 29 '08 at 23:14

Amy B

108,202
21
135
185

You are better off using a left outer join rather than using an IN predicate in the first place. – dland Sep 30 '08 at 11:07

How do I find records that are not joined?

11 Answers11

Linked

Related