PostgreSQL - How to select rows that don't exist in other table?
Select rows that are present in one table but missing from the other table
You have two tables:
- Table
customers:
| id | name | order_id |
|---|---|---|
| 1 | Anne | 8347923 |
| 2 | Bob | 9238471 |
| … | … | … |
- Table
payment:
| order_id | amount |
|---|---|
| 9238000 | 800 |
| 0234992 | 230 |
| … | … |
Now you want to list out all the customers that have not paid, identified by order_id. List all rows in customers which do not have a corresponding row in payment.
1. The fastest way is to check NOT EXIST:
SELECT
c.*
FROM
customers c
WHERE
NOT EXISTS (
SELECT
1
FROM
payment p
WHERE
c.order_id = p.order_id);
2. Or you can use NOT IN:
SELECT
c.*
FROM
customers c
WHERE
c.order_id NOT IN(
SELECT
order_id FROM payment p);
3. Or LEFT JOIN:
SELECT
c.*
FROM
customers c
LEFT JOIN payment p ON c.order_id = p.order_id
WHERE
p.order_id IS NULL;
Need a good GUI tool for PostgreSQL? Check out TablePlus. It’s native, beautiful, and available for free.
