Autocoder

SQL Joins Interview Questions

Last updated 2026-11-19

Joins are the most reliably asked SQL topic. Interviewers rarely ask for definitions; they put two small tables on the screen and ask what a query returns, how many rows, or why a count is wrong. Each answer below shows the query and the result computed on the same sample data, so you can check your own prediction row by row.

All queries run on the two tables shown first. Gita has no department (NULL), and the Legal department has no employees. Those two rows are what make the join types behave differently, so keep them in mind. For the wider set see SQL interview questions and the strategy in how to crack a SQL interview.

DialectsStandard SQL; MySQL and PostgreSQL differences noted
Sample tablesemployees (7 rows), departments (4 rows)
LevelEasy to hard

Sample data used on this page

Two tables. employees has a manager_id pointing back at emp_id for the self-join questions. Asha has no manager, and Gita has no department.

sql
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, salary INT, manager_id INT);
INSERT INTO departments VALUES (10,'Sales'),(20,'Engineering'),(30,'HR'),(40,'Legal');
INSERT INTO employees VALUES
(1,'Asha',20,90000,NULL),(2,'Bilal',20,75000,1),(3,'Chitra',10,60000,1),
(4,'Dev',10,60000,3),(5,'Esha',30,50000,1),(6,'Farid',20,75000,1),(7,'Gita',NULL,40000,5);

What to look for in any join question

SQL Joins Interview Questions: questions with worked answers

  1. 1

    What does INNER JOIN return on this data?

    easy

    Only rows with a match in both tables. Gita (NULL department) and Legal (no employees) disappear, so six rows are returned.

    sql
    SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id ORDER BY e.emp_id;
    -- name   | dept_name
    -- -------+------------
    -- Asha   | Engineering
    -- Bilal  | Engineering
    -- Chitra | Sales
    -- Dev    | Sales
    -- Esha   | HR
    -- Farid  | Engineering
  2. 2

    What does LEFT JOIN return, and how do you read the unmatched rows?

    easy

    Every row from the left table, with NULLs for right-side columns where no match exists. Gita appears with a NULL department name. All seven employees are returned, whereas the right-hand Legal row is not, because it has no employee.

    sql
    SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id ORDER BY e.emp_id;
    -- name   | dept_name
    -- -------+------------
    -- Asha   | Engineering
    -- Bilal  | Engineering
    -- Chitra | Sales
    -- Dev    | Sales
    -- Esha   | HR
    -- Farid  | Engineering
    -- Gita   | NULL
  3. 3

    Find departments that have no employees (an anti-join). Show three ways.

    medium

    The LEFT JOIN with IS NULL check keeps departments whose join found nothing. NOT EXISTS is usually the clearest and handles NULLs correctly. NOT IN is dangerous: if the subquery returns any NULL, the whole predicate becomes unknown and returns no rows. Here employees.dept_id contains a NULL (Gita), so NOT IN returns an empty result even though Legal has no employees.

    sql
    SELECT d.dept_name FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id WHERE e.emp_id IS NULL;
    -- dept_name
    -- ---------
    -- Legal
    sql
    SELECT d.dept_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);
    -- dept_name
    -- ---------
    -- Legal
    sql
    SELECT d.dept_name FROM departments d WHERE d.dept_id NOT IN (SELECT dept_id FROM employees);
    -- (0 rows)
  4. 4

    What is the difference between putting a condition in ON versus WHERE with a LEFT JOIN?

    medium

    A condition in ON decides which right-side rows match, so unmatched left rows still appear with NULLs. The same condition in WHERE filters the final rows, and because NULL fails the test it removes the unmatched rows, silently turning the LEFT JOIN into an inner join. Compare the two results: the first keeps all seven employees, the second only the Engineering ones.

    sql
    SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id AND d.dept_name = 'Engineering' ORDER BY e.emp_id;
    -- name   | dept_name
    -- -------+------------
    -- Asha   | Engineering
    -- Bilal  | Engineering
    -- Chitra | NULL
    -- Dev    | NULL
    -- Esha   | NULL
    -- Farid  | Engineering
    -- Gita   | NULL
    sql
    SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id WHERE d.dept_name = 'Engineering' ORDER BY e.emp_id;
    -- name  | dept_name
    -- ------+------------
    -- Asha  | Engineering
    -- Bilal | Engineering
    -- Farid | Engineering
  5. 5

    How do you write a FULL OUTER JOIN in MySQL, which does not support it?

    medium

    PostgreSQL, SQL Server, Oracle and SQLite (3.39+) support FULL OUTER JOIN directly. MySQL does not, so combine a LEFT JOIN and a RIGHT JOIN with UNION, which removes the duplicate matched rows (use UNION ALL plus an anti-join condition if duplicates matter). The result has both unmatched sides: Gita with no department and Legal with no employee.

    sql
    SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
    UNION
    SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id
    ORDER BY 1;
    -- Equivalent in PostgreSQL: FROM employees e FULL OUTER JOIN departments d ON ...
    -- name   | dept_name
    -- -------+------------
    -- NULL   | Legal
    -- Asha   | Engineering
    -- Bilal  | Engineering
    -- Chitra | Sales
    -- Dev    | Sales
    -- Esha   | HR
    -- Farid  | Engineering
    -- Gita   | NULL
  6. 6

    Write a self join to list each employee with their manager.

    medium

    Join the table to itself with two aliases, matching manager_id to emp_id. Use LEFT JOIN so the top of the hierarchy (Asha, with no manager) is not dropped. Always alias both sides, otherwise the column names are ambiguous.

    sql
    SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id ORDER BY e.emp_id;
    -- employee | manager
    -- ---------+--------
    -- Asha     | NULL
    -- Bilal    | Asha
    -- Chitra   | Asha
    -- Dev      | Chitra
    -- Esha     | Asha
    -- Farid    | Asha
    -- Gita     | Esha
  7. 7

    Why can a join make COUNT or SUM wrong? Show the fan-out.

    medium

    Joining a parent to a one-to-many child repeats the parent row once per child, so any aggregate over parent columns is inflated; aggregate the child table first or count distinct keys. A second trap is that COUNT(*) also counts the NULL-extended row a LEFT JOIN creates for an unmatched parent: Legal has no employees, yet COUNT(*) reports 1, while COUNT(e.emp_id) correctly reports 0.

    sql
    SELECT d.dept_name, COUNT(*) AS wrong, COUNT(e.emp_id) AS right_count FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id GROUP BY d.dept_name ORDER BY d.dept_name;
    -- dept_name   | wrong | right_count
    -- ------------+-------+------------
    -- Engineering | 3     | 3
    -- HR          | 1     | 1
    -- Legal       | 1     | 0
    -- Sales       | 2     | 2
  8. 8

    What does a CROSS JOIN produce and how many rows?

    easy

    The Cartesian product: every row of the first table paired with every row of the second, so m times n rows, here 7 x 4 = 28. It has no join condition. It is used deliberately to generate combinations (all dates times all products), and accidentally when someone forgets the ON clause in an old comma-style join.

    sql
    SELECT COUNT(*) AS rows_returned FROM employees CROSS JOIN departments;
    -- rows_returned
    -- -------------
    -- 28
  9. 9

    What is the difference between ON, USING and NATURAL JOIN?

    medium

    ON takes any condition. USING(col) joins on a column with the same name in both tables and outputs that column once. NATURAL JOIN joins on every column that happens to share a name, which is fragile: adding an innocently named column later (like both tables getting a last_updated column) silently changes the query. Avoid NATURAL JOIN in production code. In MySQL and PostgreSQL USING works the same way, and Oracle also supports it.

    sql
    SELECT name, dept_name FROM employees JOIN departments USING (dept_id) ORDER BY emp_id;
    -- name   | dept_name
    -- -------+------------
    -- Asha   | Engineering
    -- Bilal  | Engineering
    -- Chitra | Sales
    -- Dev    | Sales
    -- Esha   | HR
    -- Farid  | Engineering
  10. 10

    Do NULL keys match in a join? How do you join on a nullable column?

    medium

    No. NULL = NULL evaluates to unknown, so rows with NULL keys never match in an inner or outer join condition, which is why Gita matches no department. If you really want NULLs to match, use the null-safe operator: IS NOT DISTINCT FROM in PostgreSQL, or the <=> operator in MySQL. Think carefully, because matching NULLs usually means the data model is wrong.

  11. 11

    Find employees who earn more than their manager.

    hard

    Self join with a comparison on salary. Here Bilal earns 75000 and his manager Asha earns 90000, so nobody qualifies, and the result is empty. Change the data and rows appear, which is the point of testing with an empty case. A correlated subquery is the alternative.

    sql
    SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.emp_id WHERE e.salary > m.salary;
    -- (0 rows)
  12. 12

    Join three tables: list each department with its employee count and total salary, including empty departments.

    medium

    Start from the table that must be preserved (departments), LEFT JOIN employees, then group. Use COUNT(e.emp_id) and COALESCE on SUM so empty departments show 0 rather than NULL. Employees without a department are excluded because they have no department row to attach to.

    sql
    SELECT d.dept_name, COUNT(e.emp_id) AS headcount, COALESCE(SUM(e.salary), 0) AS total_salary FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id GROUP BY d.dept_name ORDER BY d.dept_name;
    -- dept_name   | headcount | total_salary
    -- ------------+-----------+-------------
    -- Engineering | 3         | 240000
    -- HR          | 1         | 50000
    -- Legal       | 0         | 0
    -- Sales       | 2         | 120000
  13. 13

    What is a non-equi join? Give an example.

    hard

    A join whose condition uses an operator other than equality: range, inequality or BETWEEN. Typical uses are salary bands, date ranges and pairs without duplicates. This finds each pair of employees in one department where the first earns more than the second. It cannot use a hash join, so it is usually executed as a nested loop and can be slow on large tables.

    sql
    SELECT a.name AS higher, b.name AS lower FROM employees a JOIN employees b ON a.dept_id = b.dept_id AND a.salary > b.salary ORDER BY a.name, b.name;
    -- higher | lower
    -- -------+------
    -- Asha   | Bilal
    -- Asha   | Farid
  14. 14

    How does the database execute a join? Name the join algorithms.

    medium

    Three main strategies. Nested loop: for each row of the outer input, probe the inner input, best with an index on the inner join key and small outer sets. Hash join: build a hash table on the smaller input and probe it with the other, good for large unsorted equi-joins (PostgreSQL has it; MySQL added it in 8.0.18). Merge join: both inputs sorted on the key and merged, good when sorted or indexed already. Use EXPLAIN to see which is chosen. The optimiser picks by estimated cost, so up-to-date statistics matter. See SQL indexes questions.

  15. 15

    What is a semi join, and when do EXISTS and IN beat a join?

    medium

    A semi join returns rows from the left table that have at least one match on the right, without duplicating them. EXISTS and IN express it directly. An inner join that returns the same left row once per match gives duplicates that you would need DISTINCT to remove. Departments that have at least one employee:

    sql
    SELECT d.dept_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id) ORDER BY d.dept_name;
    -- dept_name
    -- -----------
    -- Engineering
    -- HR
    -- Sales

Frequently asked questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only rows with matches on both sides. LEFT JOIN keeps every left row and fills the right-side columns with NULL when there is no match.

Does join order matter?

For inner joins the optimiser can reorder them freely. For outer joins the order and the preserved side change the result.

Why does my LEFT JOIN behave like an INNER JOIN?

A WHERE condition on a right-table column removes the NULL rows. Move the condition into ON, or add OR column IS NULL.

Where can I practise more SQL queries?

Try SQL query practice and SQL for freshers.

Related guides

More in Programming Language Interview Questions by Language

See all in this section