CODEWITHSUNDEEP
sql
Some code wizard
Some code wizard

Reputation: 69

SQL Get rows that doesn't appear in another table

I have this SQL problem: I have tables A and B. Table A has columns id and name, Table B amount and id which is a foreign key to table A.id.

I need to return all table A rows that don't have their id stored in table B. Any ideas?

So the complete opposite is:

SELECT *
FROM a
LEFT OUTER JOIN b ON a.id = b.id;

Here row what I need is left out of result

Upvotes: 1

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Just add a where clause:

SELECT a.*
FROM a LEFT OUTER JOIN
     b
     ON a.id = b.id
WHERE b.id IS NULL;

You can also use NOT EXISTS:

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

In most databases, the two methods typically have similar performance.

Upvotes: 4

Related Questions