Lesson 3 of 14 · 8 min · Combining tables
Joins: combining tables with INNER, LEFT and self joins
What you will learn
- Why a database splits data into several tables
INNER JOIN,LEFT JOINand how to read their results- Finding rows that have no match (an anti-join)
- Joining a table to itself
- Why a join can multiply rows, and the
ONversusWHEREquestion
In simple words
A shop keeps one list of customers and a separate list of orders, so a customer's name is stored once, not on every order. A join is how you glue the two lists back together when you need both: "show each order with the name of the customer who placed it". You tell SQL which column in one list matches which column in the other.
Words to know
- Join: combining rows from two tables where a condition matches.
- Primary key: the column that uniquely identifies each row, such as
customer_id. - Foreign key: a column that points to another table's primary key, such as
orders.customer_id. - Alias: a short temporary name for a table, such as
cforcustomers.
INNER JOIN
Use the practice tables from lesson 1.
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;
name | order_id | amount
-------+----------+---------
Asha | 101 | 1200.00
Asha | 102 | 800.00
Ravi | 103 | 1500.00
Meena | 104 | 600.00
Meena | 105 | 900.00
Asha | 106 | 500.00
(The row order is not guaranteed without ORDER BY.)
INNER JOIN (or just JOIN) keeps only rows that have a match on both sides. Kiran and Divya have no orders, so they disappear. The ON clause says how rows match. Aliases (c, o) keep the query short; write c.name so it is clear which table a column belongs to, and so two columns with the same name do not clash.
LEFT JOIN
LEFT JOIN keeps every row from the left table, and fills the right side with NULL where nothing matches.
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;
name | order_id
-------+----------
Asha | 101
Asha | 102
Asha | 106
Ravi | 103
Meena | 104
Meena | 105
Kiran |
Divya |
Eight rows: the six matches plus one row each for Kiran and Divya. RIGHT JOIN is the mirror image; most people just swap the table order and use LEFT JOIN. FULL OUTER JOIN keeps unmatched rows from both sides, and CROSS JOIN pairs every row with every row (use it deliberately, for example to build a calendar of every customer by every month).
Finding rows with no match
The most useful left-join pattern: keep the rows that matched nothing.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
This returns Kiran and Divya: customers who never ordered. Test a column that cannot be NULL in a real match (the primary key of the right table). NOT EXISTS gives the same answer and is equally good.
ON versus WHERE with a left join
For an inner join, a condition in ON or WHERE gives the same rows. For a left join they differ:
-- keeps every customer, shows only their paid orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid'
-- WHERE removes customers who have no paid order, so it behaves like an inner join
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
Put conditions about the right table in ON when you want to keep unmatched left rows.
Joining a table to itself
A table can reference itself, for example an employee and their manager:
CREATE TABLE employees (
emp_id INT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(30),
salary INT, manager_id INT REFERENCES employees(emp_id));
INSERT INTO employees VALUES
(1,'Neha','Engineering',90000,NULL), (2,'Arun','Engineering',70000,1),
(3,'Sana','Engineering',65000,1), (4,'Ravi','Sales',50000,NULL),
(5,'Meena','Sales',55000,4), (6,'Kiran','Sales',55000,4);
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;
Neha and Ravi show a NULL manager (they are at the top). Use two different aliases for the same table: that is what makes a self join readable. We will reuse employees in the window-function lessons.
The duplicate-row trap
A join can return more rows than either table when one row matches several. Joining customers to orders gives one row per order, which is right if you want orders but wrong if you then count customers:
SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.customer_id; -- 6, not 3
Use COUNT(DISTINCT c.customer_id) to count customers (3), or aggregate orders first and then join. If you join three tables and one-to-many relationships stack, sums can be repeated many times; we revisit this in the application-SQL lesson.
Common mistakes
- Forgetting the
ONclause, which in some databases produces a cross join and millions of rows. - Joining on the wrong columns (matching
order_idtocustomer_id). Always join key to the key it references. - Filtering the right table in
WHEREafter aLEFT JOIN, silently turning it into an inner join. - Counting after a join without realising rows were multiplied.
- Ambiguous column names (
customer_idexists in both tables); prefix with the alias.
Try it yourself
- List every order with the customer's name and city.
- List every customer with their number of orders, including those with zero (hint:
LEFT JOINandCOUNT(o.order_id)). - Find customers who have never placed an order, two ways.
- Create
employeesand list each employee with their manager's name. - Show each customer's paid orders only, but keep customers without any (put the status test in
ON).
Check yourself
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row of the left table, with NULLs on the right where there is no match.
How do you find customers with no orders?
LEFT JOIN orders and keep rows WHERE o.order_id IS NULL, or use NOT EXISTS.
Why can adding a join increase the row count?
Each left row appears once per matching right row, so a customer with three orders appears three times.
Where do you put a condition on the right table to keep unmatched left rows?
In the ON clause, not in WHERE.
Interview questions
Explain the types of joins with an example.
INNER keeps matching rows only. LEFT and RIGHT keep all rows from one side. FULL OUTER keeps all rows from both sides. CROSS pairs every row with every row. For customers and orders: inner shows customers who ordered; left shows all customers including those who did not.
What is a self join and when do you use it?
Joining a table to itself under two aliases, used for hierarchies such as employee and manager, or for comparing rows within one table, such as finding pairs.
How would you find duplicate customers by email?
SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1; to see the rows themselves, join the table to that result on email.
Summary
- Joins match rows on a key: primary key to foreign key.
INNERkeeps matches;LEFTkeeps all left rows;IS NULLon the right finds missing matches.- Conditions on the right table go in
ONfor a left join. - One-to-many joins multiply rows; count with
DISTINCTor aggregate first.
Going deeper
- Joined tables: the PostgreSQL manual on join types.
- JOIN clause in MySQL: MySQL syntax and options.
- Tutorial: joins between tables: a short worked introduction.
See how much of this you can explain
A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.