SQL Joins Explained: Inner, Left, Right, Full and Self Join

Relational design deliberately spreads facts over several tables: the employee's name in one, the department's name in another, linked by a key. A join puts them back together for a query. Interviewers test joins by giving two small tables and asking for the exact output, so this note runs every join type on the same two tables and shows every result row. Results are listed in a fixed order for reading; a real query needs ORDER BY to guarantee any order.

The example tables

employees (Rohan has no department yet; manager_id refers to emp_id in the same table):

departments (HR has no employees):

Every join below starts from the same matching: each employee's dept_id against each department's.

1. The join reads employees one row at a time and looks in departments for every row whose dept_id equals this employee's. Each pair found becomes one result row.
2. Asha's dept_id is 10, which equals Engineering's: one result row, Asha with Engineering.
3. Vikram's dept_id is 20, which equals Sales's: one result row, Vikram with Sales.
4. Meera's dept_id is 10, which equals Engineering's: one result row, Meera with Engineering. A department may match any number of employees.
5. Rohan's dept_id is NULL, and NULL = 10 is UNKNOWN, never TRUE, so no department matches. An inner join emits nothing; a left join still emits Rohan, with NULL for every department column.
6. No employee pointed at HR, so a right join adds it with NULL for the employee. A full outer join keeps both padded rows: 3 matched pairs plus one unmatched row from each side.

Inner join

An inner join returns one row for each pair of rows that satisfy the join condition. Rows without a partner disappear from both sides.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Rohan is missing because NULL = 10 is UNKNOWN, not TRUE, and HR is missing because no employee points to it. JOIN alone means INNER JOIN. A join whose condition is equality is an equi-join; one with any other comparison (<, BETWEEN) is a theta join or non-equi join, used for example to match a salary to a pay band.

Left outer join

A left join keeps every row of the left table. Where there is no match, the right table's columns are NULL.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Right outer join

A right join keeps every row of the right table. It is a left join with the tables swapped, and most teams write it as a left join for readability.

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Full outer join

A full outer join keeps every row from both sides: the matched pairs, the unmatched left rows and the unmatched right rows.

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;   -- PostgreSQL, SQL Server, Oracle

MySQL has no FULL OUTER JOIN. Emulate it with a left join plus the right join's unmatched rows:

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;

The often-quoted version, LEFT JOIN … UNION … RIGHT JOIN, gives the same five rows here, but UNION removes duplicates, so it would also collapse genuinely repeated result rows (two employees with the same name in the same department). UNION ALL with the IS NULL filter does not.

Cross join

A cross join is the Cartesian product: every row of one table paired with every row of the other, with no condition. Four employees and three departments give 4 × 3 = 12 rows.

SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

1. A cross join pairs every employee with every department, with no condition: 4 × 3 = 12 result rows, one per cell.
2. An inner join keeps only the cells where ON e.dept_id = d.dept_id is TRUE: 3 of 12. Rohan's comparisons are NULL = 10, NULL = 20 and NULL = 30, all UNKNOWN, so his row has no TRUE cell.
3. Outer joins add back what has no TRUE cell, padded with NULL: a left join adds Rohan's row, a right join HR's column, and a full outer join both.

Writing FROM employees, departments with no WHERE produces the same product, which is how accidental cross joins happen. Cross joins are useful for generating combinations, such as every size with every colour.

Self join

A self join joins a table to itself through two aliases. To list each employee with their manager's name, treat one copy as employees (e) and the other as managers (m):

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Another self join finds pairs of colleagues in the same department: ON e.dept_id = m.dept_id AND e.emp_id < m.emp_id returns the single pair (Asha, Meera); the < stops each pair appearing twice and a person pairing with themselves.

Natural join and USING

A natural join joins on every column the two tables share by name and shows each shared column once:

SELECT * FROM employees NATURAL JOIN departments;

It is an inner join on dept_id, the only shared name. MySQL puts the shared column first. JOIN departments USING (dept_id) gives the same result while naming the column explicitly, whereas ON e.dept_id = d.dept_id with SELECT * shows dept_id twice. The danger of NATURAL JOIN is that it trusts names: if departments had a name column, the join would also require employees.name = departments.name and return nothing. Prefer ON or USING in real code.

Duplicate rows: joining on non-key columns

A join returns one row per matching pair. When the join column is unique on one side (a key), each row on the other side matches at most once. When it is not unique on either side, rows multiply. Add a table of bonuses, where a department may have several:

For each value of the join column, the join returns (matching left rows) × (matching right rows):

This fan-out is the most common cause of wrong totals: join an order to its items and to its payments at once, and each item is repeated once per payment, so SUM(item_price) comes out too large. Aggregate each child table in a subquery first, then join the totals.

Where to put a filter in an outer join

A condition in ON decides which rows match; a condition in WHERE filters the result afterwards. For an outer join the two differ:

Join vs subquery

Some questions can be written either way, and the choice changes duplicates and NULL handling:

The last row is a classic trap. 30 NOT IN (10, 20, 10, NULL) expands to 30 <> 10 AND 30 <> 20 AND 30 <> 10 AND 30 <> NULL, and the last test is UNKNOWN, so HR is not returned. Use NOT EXISTS, or filter WHERE dept_id IS NOT NULL inside the subquery.

On performance, modern optimizers (MySQL 8 included) turn many IN and EXISTS subqueries into semi-joins, so the two forms often run the same plan. Use a join when you need columns from both tables, and EXISTS when you only need to test for a match.

Join types at a glance

Common mistakes

• Forgetting that NULL never equals anything, so rows with NULL keys drop out of inner joins.
• Filtering the right table in WHERE after a left join, which silently turns it into an inner join.
• Summing after a join that fans out, which double-counts.
• Using NOT IN against a column that can hold NULL.
• Writing LEFT JOIN … UNION … RIGHT JOIN for a full join and losing genuine duplicates.
• Using NATURAL JOIN in production code that can later gain same-named columns.

Interview questions

What is the difference between LEFT JOIN and RIGHT JOIN? A left join keeps all rows of the table before the keyword and a right join all rows of the table after it. A LEFT JOIN B returns the same rows as B RIGHT JOIN A; only the column order differs.

If table A has 4 rows and B has 3, what is the minimum and maximum size of A INNER JOIN B? Of A LEFT JOIN B? An inner join returns between 0 and 12 rows. A left join returns at least 4 rows (every row of A appears) and at most 12.

How do you find employees who have no department? SELECT name FROM employees WHERE dept_id IS NULL if NULL means unassigned. To also catch department ids that do not exist in departments, left join and keep rows where d.dept_id IS NULL.

How do you write a full outer join in MySQL? A left join, then UNION ALL with a right join filtered to rows where the left table's key is NULL. Using plain UNION removes duplicate rows that may be genuine.

What is a self join? Give a use. A table joined to itself under two aliases. It finds each employee's manager, pairs of rows in the same group, or consecutive events in a log table.

Why can NOT IN return no rows when you expect some? If the subquery returns a NULL, every x NOT IN (…) test becomes UNKNOWN, and WHERE drops UNKNOWN rows. NOT EXISTS does not have this problem.

How does a database execute a join? With a nested-loop join (for each outer row, look up matches, ideally through an index), a hash join (build a hash table on the smaller input, probe it with the other) or a sort-merge join (sort both inputs on the key and merge). MySQL 8.0.18 and later can use hash joins; PostgreSQL implements all three.

Is a cross join ever useful? Yes: generating every combination (dates × stores for a report with zero-filled days, sizes × colours) or joining to a one-row table of parameters. Accidental cross joins come from a forgotten join condition.

Next, summarize rows with grouping and subqueries: SQL GROUP BY, HAVING and Subqueries. Joins are the heart of the SQL (Basic) skill test.

Practise joins on real tables: Employees Earning More Than Their Manager (a self join), Hostel Students Who Never Ordered From the Canteen (an anti join) and Mock Test Sittings for Every Learner and Track (a cross join with a left join).