SQL joins: inner, left, anti-join and self-join

Joins are the most common topic in SQL rounds. The question sounds like this: "List every customer with the number of orders" or "Which customers never ordered?" The answer needs two tables. The interviewer watches one thing in particular: do you keep the rows that have no match, or lose them without noticing?

A weak answer uses one kind of join for everything. It also does not check how many rows come out. A strong answer chooses the join from the words of the question. It says what happens to rows with no match, and it checks the row count after each join.

At SDE-1 level you should write an inner join and a left join. At SDE-2 level you should also find rows with no match and compare rows of one table with each other. You should explain why numbers can repeat after a join.

A customer's name is stored once, in the customers table. An order does not repeat the name. It keeps only a customer_id, which is the id of one row of customers. That column is a foreign key, a column that points to the primary key of another table (lesson 34). To see an order with the customer name, you must put the two tables side by side. A join does this: it builds one result by matching rows of two tables.

INNER JOIN: only the rows that match

JOIN ... ON takes each row of the first table and finds the rows of the second table that pass the test after ON. Only matched pairs stay in the result. Rows with no partner are dropped. JOIN alone means INNER JOIN.

A short name after a table is a table alias. We write o for orders and c for customers. A qualified name such as o.id says which table a column comes from. It is needed when both tables have a column with the same name, such as id. Without it, SQLite stops with an "ambiguous column" error.

SELECT o.id, c.name, o.amount
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
WHERE o.id <= 9;

id  name   amount
--  -----  ------
 1  Asha      440
 2  Ravi      200
 3  Meena     360
 4  Asha      440
 5  Karan     600
 6  Sana      340
 7  Ravi      400
 9  Meena     300

Order 8 is missing. It is a guest order, so it has no customer to match. An inner join dropped it.

SELECT id, name FROM orders JOIN customers ON orders.customer_id = customers.id;

Error: ambiguous column name: id

How many rows does a join make?

Count before you trust a join.

• One to many: one customer has many orders. Joining customers to orders gives one row for each order. The "many" side sets the count.
• Many to many: the rows multiply. If a customer is stored with 3 partners in a table of pairs, the customer appears 3 times in the join.
• No ON: every row meets every row. This is a cross join. With 24 orders and 9 customers the result has 24 x 9 = 216 rows.

SELECT o.id, c.name FROM orders AS o CROSS JOIN customers AS c ORDER BY o.id, c.id LIMIT 4;

id  name
--  -----
 1  Asha
 1  Ravi
 1  Meena
 1  Karan

SELECT o.id FROM orders AS o CROSS JOIN customers AS c;

Without LIMIT the query makes 216 rows. When a join gives far more rows than you expect, the ON test is missing or wrong.

You can join three or more tables in a chain. Each JOIN ... ON adds one table to the result so far.

SELECT o.id, c.name AS customer, r.name AS restaurant, o.amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN restaurants AS r ON r.id = o.restaurant_id
WHERE o.id IN (1, 4, 9);

id  customer  restaurant   amount
--  --------  -----------  ------
 1  Asha      Spice Route     440
 4  Asha      Roti Ghar       440
 9  Meena     Dosa Corner     300

LEFT JOIN: keep every row of the left table

LEFT JOIN keeps every row of the table on the left of the word JOIN. If a row has no partner, the columns of the right table are NULL. Now the guest order stays:

SELECT o.id, c.name, o.amount
FROM orders AS o
LEFT JOIN customers AS c ON o.customer_id = c.id
WHERE o.id IN (7, 8, 9);

id  name   amount
--  -----  ------
 7  Ravi      400
 8  NULL      210
 9  Meena     300

Swap the sides, and we see every customer, with or without orders.

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.id >= 7
ORDER BY c.id, o.id;

name   order_id
-----  --------
Priya        19
Priya        23
Dev        NULL
Neha       NULL

Dev and Neha never ordered. They appear with NULL in order_id.

Where does the extra test go? A test on the right table in WHERE runs after the join. A NULL row fails the test, so it is dropped, and the left join turns back into an inner join. If the test is only about which orders may match, put it in ON. We want every customer, with their cancelled order if they have one:

SELECT c.name, o.id AS cancelled_order
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'cancelled'
ORDER BY c.id;

name    cancelled_order
------  ---------------
Asha               NULL
Ravi               NULL
Meena              NULL
Karan                 5
Sana                 12
Vikram             NULL
Priya                19
Dev                NULL
Neha               NULL

With the same test in WHERE, only 3 customers would remain.

The anti-join: rows with no match

"Customers who never ordered" means: left join, then keep the rows where the right side is NULL. We test the right table's key, which cannot be NULL when a match exists.

SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

id  name
--  ----
 8  Dev
 9  Neha

The choice of join comes from the question. "Orders with their customer" is inner. "All customers, even without orders" is left. "Customers without orders" is left plus IS NULL.

Customers on the left, orders on the right. Priya has two orders, 19 and 23, so two lines join her to them. Dev and Neha have no order, so no line joins them and a cross marks the missing match. An inner join drops Dev and Neha. A left join keeps them with NULL in the order column. An anti-join keeps only them.

Other joins

RIGHT JOIN keeps every row of the right table, and FULL OUTER JOIN keeps both sides. SQLite has had them since version 3.39. Most people write a left join with the two tables swapped, because it reads the same way every time. CROSS JOIN is useful for a small list, when you want every pair on purpose, for example every restaurant with each of 3 time slots.

Self-join: one table, two roles

A table can be joined to itself. Here are the 9 employees. manager_id is the id of the manager, and the top person has none.

SELECT * FROM employees;

id  name          manager_id  department   salary  bonus
--  ------------  ----------  -----------  ------  -----
 1  Rohit Mehra         NULL  Management   200000   NULL
 2  Anita Rao              1  Engineering  150000  20000
 3  Sameer Khan            1  Sales        120000   NULL
 4  Deepa Iyer             2  Engineering  110000  10000
 5  Imran Shaikh           2  Engineering   95000   NULL
 6  Pooja Nair             2  Engineering  160000  15000
 7  Kabir Das              3  Sales         80000   5000
 8  Latha Menon            3  Sales         80000   NULL
 9  Tarun Joshi            4  Engineering   70000   NULL

Give the table two aliases, as if it were two tables. In employees, manager_id holds the id of another employee. So one alias plays the employee and the other plays the manager.

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id
WHERE e.salary > m.salary;

employee    salary  manager    manager_salary
----------  ------  ---------  --------------
Pooja Nair  160000  Anita Rao          150000

Pooja earns more than her manager Anita. Rohit has no manager, so an inner join drops him. Use LEFT JOIN to keep him.

To compare rows in pairs, join a table to itself with a.id < b.id. This keeps A-B once, drops B-A, and never pairs a row with itself. Pairs in the Sales department:

SELECT a.name AS first, b.name AS second
FROM employees AS a
JOIN employees AS b ON a.department = b.department AND a.id < b.id
WHERE a.department = 'Sales';

first        second
-----------  -----------
Sameer Khan  Kabir Das
Sameer Khan  Latha Menon
Kabir Das    Latha Menon

Joins that are not equal

ON can hold any test. Two time ranges overlap when each starts before the other ends. Two bookings that touch (one ends at 22:00, the next starts at 22:00) do not overlap. Here is a small table of table bookings. We add it for this lesson. Run the next block in your own SQLite tool; the Tradeoff runner only reads data.

CREATE TABLE table_bookings (id INTEGER PRIMARY KEY, restaurant_id INTEGER, guest TEXT, starts_at TEXT, ends_at TEXT);
INSERT INTO table_bookings VALUES
  (1, 1, 'Asha',  '2026-03-20 19:00', '2026-03-20 21:00'),
  (2, 1, 'Karan', '2026-03-20 20:30', '2026-03-20 22:00'),
  (3, 1, 'Sana',  '2026-03-20 22:00', '2026-03-20 23:00'),
  (4, 2, 'Ravi',  '2026-03-20 19:30', '2026-03-20 20:30');

SELECT a.guest AS guest_a, b.guest AS guest_b
FROM table_bookings AS a
JOIN table_bookings AS b ON a.restaurant_id = b.restaurant_id AND a.id < b.id
                        AND a.starts_at < b.ends_at AND b.starts_at < a.ends_at;

guest_a  guest_b
-------  -------
Asha     Karan

Only Asha and Karan overlap. Karan and Sana touch at 22:00, so the strict < keeps them out. The same idea puts a value in a band, such as an amount between a low and a high limit.

A join table: many to many

A customer can like many restaurants, and a restaurant can have many fans. We store each pair in a join table, a table of two foreign keys (lesson 34). To read it you join through it. We add this table too; run the next block in your own SQLite tool.

CREATE TABLE favourites (customer_id INTEGER, restaurant_id INTEGER, PRIMARY KEY (customer_id, restaurant_id));
INSERT INTO favourites VALUES (1, 1), (1, 2), (2, 4), (3, 6), (3, 7), (5, 4), (5, 5);

SELECT c.name AS customer, r.name AS restaurant
FROM favourites AS f
JOIN customers AS c ON c.id = f.customer_id
JOIN restaurants AS r ON r.id = f.restaurant_id
WHERE r.id = 4 OR c.id = 1
ORDER BY c.id, r.id;

customer  restaurant
--------  ------------
Asha      Spice Route
Asha      Roti Ghar
Ravi      Bombay Bites
Sana      Bombay Bites

The fan-out trap

An order has several item lines in order_items. This table has 44 rows, so we show it in three parts.

SELECT * FROM order_items WHERE order_id <= 8;

order_id  item              qty  price
--------  ----------------  ---  -----
       1  Butter Chicken      1    320
       1  Butter Naan         3     40
       2  Pav Bhaji           1    140
       2  Vada Pav            2     30
       3  Misal Pav           2    130
       3  Sabudana Khichdi    1    100
       4  Dal Tadka           1    180
       4  Paneer Tikka        1    260
       5  Dal Makhani         2    240
       5  Jeera Rice          1    120
       6  Puran Poli          1     90
       6  Veg Thali           1    250
       7  Misal Pav           1    120
       7  Pav Bhaji           2    140
       8  Aloo Tikki          1     90
       8  Pani Puri           2     60

SELECT * FROM order_items WHERE order_id BETWEEN 9 AND 16;

order_id  item              qty  price
--------  ----------------  ---  -----
       9  Filter Coffee       2     50
       9  Idli Plate          1     80
       9  Masala Dosa         1    120
      10  Butter Chicken      2    320
      11  Dahi Bhalla         1    110
      11  Pani Puri           1     60
      12  Veg Thali           1    250
      13  Misal Pav           2    130
      13  Sabudana Khichdi    1    100
      14  Puran Poli          1     90
      14  Veg Thali           1    250
      15  Misal Pav           1    120
      15  Vada Pav            4     30
      16  Butter Chicken      1    320
      16  Butter Naan         2     40

SELECT * FROM order_items WHERE order_id > 16;

order_id  item              qty  price
--------  ----------------  ---  -----
      17  Filter Coffee       2     50
      17  Masala Dosa         2    120
      18  Misal Pav           1    130
      18  Sabudana Khichdi    2    100
      19  Veg Thali           1    250
      20  Paneer Tikka        2    260
      20  Tandoori Roti       4     20
      21  Dal Tadka           2    180
      22  Idli Plate          2     80
      22  Masala Dosa         1    120
      23  Misal Pav           1    120
      23  Pav Bhaji           1    140
      24  Vada Pav            5     30

A join to the lines repeats the columns of the order once for each line. Order 9 has 3 lines, so its amount of Rs 300 appears 3 times.

SELECT o.id, o.amount, i.item, i.qty, i.price
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE o.id = 9;

id  amount  item           qty  price
--  ------  -------------  ---  -----
 9     300  Filter Coffee    2     50
 9     300  Idli Plate       1     80
 9     300  Masala Dosa      1    120

If you add up the amount column of this result, you get 3 x 300 = 900. The true amount is 300. Add up amounts only on a result that has one row for each order.

In Postgres and MySQL

• USING (col) is a short form of ON a.col = b.col for a column with the same name in both tables. It works in all three.
• MySQL has no FULL OUTER JOIN. Postgres and SQLite 3.39 or newer have it.
• Postgres needs ON after JOIN (a bare JOIN is a syntax error). SQLite and MySQL accept it and make a cross join.
• A join on text follows the case rule of each database (lesson 42), so the same join can match more rows in MySQL.

The Tiffin Town owner asks five questions. Each one needs a different join.

Question 1: "Show each order with the customer name." Inner join. Check the row count: 24 orders, 1 guest order, so we expect 23 rows. Run the full query in your own tool and read the row count it shows.

SELECT o.id, c.name, o.amount
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
ORDER BY o.id DESC LIMIT 3;

id  name    amount
--  ------  ------
24  Ravi       150
23  Priya      260
22  Vikram     280

SELECT o.id FROM orders AS o JOIN customers AS c ON o.customer_id = c.id;

The full join has 23 rows, as expected.

Question 2: "Show every order, even a guest order, with the city of the customer." Left join from orders.

SELECT o.id, c.name, c.city
FROM orders AS o
LEFT JOIN customers AS c ON o.customer_id = c.id
WHERE o.id BETWEEN 7 AND 9;

id  name   city
--  -----  ------
 7  Ravi   Mumbai
 8  NULL   NULL
 9  Meena  Pune

Order 8 stays, with NULL for the name and the city.

Question 3: "Which customers never ordered, and from which city?" Left join from customers, and keep the rows where the order key is NULL.

SELECT c.name, c.city
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

name  city
----  ---------
Dev   Delhi
Neha  Bengaluru

Dev and Neha are the answer. This also answers the predict box. With JOIN, Dev is dropped, because no order matches. With LEFT JOIN, Dev stays and the order columns are NULL.

Question 4: "List every employee with the name of the manager, and say who earns more than the manager." A self-join. It must be a left join, or the top person is lost.

SELECT e.name AS employee, m.name AS manager,
  CASE WHEN e.salary > m.salary THEN 'earns more' ELSE '' END AS note
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.id
WHERE e.id IN (1, 2, 6, 9);

employee     manager      note
-----------  -----------  ----------
Rohit Mehra  NULL
Anita Rao    Rohit Mehra
Pooja Nair   Anita Rao    earns more
Tarun Joshi  Deepa Iyer

Rohit has no manager, so the manager name is NULL. Pooja earns Rs 160,000 and her manager Anita earns Rs 150,000, so she gets the note. For Rohit the test e.salary > m.salary is unknown, so CASE goes to ELSE.

Question 5: "How much did we sell?" A trap. We join orders to items and look at the lines of order 9.

SELECT o.id, o.amount, i.item
FROM orders AS o JOIN order_items AS i ON i.order_id = o.id
WHERE o.id IN (9, 10);

id  amount  item
--  ------  --------------
 9     300  Filter Coffee
 9     300  Idli Plate
 9     300  Masala Dosa
10     640  Butter Chicken

Order 9 appears 3 times, and order 10 once. Adding up o.amount here would count Rs 300 three times and give 900 + 640 = 1,540. The real total is 300 + 640 = 940. Add the money from the lines (i.qty * i.price).

• It solves: answers that need facts from several tables, with a clear choice of which unmatched rows stay.
• It costs: a join is easy to get subtly wrong. The wrong kind loses rows. A wrong ON makes too many rows. A join on columns without an index is slow (lesson 36).
• I would change it when: the same join is in every query and the tables are huge. Then I check the query plan, and I may keep a combined copy of the data that the app updates (lesson 35). That is a design change, so I name its cost: the copy can go out of date.
• Say the join type aloud and say why: "left, because the guest orders must stay."

"LEFT JOIN with a WHERE on the right table still keeps the unmatched rows." It does not. The unmatched rows have NULL in the right columns, and WHERE o.status = 'cancelled' is not true for NULL. They are dropped. Put the test in ON.

"A join always makes one row for each row of the first table." Only when each row has at most one partner. If a row has 3 partners, it appears 3 times, as order 9 does with its 3 item lines.

"To find rows without a match, I can test = NULL." Use IS NULL on a column of the right table that is never NULL in a real match, such as its key.

1. Which join keeps every row of the left table? What shows in the right columns when there is no match?
2. Two tables have 40 rows and 25 rows. You run a join with no ON. How many rows come out? An inner join on a key follows, and each of the 40 rows matches at most one of the 25. How many rows can it give at most?
3. A query lists every restaurant and its cancelled orders: FROM restaurants r LEFT JOIN orders o ON o.restaurant_id = r.id WHERE o.status = 'cancelled'. Restaurants without a cancelled order are missing. Fix it.