Until now every query read from one table. That is why the GROUP BY & HAVING chapter could only show department numbers like 10 and 20: the employee rows store dept_id, while the department names live in a different table. Real questions almost always need data from several tables: "show each order with the customer's name", "show each employee with their department and office city", "which department has no employees?".
The tool for this is the JOIN. This chapter is the big picture: why joins exist, what a join really does, the syntax, the different types of joins and how MySQL executes them. The following chapters then go deep into each type: INNER JOIN, LEFT, RIGHT & FULL OUTER JOIN, and SELF JOIN & CROSS JOIN.
All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.
Why Is Data Split Across Tables?
Imagine we stored the department name and location inside every employee row:
emp_id name dept_name location
------ ------------- ----------- ---------
1 Aarav Sharma Engineering Bengaluru
2 Priya Iyer Engineering Bengaluru
3 Rohan Mehta Engineering Bengaluru
4 Sneha Kapoor Sales Mumbai
...This looks convenient, but it causes real problems:
Repetition: "Engineering, Bengaluru" is stored three times. With 5,000 engineers it is stored 5,000 times.
Update anomaly: if Engineering moves to Hyderabad, we must update every engineer's row. Miss one and the data contradicts itself.
Insert anomaly: we cannot record the new Research department until it has at least one employee.
Delete anomaly: if Arjun (the only Marketing employee) leaves and we delete his row, we lose the fact that Marketing is in Pune.
So well-designed databases store each fact once, in its own table, and link tables with keys. This design process is called normalization (covered in detail in the DBMS tutorial). In company_db:
departments employees
+---------+-------------+ +--------+--------------+---------+
| dept_id | dept_name | <----- | emp_id | name | dept_id |
| (PK) | | FK | (PK) | | (FK) |
+---------+-------------+ +--------+--------------+---------+
| 10 | Engineering | | 1 | Aarav Sharma | 10 |
| 20 | Sales | | 4 | Sneha Kapoor | 20 |
| ... | ... | | ... | ... | ... |
customers orders
+-------------+------+ +----------+-------------+
| customer_id | name | <-------- | order_id | customer_id |
| (PK) | | FK | (PK) | (FK) |
+-------------+------+ +----------+-------------+employees.dept_id is a foreign key pointing to departments.dept_id, and orders.customer_id points to customers.customer_id. Normalization splits data apart; joins put it back together at query time, exactly the way each question needs it.
What is a Join?
A JOIN combines rows from two (or more) tables into result rows, based on a join condition that says which row of one table goes with which row of the other. Each result row contains columns from both tables.
Here is our first join: every employee together with their department's name and location.
SELECT e.name, d.dept_name, d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;+--------------+-------------+-----------+
| name | dept_name | location |
+--------------+-------------+-----------+
| Aarav Sharma | Engineering | Bengaluru |
| Priya Iyer | Engineering | Bengaluru |
| Rohan Mehta | Engineering | Bengaluru |
| Sneha Kapoor | Sales | Mumbai |
| Vikram Singh | Sales | Mumbai |
| Ananya Das | Sales | Mumbai |
| Karan Patel | HR | Delhi |
| Meera Nair | HR | Delhi |
| Arjun Reddy | Marketing | Pune |
+--------------+-------------+-----------+
9 rows in set (0.00 sec)Read the query like a sentence: "take employees (call it e), join departments (call it d), and pair up rows where e.dept_id equals d.dept_id".
Two things are already worth noticing:
There are 10 employees but only 9 rows. Ishita has
dept_id = NULL, andNULL = anythingis never TRUE, so she has no partner.The Research department (50) does not appear, because no employee has
dept_id = 50.
Whether such "unmatched" rows should be kept or dropped is exactly what distinguishes the different types of joins.
The Foundation: Every Row With Every Row
Conceptually, a join starts from the Cartesian product: every row of the first table paired with every row of the second. Then the join condition keeps only the pairs that belong together.
Take just two employees and three departments:
Step 1: Cartesian product (2 x 3 = 6 pairs)
e.name e.dept_id d.dept_id d.dept_name e.dept_id = d.dept_id ?
------------ --------- --------- ----------- -----------------------
Aarav Sharma 10 10 Engineering TRUE -> keep
Aarav Sharma 10 20 Sales FALSE
Aarav Sharma 10 30 HR FALSE
Karan Patel 30 10 Engineering FALSE
Karan Patel 30 20 Sales FALSE
Karan Patel 30 30 HR TRUE -> keep
Step 2: keep the pairs where the condition is TRUE
Aarav Sharma Engineering
Karan Patel HRFor the full tables, the product has 10 × 5 = 50 pairs. You can see this number by joining with no condition at all:
SELECT COUNT(*) FROM employees CROSS JOIN departments;+----------+
| COUNT(*) |
+----------+
| 50 |
+----------+
1 row in set (0.00 sec)Out of those 50 pairs, only 9 satisfy e.dept_id = d.dept_id. In relational algebra, this is exactly how a join is defined: a join is a selection (σ) applied on a Cartesian product (×), written employees ⋈e.dept_id = d.dept_id departments.
Important: this is the logical meaning. The database does not actually build the full product and then filter it; that would be hopelessly slow for big tables. It uses smarter algorithms (see "How MySQL Executes a Join" below) that produce the same result.
Anatomy of a Join Query
SELECT columns
FROM table1 [AS] alias1
[INNER | LEFT | RIGHT | CROSS] JOIN table2 [AS] alias2
ON join_condition
[WHERE ...]
[GROUP BY ...]
[ORDER BY ...];Table Aliases
employees e gives the table a short nickname. The keyword AS is optional (employees AS e means the same). Aliases keep queries readable, and in a self join (joining a table to itself) they are compulsory, because you need two different names for the same table.
Qualified Column Names
Both tables have a column called dept_id. If you do not say which one you mean, MySQL refuses:
SELECT dept_id, name
FROM employees
JOIN departments ON employees.dept_id = departments.dept_id;ERROR 1052 (23000): Column 'dept_id' in field list is ambiguousWrite e.dept_id or d.dept_id (or the full table name). Columns that exist in only one table, like name here, do not strictly need a prefix, but a good habit is to prefix every column in a join query. Then a reader instantly knows where each value comes from, and the query does not break the day someone adds a name column to departments.
The Join Condition
The ON clause is an ordinary condition. Almost always it compares a foreign key with the primary key it references (e.dept_id = d.dept_id), but SQL does not require that. Any condition is allowed:
Equi-join: the condition uses
=. This is by far the most common kind.Non-equi join (theta join): the condition uses
<,>,BETWEEN,<>and so on, for example matching a salary to a salary band.Conditions can combine several comparisons with
AND/OR.
A foreign key constraint is also not required to join two tables. The constraint protects data integrity; the join only needs columns whose values can be compared.
Types of Joins
Every join type answers one question: what happens to rows that find no partner? In our data there are two such rows: Ishita (an employee with no department) and Research (a department with no employees).
Join type | Keeps | Rows for employees + departments |
|---|---|---|
INNER JOIN | Only matched pairs | 9 |
LEFT (OUTER) JOIN | Matched pairs + unmatched rows of the left table (right side filled with NULL) | 10 (adds Ishita) |
RIGHT (OUTER) JOIN | Matched pairs + unmatched rows of the right table (left side filled with NULL) | 10 (adds Research) |
FULL (OUTER) JOIN | Matched pairs + unmatched rows of both tables | 11 (adds both). Not built into MySQL; emulated |
CROSS JOIN | Every row paired with every row, no condition | 50 |
SELF JOIN | Not a separate keyword: any join of a table with itself | (e.g. employee with manager) |
A text picture of the four "matching" joins, using employees as the left table L and departments as the right table R:
L only matched R only
(Ishita) (9 emp-dept pairs) (Research)
INNER JOIN - YES -
LEFT JOIN YES YES -
RIGHT JOIN - YES YES
FULL OUTER JOIN YES YES YESVenn diagrams are often used for this, and the picture above is the same idea. Just remember that a join works on rows and matches, not on sets of values: one department matching three employees produces three result rows, something a Venn diagram cannot show.
Here is a quick taste of each type. The detailed rules and traps are in the next chapters.
Outer Joins: Keeping the Unmatched Rows
A full outer join keeps everybody. MySQL has no FULL OUTER JOIN keyword, so this result is produced by combining a LEFT JOIN with the unmatched rows of a RIGHT JOIN (explained step by step in the LEFT, RIGHT & FULL OUTER JOIN chapter):
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION ALL
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;+--------------+-------------+
| name | dept_name |
+--------------+-------------+
| Aarav Sharma | Engineering |
| Priya Iyer | Engineering |
| Rohan Mehta | Engineering |
| Sneha Kapoor | Sales |
| Vikram Singh | Sales |
| Ananya Das | Sales |
| Karan Patel | HR |
| Meera Nair | HR |
| Arjun Reddy | Marketing |
| Ishita Gupta | NULL |
| NULL | Research |
+--------------+-------------+
11 rows in set (0.00 sec)The first 9 rows are the inner join. Row 10 is what the LEFT JOIN adds (Ishita, no department, so dept_name is NULL). Row 11 is what the RIGHT JOIN adds (Research, no employee, so name is NULL).
Self Join: A Table Joined With Itself
Each employee's manager_id points to another row of the same employees table. To show manager names, we use the table twice under two aliases:
SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;+--------------+--------------+
| employee | manager |
+--------------+--------------+
| Priya Iyer | Aarav Sharma |
| Rohan Mehta | Aarav Sharma |
| Sneha Kapoor | Aarav Sharma |
| Vikram Singh | Sneha Kapoor |
| Ananya Das | Sneha Kapoor |
| Karan Patel | Aarav Sharma |
| Meera Nair | Karan Patel |
| Arjun Reddy | Aarav Sharma |
| Ishita Gupta | Aarav Sharma |
+--------------+--------------+
9 rows in set (0.00 sec)Aarav is missing from the employee column because he has no manager. The SELF JOIN & CROSS JOIN chapter shows how to keep him.
Three Ways to Write a Join
1. Explicit JOIN ... ON (recommended)
This is the ANSI SQL-92 style used throughout this tutorial. The join condition sits in ON, separate from the filters in WHERE.
2. The Old Comma Style
Before SQL-92, joins were written by listing tables with commas and putting the join condition in WHERE:
SELECT employees.name, departments.dept_name
FROM employees, departments
WHERE employees.dept_id = departments.dept_id
AND departments.dept_id = 30;+-------------+-----------+
| name | dept_name |
+-------------+-----------+
| Karan Patel | HR |
| Meera Nair | HR |
+-------------+-----------+
2 rows in set (0.00 sec)It still works, and you will see it in older code and some exam papers. The problem: the join condition is mixed with the ordinary filters, and if you forget it, you silently get the full Cartesian product instead of an error. The comma style also cannot express outer joins. Prefer explicit JOIN ... ON.
3. USING and NATURAL JOIN
When the join columns have the same name in both tables, USING is a shortcut for the equality:
SELECT emp_id, name, dept_id, dept_name
FROM employees
JOIN departments USING (dept_id)
ORDER BY emp_id
LIMIT 3;+--------+--------------+---------+-------------+
| emp_id | name | dept_id | dept_name |
+--------+--------------+---------+-------------+
| 1 | Aarav Sharma | 10 | Engineering |
| 2 | Priya Iyer | 10 | Engineering |
| 3 | Rohan Mehta | 10 | Engineering |
+--------+--------------+---------+-------------+
3 rows in set (0.00 sec)With USING, the two dept_id columns merge into one, so the unqualified dept_id is no longer ambiguous.
NATURAL JOIN goes one step further: it joins automatically on all columns that share a name. That sounds handy but is dangerous. employees and customers both have columns called name and city, so a natural join between them silently means e.name = c.name AND e.city = c.city:
SELECT COUNT(*) FROM employees NATURAL JOIN customers;+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)No error, just a meaningless empty answer. Worse, a natural join that works today can change meaning tomorrow when someone adds a column with a common name (like created_at) to both tables. Avoid NATURAL JOIN in real code; know it for exams.
Joins Combine With Everything You Already Know
A join simply produces a bigger "virtual table" in the FROM clause. Everything after that works as before: WHERE filters its rows, GROUP BY groups them, ORDER BY sorts them.
SELECT e.name, d.dept_name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.dept_id
WHERE d.location = 'Mumbai'
ORDER BY e.name;+--------------+-----------+
| name | dept_name |
+--------------+-----------+
| Ananya Das | Sales |
| Sneha Kapoor | Sales |
| Vikram Singh | Sales |
+--------------+-----------+
3 rows in set (0.00 sec)And finally the report from the previous chapter, now with real department names:
SELECT d.dept_name, COUNT(*) AS headcount
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_id
ORDER BY headcount DESC, d.dept_name;+-------------+-----------+
| dept_name | headcount |
+-------------+-----------+
| Engineering | 3 |
| Sales | 3 |
| HR | 2 |
| Marketing | 1 |
+-------------+-----------+
4 rows in set (0.00 sec)We grouped by d.dept_id but selected d.dept_name. That is allowed under ONLY_FULL_GROUP_BY because dept_id is the primary key of departments, so dept_name is functionally dependent on it.
The logical order of a query with joins is: FROM + JOINs (build the combined rows) → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
How MySQL Executes a Join
As mentioned, no database builds the full Cartesian product. MySQL 8 mainly uses two algorithms:
Nested loop join (with index lookups): for each row of the outer table, look up the matching rows of the inner table. With an index on the join column, each lookup is a fast B+ tree search instead of a full scan. This is the classic algorithm, and the reason join columns should be indexed. (InnoDB automatically creates an index on a foreign key column if none exists.)
Hash join (MySQL 8.0.18 and later): build an in-memory hash table on the join column of the smaller input, then scan the other table and probe the hash table. It is used when no useful index exists. Since 8.0.20 it has also replaced the older "block nested loop" algorithm.
You can see the chosen plan with EXPLAIN FORMAT=TREE. For employees and departments, the foreign-key index on employees.dept_id is used (the cost numbers will differ on your machine):
EXPLAIN FORMAT=TREE
SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id;
-> Nested loop inner join (cost=4.25 rows=10)
-> Covering index scan on d using dept_name (cost=0.75 rows=5)
-> Index lookup on e using dept_id (dept_id=d.dept_id) (cost=0.54 rows=2)Joining employees to customers on city, a column with no index, makes MySQL pick a hash join:
EXPLAIN FORMAT=TREE
SELECT e.name, c.name FROM employees e JOIN customers c ON e.city = c.city;
-> Inner hash join (e.city = c.city) (cost=6 rows=5)
-> Table scan on e (cost=0.0704 rows=10)
-> Hash
-> Table scan on c (cost=0.75 rows=5)Notice that MySQL also decided on its own which table to read first. The order in which you write tables in an inner join does not affect the result, and the optimizer is free to reorder them for speed. (For outer joins the order does matter for the result, as the next chapters show.) Indexes are covered fully in the Indexes chapter.
Common Mistakes
Forgetting the join condition. With commas, or with a plain
JOINthat has no ON (MySQL accepts this and treats it as a cross join), you get every combination: 10 × 5 = 50 rows here, millions on real tables.Unqualified column names that exist in both tables, giving error 1052.
Joining on the wrong columns, e.g.
e.emp_id = d.dept_id. The query runs and returns nonsense. Always join a foreign key to the key it references.Expecting unmatched rows in an inner join. Rows with NULL or with no partner silently disappear; use an outer join when you need them.
Using NATURAL JOIN and getting matched on columns you did not intend.
Not ordering the result. Join output order depends on the plan; add ORDER BY.
Interview Questions
What is a join in SQL?
A join combines rows from two or more tables into result rows based on a related column or condition, so that one result row contains columns from both tables.
What are the types of joins?
INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN and CROSS JOIN. A SELF JOIN is any of these applied to a table and itself. NATURAL JOIN is a shortcut syntax for an equi-join on all same-named columns.
Why do we need joins at all?
Normalized databases store each fact in one place and link tables with keys to avoid redundancy and update, insert and delete anomalies. Joins recombine that data at query time.
What is the relationship between a join and a Cartesian product?
Logically, a join is a Cartesian product followed by a selection on the join condition. Physically, databases use nested loop, hash or merge algorithms and never materialize the whole product.
What is the difference between an equi-join and a non-equi join?
An equi-join matches rows with the = operator; a non-equi (theta) join uses other comparisons like <, > or BETWEEN.
Is a foreign key required to join two tables?
No. Any comparable columns can be used in the join condition. A foreign key only enforces that the referenced row exists.
Does MySQL support FULL OUTER JOIN?
No. It is emulated by combining a LEFT JOIN and a RIGHT JOIN with UNION (or UNION ALL plus a filter for unmatched rows).
What is the difference between USING and ON?
ON takes any condition. USING (col) is shorthand for equality on a column with the same name in both tables, and it outputs that column only once.
Why should join columns be indexed?
So that a nested loop join can find matching rows with a fast index lookup instead of scanning the whole inner table for every outer row.
Summary
Normalization splits data into tables linked by keys; joins combine it back at query time.
Logically, a join = Cartesian product + filter on the join condition.
Syntax:
FROM t1 alias1 JOIN t2 alias2 ON condition. Use aliases and qualify every column.Join types differ only in what they do with unmatched rows: INNER drops them, LEFT/RIGHT keep one side's, FULL keeps both; CROSS has no condition at all.
A self join joins a table to itself using two aliases.
Prefer explicit
JOIN ... ON; know the comma style and USING; avoid NATURAL JOIN.MySQL executes joins with index-based nested loops or hash joins, so index your join columns.
In the next chapter, INNER JOIN, we look closely at the most common join: matching rows step by step, multi-table joins, joins with aggregates, and the row-multiplication trap.