SQL course · Module 4: Joins · Lesson 14 of 40
Quick answer: A SQL self join joins a table to itself, with two different aliases, so you can compare rows of the same table: FROM customers c1 JOIN customers c2 ON c2.city = c1.city AND c1.id < c2.id finds customers who live in the same city. A CROSS JOIN pairs every row of one table with every row of another. A semi-join (EXISTS) keeps rows that have at least one match, and an anti-join (NOT EXISTS) keeps rows that have none. Avoid NOT IN with a subquery that can return NULL: if it does, NOT IN returns no rows at all.
In this lesson you will learn:
- how to write a SQL self join, and how to avoid pairing a row with itself
- what CROSS JOIN does and when it is useful
- how
EXISTSfinds rows with a match without creating duplicates - how
NOT EXISTSfinds rows without a match - why
NOT INbreaks when the subquery returns a NULL
Before you start: Lesson 13, SQL LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN and Lesson 6, SQL NULL Values: IS NULL, COALESCE and Three-Valued Logic.
We ran every example on PostgreSQL 16.15 with the shop database, and checked the current PostgreSQL 18 manual. NULLs are shown as (null) with \pset null '(null)' from Lesson 6.
Table of Contents
SQL self join: join a table to itself
Which customers live in the same city? Both people are in the customers table, so we join customers to customers. If you try it with the plain table name, PostgreSQL stops you:
SELECT * FROM customers JOIN customers ON customers.city = customers.city;
ERROR: table name "customers" specified more than once
Each copy needs its own alias. The PostgreSQL manual says table aliases “are mainly for notational convenience, but it is necessary to use them when joining a table to itself” (PostgreSQL docs: Table and Column Aliases). Call them c1 and c2:
SELECT c1.id, c1.name, c2.id AS other_id, c2.name AS other_name, c1.city
FROM customers c1
JOIN customers c2 ON c2.city = c1.city
ORDER BY c1.id, c2.id;
id | name | other_id | other_name | city
----+--------------+----------+--------------+---------
1 | Ayesha Malik | 1 | Ayesha Malik | Lahore
1 | Ayesha Malik | 10 | Omar Haddad | Lahore
2 | Lucas Martin | 2 | Lucas Martin | Toronto
3 | Sofia Rossi | 3 | Sofia Rossi | Milan
4 | bilal ahmed | 4 | bilal ahmed | Karachi
5 | Emma Clarke | 5 | Emma Clarke | London
6 | Daniel Weber | 6 | Daniel Weber | Berlin
8 | NOAH WILSON | 8 | NOAH WILSON | Chicago
9 | Chloe Dubois | 9 | Chloe Dubois | Paris
10 | Omar Haddad | 1 | Ayesha Malik | Lahore
10 | Omar Haddad | 10 | Omar Haddad | Lahore
(11 rows)
It is a normal inner join, and three things are wrong with the answer:
- Every customer matches themselves, because their city equals their own city.
- The one real pair, Ayesha and Omar in Lahore, appears twice, once in each direction.
- Hira Siddiqui (customer 7) is missing. Her city is NULL, and
NULL = NULLis not true, so she matches no one, not even herself.
Add c1.id < c2.id to the ON condition. A row is never smaller than itself, and of the two directions only one has the smaller id first:
SELECT c1.name, c2.name AS other_name, c1.city
FROM customers c1
JOIN customers c2 ON c2.city = c1.city AND c1.id < c2.id
ORDER BY c1.id, c2.id;
name | other_name | city
--------------+-------------+--------
Ayesha Malik | Omar Haddad | Lahore
(1 row)
The same pattern works on any table. Here are orders placed on the same day (the tie you sorted in Lesson 7):
SELECT o1.id AS order_id, o2.id AS other_order, o1.order_date
FROM orders o1
JOIN orders o2 ON o2.order_date = o1.order_date AND o1.id < o2.id
ORDER BY o1.id;
order_id | other_order | order_date
----------+-------------+------------
14 | 15 | 2026-09-02
(1 row)
Self joins also handle hierarchies, such as an employees table where manager_id points to another employee. Long hierarchies are easier with recursive CTEs (Lesson 22).
CROSS JOIN: every combination
A CROSS JOIN has no ON. It pairs every row of the first table with every row of the second. The manual calls this “a Cartesian product”: “If the tables have N and M rows respectively, the joined table will have N * M rows.” With 10 customers and 10 products:
SELECT count(*) AS combinations FROM customers CROSS JOIN products;
combinations
--------------
100
(1 row)
A small part of it, two customers and the two Stationery products:
SELECT c.name AS customer, p.name AS product
FROM customers c
CROSS JOIN products p
WHERE c.id <= 2 AND p.category = 'Stationery'
ORDER BY c.id, p.id;
customer | product
--------------+------------------
Ayesha Malik | Notebook Pack
Ayesha Malik | Gel Pens 10-Pack
Lucas Martin | Notebook Pack
Lucas Martin | Gel Pens 10-Pack
(4 rows)
The useful case is a report that must show every combination, even ones that never happened. Build the grid with CROSS JOIN, attach the real data with LEFT JOIN (Lesson 13), and total it with GROUP BY from Lesson 10. How many units of each Stationery product did each customer in Pakistan buy?
SELECT c.name AS customer, p.name AS product, coalesce(sum(oi.quantity), 0) AS units
FROM customers c
CROSS JOIN products p
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items oi ON oi.order_id = o.id AND oi.product_id = p.id
WHERE c.country = 'Pakistan' AND p.category = 'Stationery'
GROUP BY c.id, c.name, p.id, p.name
ORDER BY c.id, p.id;
customer | product | units
---------------+------------------+-------
Ayesha Malik | Notebook Pack | 2
Ayesha Malik | Gel Pens 10-Pack | 4
bilal ahmed | Notebook Pack | 0
bilal ahmed | Gel Pens 10-Pack | 3
Hira Siddiqui | Notebook Pack | 3
Hira Siddiqui | Gel Pens 10-Pack | 7
Omar Haddad | Notebook Pack | 0
Omar Haddad | Gel Pens 10-Pack | 0
(8 rows)
All 4 × 2 = 8 combinations are there. Only 5 of them were ever bought; the three zero rows come from the grid.
Be careful: a cross join grows fast. Two tables of 10,000 rows give 100 million rows. An accidental one, such as FROM orders, customers without a WHERE condition, is a classic bug (Lesson 12).
Semi-join with EXISTS: rows that have a match
Which customers have bought at least one Stationery product? A join gives an answer, but with repeats:
SELECT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE p.category = 'Stationery'
ORDER BY c.id;
id | name
----+---------------
1 | Ayesha Malik
1 | Ayesha Malik
2 | Lucas Martin
3 | Sofia Rossi
4 | bilal ahmed
5 | Emma Clarke
7 | Hira Siddiqui
7 | Hira Siddiqui
7 | Hira Siddiqui
8 | NOAH WILSON
8 | NOAH WILSON
(11 rows)
As Lesson 12 showed, a join repeats a customer once per matching order line. Hira bought Stationery on three lines, so she appears three times. DISTINCT would hide the repeats, but the question was only “is there at least one?”. That is a semi-join, and EXISTS says it directly:
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = c.id AND p.category = 'Stationery'
)
ORDER BY c.id;
id | name
----+---------------
1 | Ayesha Malik
2 | Lucas Martin
3 | Sofia Rossi
4 | bilal ahmed
5 | Emma Clarke
7 | Hira Siddiqui
8 | NOAH WILSON
(7 rows)
The query in brackets is a subquery (Lesson 20 covers them in depth). It uses c.id from the outer query, so logically it is checked once for each customer. From the manual: “If it returns at least one row, the result of EXISTS is ‘true'”. The SELECT 1 does not matter, because “the output list of the subquery is normally unimportant”, and the check “will generally only be executed long enough to determine whether at least one row is returned” (PostgreSQL docs: Subquery Expressions).
WHERE c.id IN (SELECT o.customer_id FROM ...) with the same joins gave the same 7 customers, so IN is another way to write a semi-join.
Anti-join with NOT EXISTS: rows with no match
Put NOT in front, and you get the rows that have no match. Customers with no orders:
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
id | name
----+-------------
10 | Omar Haddad
(1 row)
This is the same answer as LEFT JOIN ... WHERE o.id IS NULL from Lesson 13. Both are anti-joins; NOT EXISTS states the question more plainly. Products never ordered work the same way:
SELECT p.id, p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);
id | name
----+-----------------
9 | Ergonomic Chair
(1 row)
Here NOT IN also works: WHERE p.id NOT IN (SELECT oi.product_id FROM order_items oi) returned the Ergonomic Chair too. It is safe only because product_id is NOT NULL.
The NOT IN and NULL trap
In Lesson 6 you saw that NOT IN returns no rows when its list contains a NULL. With a subquery, the NULL can arrive without you noticing. Say we want customers who live outside every city that has a shipped order. These are the shipped orders’ customers:
SELECT s.id, s.name, s.city
FROM orders o
JOIN customers s ON s.id = o.customer_id
WHERE o.status = 'shipped'
ORDER BY s.id;
id | name | city
----+---------------+---------
2 | Lucas Martin | Toronto
6 | Daniel Weber | Berlin
7 | Hira Siddiqui | (null)
(3 rows)
Hira has no city, so the list is Toronto, Berlin and NULL. Now NOT IN:
SELECT c.id, c.name, c.city
FROM customers c
WHERE c.city NOT IN (
SELECT s.city
FROM orders o
JOIN customers s ON s.id = o.customer_id
WHERE o.status = 'shipped'
)
ORDER BY c.id;
id | name | city
----+------+------
(0 rows)
No error, just an empty result. The manual explains: “if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true.” WHERE keeps only true rows, so every customer is dropped. The PostgreSQL wiki’s “Don’t Do This” page warns that “NOT IN behaves in unexpected ways if there is a null present” and recommends NOT EXISTS instead (PostgreSQL wiki: Don’t Do This):
SELECT c.id, c.name, c.city
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
JOIN customers s ON s.id = o.customer_id
WHERE o.status = 'shipped' AND s.city = c.city
)
ORDER BY c.id;
id | name | city
----+---------------+---------
1 | Ayesha Malik | Lahore
3 | Sofia Rossi | Milan
4 | bilal ahmed | Karachi
5 | Emma Clarke | London
7 | Hira Siddiqui | (null)
8 | NOAH WILSON | Chicago
9 | Chloe Dubois | Paris
10 | Omar Haddad | Lahore
(8 rows)
Lucas and Daniel are excluded. Notice Hira: her own city is unknown, so s.city = c.city never matches and NOT EXISTS keeps her. If unknown cities should not count, add AND c.city IS NOT NULL to the outer WHERE. Adding AND s.city IS NOT NULL inside the subquery also fixes the NOT IN version; we got 7 rows, Hira not included. Our SQLite 3.53.1 test showed the same trap.
Try it yourself
Exercise 1: Customers from the same country
List every pair of customers who live in the same country, each pair once.
Exercise 2: Products in cancelled orders
Use EXISTS to list the products that appear in at least one cancelled order.
Exercise 3: Customers who never bought Stationery
Use NOT EXISTS to list the customers who have never bought a Stationery product.
Solutions
Solution 1
SELECT c1.name, c2.name AS other_name, c1.country
FROM customers c1
JOIN customers c2 ON c2.country = c1.country AND c1.id < c2.id
ORDER BY c1.id, c2.id;
name | other_name | country
---------------+---------------+----------
Ayesha Malik | bilal ahmed | Pakistan
Ayesha Malik | Hira Siddiqui | Pakistan
Ayesha Malik | Omar Haddad | Pakistan
bilal ahmed | Hira Siddiqui | Pakistan
bilal ahmed | Omar Haddad | Pakistan
Hira Siddiqui | Omar Haddad | Pakistan
(6 rows)
Four customers from Pakistan make 6 pairs; every other country has one customer, so no pair.
Solution 2
SELECT p.id, p.name
FROM products p
WHERE EXISTS (
SELECT 1
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE oi.product_id = p.id AND o.status = 'cancelled'
)
ORDER BY p.id;
id | name
----+--------------
4 | Laptop Stand
10 | HD Webcam
(2 rows)
The subquery links each order line to its product with oi.product_id = p.id.
Solution 3
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = c.id AND p.category = 'Stationery'
)
ORDER BY c.id;
id | name
----+--------------
6 | Daniel Weber
9 | Chloe Dubois
10 | Omar Haddad
(3 rows)
These are exactly the 3 customers missing from the 7 found with EXISTS.
Common mistakes
- A self join without aliases. You get
ERROR: table name "customers" specified more than once. Give each copy its own alias. - Forgetting
c1.id < c2.id. Every row matches itself and every pair appears twice: 11 rows instead of 1. - Using NOT IN with a subquery that can return NULL. One NULL makes the result silently empty. Use
NOT EXISTS. - Forgetting to link the subquery to the outer row.
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.status = 'cancelled')returned all 10 customers, because some cancelled order exists no matter which customer you look at. Addo.customer_id = c.id. - Using a join to ask “is there at least one?”. The join repeats rows (11 instead of 7 above). Use
EXISTS.
Frequently asked questions
Is a self join different from an inner join?
A self join is not a separate join type. It is a normal join (inner or outer) where both sides are the same table, so each side needs its own alias. You can write LEFT JOIN customers c2 too, if you want to keep rows with no partner.
Is NOT EXISTS faster than NOT IN?
Often, in PostgreSQL. The wiki page above explains that the planner cannot turn NOT IN (SELECT ...) into an anti-join, so it can become very slow on big tables. In our EXPLAIN on the shop data, NOT EXISTS became a “Hash Right Anti Join”, and NOT IN a filter with a “hashed SubPlan”. NOT EXISTS is also correct with NULLs, so prefer it. Lesson 32 teaches EXPLAIN.
When should I use a CROSS JOIN?
Use it when you really want every combination: a grid of customers and products, every size with every colour, or every date with every store, usually followed by a LEFT JOIN to the real data. If you see a huge, repeated result you did not expect, check for a missing join condition first.
Summary
- A SQL self join joins a table to itself with two aliases; add
c1.id < c2.idto drop self-matches and repeated pairs. - CROSS JOIN returns every combination (N × M rows); with LEFT JOIN it builds complete report grids with zeros.
EXISTSis a semi-join: it keeps each row that has at least one match, without the repeats a join creates.NOT EXISTSis an anti-join: it keeps rows with no match, likeLEFT JOIN ... IS NULL.NOT INreturns no rows if the subquery returns a NULL; useNOT EXISTSinstead.
Comparing rows of the same table comes back in the SQL window functions practice problems, where LAG and LEAD compare each sale with the previous or next one. Then try these patterns on the datasets in the data engineering projects list.
Next lesson
Next: Lesson 15, SQL UNION vs UNION ALL, INTERSECT and EXCEPT.
Sources
- PostgreSQL tutorial: Joins Between Tables (self join)
- PostgreSQL documentation: Table Expressions (CROSS JOIN, table aliases)
- PostgreSQL documentation: Subquery Expressions (EXISTS, IN, NOT IN)
- PostgreSQL wiki: Don’t Do This (Don’t use NOT IN)





