SQL: How to check if rows of a table doesnt exist in another table?

Question

Currently, I have table A and table B in postgres that have some duplicates, I only want to select the rows that exist in Table A but dont exist in Table B, would this be a form of a left join?

What would be the best way to achieve this?

Use [`EXCEPT`](https://www.postgresql.org/docs/13/sql-select.html#SQL-EXCEPT). — Akina, May 11 '21 at 18:55
Please provide sample data, desired results, and an explanation of what *you* mean by "duplicate". — Gordon Linoff, May 11 '21 at 19:02
`EXISTS()` is the way to go. It is even in your question title! — wildplasser, May 11 '21 at 20:23

score 1 · Answer 1 · answered May 11 '21 at 19:05

i suppose the both table has ID as pk column

there is many way: 1/ select a.id from a where not exists (select 1 from b where b.id=a.id)

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

3/ select id from a minus select id from b

score 1 · Answer 2 · answered May 11 '21 at 19:05

You can do a LEFT OUTER JOIN and check for the items whose JOIN key is NULL.

For example:

TABLE A:
id, name
1,  red
1,  red
2,  blue

TABLE B:
1,  big
3,  small

with a query like this:

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

you will only get "2, blue" that is on table_a but not on table_b

Here is a working fiddle: https://www.db-fiddle.com/f/fMiNwVpgHeCYN6ZyD8ENmD/1

SQL: How to check if rows of a table doesnt exist in another table?

2 Answers2