SQL Joins Explained: INNER, LEFT, RIGHT, FULL & Self Joins for Interviews

SQL JOINs are tested in nearly every data engineering, backend, and analyst interview. Interviewers use them to evaluate whether you can reason about relational data, not just whether you have memorized syntax. Candidates who can explain the difference between a LEFT JOIN and an INNER JOIN, identify which rows each returns, and debug a query producing unexpected NULLs stand out immediately.

This guide walks through every JOIN type with concrete examples, explains the edge cases interviewers probe, and lists the most common SQL JOIN questions you will face.

The Example Schema

All examples use two tables: employees and departments.

-- employees (id, name, dept_id)
-- 1  Alice    10
-- 2  Bob      20
-- 3  Carol    NULL   ← no department
-- 4  Dave     30     ← dept 30 doesn't exist in departments

-- departments (id, name)
-- 10  Engineering
-- 20  Marketing
-- 40  Finance    ← no employees

1. INNER JOIN, Only Matching Rows

Returns rows that have a match in both tables. Carol (NULL dept_id) and Dave (dept 30 doesn't exist) are excluded. Finance (no employees) is also excluded.

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

-- Result: Alice / Engineering, Bob / Marketing

2. LEFT JOIN, All Left Rows + Matched Right

Returns all rows from the left table and matching rows from the right. Where there is no match, right-side columns are NULL. Use LEFT JOIN when you want to keep all records from the primary table regardless of whether related data exists.

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

-- Result:
-- Alice  / Engineering
-- Bob    / Marketing
-- Carol  / NULL        ← kept, no department
-- Dave   / NULL        ← kept, dept 30 not in departments

3. RIGHT JOIN, All Right Rows + Matched Left

Mirror image of LEFT JOIN, all rows from the right table are returned; unmatched left-table columns are NULL. In practice, most developers rewrite RIGHT JOINs as LEFT JOINs with the tables swapped for readability.

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

-- Result:
-- Alice       / Engineering
-- Bob         / Marketing
-- NULL        / Finance    ← Finance has no employees

4. FULL OUTER JOIN, All Rows from Both Tables

Returns all rows from both tables; NULLs fill the gaps wherever there is no match. Not supported in MySQL, use LEFT JOIN UNION RIGHT JOIN as a workaround.

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

-- Result:
-- Alice  / Engineering
-- Bob    / Marketing
-- Carol  / NULL
-- Dave   / NULL
-- NULL   / Finance

5. SELF JOIN, Joining a Table to Itself

A SELF JOIN joins a table to itself using aliases. Common use case: hierarchical data like employees and their managers where both rows exist in the same table.

-- employees(id, name, manager_id)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Returns each employee with their manager's name.
-- Top-level employees (no manager) get NULL.

6. CROSS JOIN, Cartesian Product

Returns every combination of rows from both tables (M × N rows). Use sparingly, it is useful for generating test data, sizing matrices, or pairing all items from one set with all items from another.

7. Common JOIN Interview Questions

• What is the difference between INNER JOIN and LEFT JOIN?
• How do you find rows in Table A that have no match in Table B?
• Can you JOIN more than two tables? Write an example with three tables.
• What happens when you JOIN on a column with NULL values?
• What is the performance impact of joining on a non-indexed column?
• How do you write a SELF JOIN? Give a use case.
• What is the difference between WHERE and HAVING in a query with JOINs?