SQL Joins Explained with Examples: INNER, LEFT, RIGHT, FULL, CROSS and SELF JOIN

A SQL join combines rows from two tables using a condition, usually a shared key. INNER JOIN keeps only matching rows; LEFT and RIGHT JOIN keep every row from one side and fill the other with NULL; FULL OUTER JOIN keeps everything from both; CROSS JOIN pairs every row with every row; a SELF JOIN joins a table to itself. The rest of this guide shows each one on the same two tables so you can see exactly which rows survive.

The two tables used in every example

Every example below uses the same data, so you can compare the output row by row. One employee has no department, and one department has no employees, because those are the cases that make the join types different.

employees

departments

Notice: Meera has no department at all, Karan points at a department (40) that does not exist, and HR (30) has nobody in it. Only Asha and Ravi match on both sides.

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), dept_id INT);

INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'HR');
INSERT INTO employees VALUES (1, 'Asha', 10), (2, 'Ravi', 20), (3, 'Meera', NULL), (4, 'Karan', 40);

What is an INNER JOIN?

An INNER JOIN returns a row only when the ON condition is true for a pair of rows. Unmatched rows on either side are dropped.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON d.id = e.dept_id;

Meera (NULL department), Karan (department 40 does not exist) and HR (no employees) all disappear. JOIN on its own means INNER JOIN; the word INNER is optional.

Use it when a row only makes sense if both sides exist: an order line and its product, a student and the course they enrolled in.

What is a LEFT JOIN?

A LEFT JOIN (also written LEFT OUTER JOIN) keeps every row from the left table, the one named before the JOIN keyword. When the right table has no match, its columns come back as NULL.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id;

All four employees survive. Meera and Karan get NULL because nothing matched. HR is still missing because it is on the right side and nobody referenced it.

This is the join you reach for in most reporting queries: "all customers, with their latest order if any", "all products, with their total sales even if zero".

What is a RIGHT JOIN?

A RIGHT JOIN keeps every row from the right table and fills the left with NULL. It is the exact mirror of LEFT JOIN.

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON d.id = e.dept_id;

Now HR appears with no employee, while Meera and Karan are gone. The same result comes from swapping the table order and using LEFT JOIN, which is why many teams never write RIGHT JOIN at all: it is easier to read a query when the "keep all of these" table is always first.

SELECT e.name, d.name AS department
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id;   -- identical output

What is a FULL OUTER JOIN?

A FULL OUTER JOIN keeps every row from both tables. Matching rows are merged; unmatched rows from either side are padded with NULL.

SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON d.id = e.dept_id;

PostgreSQL, SQL Server, Oracle and SQLite (3.39+) support it. MySQL does not. In MySQL you emulate it with a UNION of the LEFT and RIGHT joins; UNION removes the duplicate matched rows:

SELECT e.name, d.name AS department
FROM employees e LEFT JOIN departments d ON d.id = e.dept_id
UNION
SELECT e.name, d.name
FROM employees e RIGHT JOIN departments d ON d.id = e.dept_id;

FULL OUTER JOIN is mostly used for reconciliation: comparing two lists and finding what is only in one of them.

What is a CROSS JOIN?

A CROSS JOIN has no ON condition. It pairs every row of the first table with every row of the second: the Cartesian product. 4 employees × 3 departments = 12 rows.

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

It is useful on purpose for generating combinations (every size of every T-shirt, every day of a date range against every store). It happens by accident when you list two tables in FROM with a comma and forget the WHERE clause, which on a million-row table is a query that never finishes.

What is a SELF JOIN?

A self join is not a separate keyword. It is any join where both sides are the same table, under two different aliases. The classic case is a hierarchy stored in one table.

CREATE TABLE staff (id INT PRIMARY KEY, name VARCHAR(50), manager_id INT);
INSERT INTO staff VALUES (1, 'Priya', NULL), (2, 'Asha', 1), (3, 'Ravi', 1), (4, 'Karan', 2);

SELECT s.name AS employee, m.name AS manager
FROM staff s
LEFT JOIN staff m ON m.id = s.manager_id;

A LEFT JOIN is used so the top of the tree (Priya, who has no manager) is not dropped. Other self-join uses: finding duplicate rows, pairing each row with the previous one by date, or listing employees who share a department with a given person.

The ON vs WHERE trap

This is the single most common join bug, and a favourite interview question. Suppose you want all employees, and the department name only when the department is in a given building:

-- WRONG: silently becomes an inner join
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
WHERE d.name <> 'HR';

The LEFT JOIN first produces NULL department rows for Meera and Karan. Then the WHERE clause evaluates NULL <> 'HR', which is not true, so those rows are thrown away. The result has only Asha and Ravi, exactly as if you had written INNER JOIN.

-- RIGHT: extra conditions on the outer table go in ON
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id AND d.name <> 'HR';

Rule of thumb: in an outer join, conditions that filter the preserved (left) table can go in WHERE; conditions on the nullable (right) table belong in ON.

How many rows will a join return?

Interviewers like to ask this because it shows whether you understand matching, not just syntax.

Two things to remember:

• NULL never matches NULL. NULL = NULL is unknown, not true, so a row with a NULL key is never joined by an equality condition. That is why Meera is unmatched even if a department also had a NULL id.
• Duplicates multiply. If an employee appears twice in a table and their department appears twice in the other, the join produces four rows for that pair. Unexpected row counts after a join almost always mean a key you thought was unique is not.

Anti joins and semi joins

Two patterns built on joins show up constantly in real queries.

Anti join: rows with no match. Employees who are not in any existing department:

SELECT e.*
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
WHERE d.id IS NULL;            -- Meera, Karan

The same thing with NOT EXISTS, which many people find clearer and which the optimiser handles well:

SELECT e.*
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

Avoid NOT IN (SELECT dept_id ...) when the subquery can contain NULL: a single NULL in the list makes NOT IN return no rows at all.

Semi join: rows that have at least one match, without duplicating them. Departments that have at least one employee:

SELECT d.*
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);   -- Engineering, Sales

An INNER JOIN would give the same departments but repeated once per employee; EXISTS returns each department once.

USING, NATURAL JOIN and joining more than two tables

• JOIN departments USING (dept_id) works when both columns have the same name and merges them into one output column. Fine for quick queries.
• NATURAL JOIN joins on every column with a matching name. Avoid it: adding a column called name or created_at to either table silently changes the join.
• Joins chain left to right. Each JOIN clause joins the result so far with the next table, so with three tables you can mix types: orders LEFT JOIN customers ... INNER JOIN products .... Read it one step at a time and ask what rows exist after each step.

Joins and performance

The optimiser decides how to execute a join (nested loop, hash join, merge join); you decide whether it has what it needs.

• Index the join columns. Foreign keys such as employees.dept_id should be indexed; the primary key side already is.
• Filter early. Put selective WHERE conditions on the driving table so fewer rows reach the join.
• Select only needed columns. SELECT * across four tables drags wide rows through every step.
• Run EXPLAIN. If you see a full table scan on the big table in a join, you are missing an index or a usable condition.

SQL join interview questions

1. INNER vs LEFT JOIN? INNER keeps matches only; LEFT keeps all left rows with NULLs for missing right rows.
2. Can a LEFT JOIN return fewer rows than the left table? No. It returns at least one row per left row, and more when a left row matches several right rows.
3. Why is my LEFT JOIN behaving like an INNER JOIN? A WHERE condition on the right table drops the NULL-padded rows. Move it into ON.
4. How do you get rows with no match? LEFT JOIN with WHERE right.key IS NULL, or NOT EXISTS.
5. What is the output size of a CROSS JOIN? Rows in A × rows in B.
6. How do you write FULL OUTER JOIN in MySQL? UNION of a LEFT JOIN and a RIGHT JOIN.
7. Do NULL keys match in a join? Never with =. Use IS NOT DISTINCT FROM (PostgreSQL) or COALESCE if you really need NULL-safe matching.
8. JOIN vs subquery? Joins return columns from both tables and are usually what the optimiser prefers; EXISTS subqueries are better for "does a match exist" checks because they do not duplicate rows.

Key takeaway

Pick the join by asking one question: which table's rows must survive even without a match? Both sides must match: INNER. Keep the left side: LEFT. Keep both: FULL. Every combination: CROSS. Then put filters on the nullable table in ON, not WHERE, and check row counts against the key rules above. For the rest of DBMS (normalisation, indexes, transactions, the questions that follow joins in an interview), download the free SQL handwritten notes and follow the placement preparation roadmap.