Interview Questions

SQL joins in detail: every join type explained with MySQL output

Himanshu Sharma · 12 September 2023 · 10 min read

Data in a relational database is deliberately split across tables: customers in one, orders in another, products in a third. A join is how you put it back together when you ask a question. "Which department does each employee work in?" needs rows from two tables, matched on a shared value. Joins are also the most common topic in SQL interviews, and the place where plausible-looking queries return wrong answers. This guide explains what a join actually does, walks through every type with real output, and then covers the traps. All results were produced on MySQL 8.4 using the two small tables below.

The sample tables

CREATE TABLE departments (
  department_id   INT PRIMARY KEY,
  department_name VARCHAR(30) NOT NULL
);
CREATE TABLE employees (
  employee_id   INT PRIMARY KEY,
  first_name    VARCHAR(30),
  last_name     VARCHAR(30),
  department_id INT NULL,
  manager_id    INT NULL,
  salary        INT
);
INSERT INTO departments VALUES (101,'HR'),(102,'IT'),(103,'Sales'),(104,'Finance');
INSERT INTO employees VALUES
  (1,'John','Smith',101,NULL,50000),
  (2,'Jane','Johnson',102,1,70000),
  (3,'Robert','Williams',101,1,45000),
  (4,'Emily','Davis',103,1,52000),
  (5,'Alex','Brown',NULL,2,48000);

Two details are deliberate. Alex has no department (NULL), and Finance has no employees. Those two rows are what make the join types behave differently.

What a join does

Think of a join in two steps. First, imagine every row of the left table paired with every row of the right table (the Cartesian product). Second, keep only the pairs where the ON condition is true. The join types differ only in what happens to rows that find no match.

Join	Rows with no match
`INNER JOIN`	dropped, from both sides
`LEFT JOIN`	left rows kept, right columns become `NULL`
`RIGHT JOIN`	right rows kept, left columns become `NULL`
`FULL OUTER JOIN`	kept from both sides
`CROSS JOIN`	no condition; every pair is kept

Real database engines do not build the full product. They use algorithms such as nested-loop joins with an index lookup on the second table, or (from MySQL 8.0.18) hash joins when there is no useful index. The result is the same; only the speed differs, which is why indexes on join columns matter (see indexes).

INNER JOIN

Returns only rows that have a match on both sides:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

+------------+-----------+-----------------+
| first_name | last_name | department_name |
+------------+-----------+-----------------+
| John       | Smith     | HR              |
| Jane       | Johnson   | IT              |
| Robert     | Williams  | HR              |
| Emily      | Davis     | Sales           |
+------------+-----------+-----------------+

Alex disappeared (no department) and so did Finance (no employees). JOIN on its own means INNER JOIN. Use it when you only want complete pairs.

LEFT JOIN (LEFT OUTER JOIN)

Keeps every row from the left table. Where there is no match, the right-side columns are NULL:

SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

+------------+-----------------+
| first_name | department_name |
+------------+-----------------+
| John       | HR              |
| Jane       | IT              |
| Robert     | HR              |
| Emily      | Sales           |
| Alex       | NULL            |
+------------+-----------------+

Alex now appears, with NULL for the department. Use a left join for "everything from this table, plus related data if it exists". Employees with or without a department, customers with or without orders.

The anti-join: find rows with no match

Add WHERE right.key IS NULL and the left join returns only the unmatched left rows. Which departments have no employees?

SELECT d.department_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

+-----------------+
| department_name |
+-----------------+
| Finance         |
+-----------------+

Test a column that can never be NULL in a real row, such as the primary key (e.employee_id).

RIGHT JOIN

The mirror image: every row from the right table is kept.

SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_id, e.employee_id;

+------------+-----------------+
| first_name | department_name |
+------------+-----------------+
| John       | HR              |
| Robert     | HR              |
| Jane       | IT              |
| Emily      | Sales           |
| NULL       | Finance         |
+------------+-----------------+

Finance appears with a NULL employee. A right join is just a left join with the tables swapped, so most developers always write left joins and put the "keep everything" table first. That makes queries read in one direction.

FULL OUTER JOIN

Keeps unmatched rows from both sides. MySQL does not have FULL OUTER JOIN, so combine a left and a right join with UNION (which also removes duplicates):

SELECT e.first_name, d.department_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, d.department_name
FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

+------------+-----------------+
| first_name | department_name |
+------------+-----------------+
| John       | HR              |
| Jane       | IT              |
| Robert     | HR              |
| Emily      | Sales           |
| Alex       | NULL            |
| NULL       | Finance         |
+------------+-----------------+

Both Alex and Finance are present. PostgreSQL, SQL Server and Oracle support FULL OUTER JOIN directly. Be careful: UNION removes duplicate result rows, so if two different real rows look identical, one disappears. Use UNION ALL plus a filter on the second query when exact duplicates matter.

CROSS JOIN

No ON condition: every row pairs with every row. With 5 employees and 4 departments you get 20 rows:

SELECT COUNT(*) FROM employees CROSS JOIN departments;

+----------+
| COUNT(*) |
+----------+
|       20 |
+----------+

It is useful for generating combinations (sizes by colours, dates by shops) and dangerous by accident. If you forget the ON clause on two big tables, you can get millions of rows, so a result that is "far too big" usually means a missing join condition.

SELF JOIN

A table joined to itself, with two different aliases. The classic use is a hierarchy: each employee has a manager_id pointing at another employee.

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

+----------+---------+
| employee | manager |
+----------+---------+
| John     | NULL    |
| Jane     | John    |
| Robert   | John    |
| Emily    | John    |
| Alex     | Jane    |
+----------+---------+

A left join is used so that John, who has no manager, still appears. For hierarchies of unknown depth, use a recursive common table expression instead of chaining self joins.

ON versus WHERE in outer joins

This is the most common source of wrong results. For an inner join, a condition in ON or in WHERE gives the same answer. For an outer join it does not.

Count employees earning above 47,000 for every department, including empty ones:

-- condition in ON: the filter applies while matching
SELECT d.department_name, COUNT(e.employee_id) AS n
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id AND e.salary > 47000
GROUP BY d.department_id, d.department_name ORDER BY d.department_id;

+-----------------+---+
| department_name | n |
+-----------------+---+
| HR              | 1 |
| IT              | 1 |
| Sales           | 1 |
| Finance         | 0 |
+-----------------+---+

Now the same condition moved to WHERE:

SELECT d.department_name, COUNT(e.employee_id) AS n
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.salary > 47000
GROUP BY d.department_id, d.department_name ORDER BY d.department_id;

+-----------------+---+
| department_name | n |
+-----------------+---+
| HR              | 1 |
| IT              | 1 |
| Sales           | 1 |
+-----------------+---+

Finance vanished. Finance's unmatched row has e.salary = NULL, the WHERE test is not true for NULL, and the row is thrown away, turning the left join into an inner join. Rule: conditions about the optional (right) table belong in ON; conditions about the table you are keeping can go in WHERE.

NULL never matches NULL

Join conditions use =, and NULL = NULL is not true. Rows whose join column is NULL never match anything in an inner join. That is why Alex was missing from the inner join even if there were a department with a NULL id. If you truly need NULLs to match, use MySQL's null-safe operator <=>, but it is rarely the right design; fix the data instead.

SELECT NULL = NULL, NULL <=> NULL, 1 <=> 1;

+-------------+---------------+---------+
| NULL = NULL | NULL <=> NULL | 1 <=> 1 |
+-------------+---------------+---------+
|        NULL |             1 |       1 |
+-------------+---------------+---------+

Joins can multiply rows

If the right table has several matches for one left row, the left row appears several times. Joining departments to employees gives HR twice because HR has two employees. This is correct, but it changes the meaning of aggregates:

SELECT d.department_name, COUNT(*) AS staff, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name ORDER BY d.department_id;

+-----------------+-------+------------+
| department_name | staff | avg_salary |
+-----------------+-------+------------+
| HR              |     2 | 47500.0000 |
| IT              |     1 | 70000.0000 |
| Sales           |     1 | 52000.0000 |
+-----------------+-------+------------+

When you join a parent to two different child tables (orders and payments, say), the rows multiply into a product and SUM double-counts. Aggregate each child table in its own subquery first, then join the summaries.

USING and NATURAL JOIN

When the join column has the same name on both sides, USING is shorter and returns that column only once:

SELECT first_name, department_name
FROM employees JOIN departments USING (department_id)
ORDER BY employee_id;

NATURAL JOIN joins on every same-named column automatically. Avoid it: adding an unrelated column with a matching name later silently changes the query.

Joining more than two tables

Chain joins one after another; each ON links the new table to something already present:

SELECT s.student_name, c.title, i.name AS instructor
FROM students s
JOIN enrolments e USING (student_id)
JOIN courses c USING (course_id)
JOIN instructors i USING (instructor_id);

Keep the join conditions explicit and indexed, and select only the columns you need.

Choosing the right join

You want	Use
Only rows that match in both tables	`INNER JOIN`
All rows of A, with B's data when it exists	`LEFT JOIN`
All rows of A that have no match in B	`LEFT JOIN ... WHERE b.key IS NULL`
All rows of both, matched where possible	`LEFT` + `RIGHT` joined by `UNION` (MySQL)
Every combination	`CROSS JOIN`
Rows related to other rows in the same table	self join

Common interview questions

What is the difference between INNER and LEFT JOIN? An inner join drops unmatched rows; a left join keeps all left rows.
How do you find records in A that are not in B? Anti-join with LEFT JOIN ... IS NULL, or NOT EXISTS.
Why did my LEFT JOIN behave like an INNER JOIN? A WHERE condition on the right table removed the NULL rows.
Why did my total double? The join multiplied rows before the aggregate.
Can a join be replaced by a subquery? Often, see subqueries and how they differ from joins.

Common mistakes

Forgetting the ON clause and creating a Cartesian product.
Putting right-table filters in WHERE on a left join.
Joining on columns of different types or without an index, which makes the join slow.
Using SELECT * across joins and getting duplicate column names.
Counting with COUNT(*) after an outer join instead of COUNT(e.employee_id), which counts the NULL rows.

Practice and next steps

Recreate the two tables above in a scratch database and predict the output of each query before running it. Then try the SQL joins cheat sheet for a compact version, the MySQL cheat sheet for everyday commands and the MySQL interview questions for more practice. The free MySQL course teaches joins on a real shop database, step by step.