Menu
CoddyTech
Start learning
Learn SQL step by stepOnline SQL compiler

SQL Joins Interview Questions

Join types, the row counts they produce, and the traps behind wrong totals.

By Kevin Spektor, Co-founder & CTO

10 questions

  1. What are the different types of joins in SQL?

    INNER keeps only matching rows, LEFT and RIGHT keep every row of one side with NULLs where nothing matches, FULL OUTER keeps both sides, and CROSS returns every combination. A self join joins a table to itself.

    SQL
    CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'Ravi', 1), (3, 'John', 2), (4, 'Neha', NULL);
SELECT 'INNER' AS join_type, COUNT(*) FROM employees e INNER JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT 'LEFT', COUNT(*) FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT 'RIGHT', COUNT(*) FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT 'FULL', COUNT(*) FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.id;

    Neha has no department and HR has no employees: INNER gives 3, LEFT adds Neha (4), RIGHT adds HR (4), FULL adds both (5). MySQL has no FULL OUTER JOIN; SQLite added RIGHT and FULL in 3.39. More in INNER JOIN.

  2. How many rows does a LEFT JOIN of customers to orders return?

    SQL
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

    A LEFT JOIN keeps every customer and repeats a customer once per order: Asha 2 rows, Ravi 1, Meera 1 row of NULLs, so 4. Answering 3 assumes one row per customer; 9 would be a CROSS JOIN.

  3. What happens to a LEFT JOIN when you filter the right table in WHERE?

    SQL
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT);
INSERT INTO orders VALUES (1, 1, 'paid'), (2, 1, 'refunded'), (3, 2, 'refunded');
SELECT COUNT(*)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

    WHERE o.status = 'paid' runs after the join and removes every row where o.status is NULL or not paid, so the LEFT JOIN acts like an INNER JOIN and one row is left. Move the condition into ON:

    SQL
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT);
INSERT INTO orders VALUES (1, 1, 'paid'), (2, 1, 'refunded'), (3, 2, 'refunded');
SELECT c.name, o.id AS paid_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.id;

    All three customers come back, with NULL for Ravi and Meera. Conditions on the right table of a LEFT JOIN belong in ON, except the anti-join's WHERE o.id IS NULL.

  4. What does SUM(o.amount) give after joining orders to their line items?

    SQL
    CREATE TABLE orders (id INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO orders VALUES (1, 100), (2, 50);
CREATE TABLE order_items (order_id INTEGER, product TEXT);
INSERT INTO order_items VALUES (1, 'pen'), (1, 'ink'), (1, 'pad'), (2, 'bag');
SELECT SUM(o.amount) FROM orders o JOIN order_items i ON i.order_id = o.id;

    Order 1 has three items, so the join repeats its 100 three times: 100 * 3 + 50 = 350. Fan-out is the usual reason a revenue figure is too high. Aggregate the many side before joining:

    SQL
    CREATE TABLE orders (id INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO orders VALUES (1, 100), (2, 50);
CREATE TABLE order_items (order_id INTEGER, product TEXT);
INSERT INTO order_items VALUES (1, 'pen'), (1, 'ink'), (1, 'pad'), (2, 'bag');
SELECT SUM(o.amount) AS revenue, SUM(i.items) AS items
FROM orders o
JOIN (SELECT order_id, COUNT(*) AS items FROM order_items GROUP BY order_id) AS i
  ON i.order_id = o.id;

    That prints 150 | 4. SUM(DISTINCT o.amount) is no fix: two orders with the same amount would count once.

  5. What is a self join? Write a query that shows each employee with their manager.

    A self join joins a table to itself under two aliases, the standard way to read a hierarchy such as manager_id pointing at another employees row.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

    LEFT JOIN keeps Asha, who has no manager; an inner join would drop her. For the whole chain of managers, use a recursive CTE. More in self join.

  6. How many rows does a CROSS JOIN of 3 sizes and 4 colors return?

    SQL
    CREATE TABLE sizes (size TEXT);
INSERT INTO sizes VALUES ('S'), ('M'), ('L');
CREATE TABLE colors (color TEXT);
INSERT INTO colors VALUES ('red'), ('blue'), ('black'), ('white');
SELECT COUNT(*) FROM sizes CROSS JOIN colors;

    A CROSS JOIN returns every combination of rows, 3 sizes times 4 colors = 12, with no ON condition. Use it to build combinations, such as every date for every user before a LEFT JOIN fills in zeros. You get one by accident when you list two tables with a comma and forget the join condition.

  7. How do you find customers who have never placed an order?

    Use an anti-join: LEFT JOIN the orders and keep rows where the order side is NULL, or use NOT EXISTS.

    SQL
    CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera');
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders VALUES (1, 1), (2, 1), (3, 2);
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

    Only Meera is returned. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) gives the same result. Avoid NOT IN (SELECT customer_id FROM orders): one NULL customer_id makes it return no rows.

  8. MySQL has no FULL OUTER JOIN. How do you write one?

    Take the LEFT JOIN, then UNION ALL the right-table rows that had no match.

    SQL
    CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'Ravi', 1), (3, 'John', 2), (4, 'Neha', NULL);
SELECT e.name AS employee, d.name AS department
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
UNION ALL
SELECT NULL, d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

    The result has the three matched employees, Neha with a NULL department, and HR with a NULL employee. LEFT JOIN ... UNION ... RIGHT JOIN also works here, but UNION would merge genuinely identical rows, such as two Ravis in one department.

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

    A non-equi join matches on a condition other than =, such as BETWEEN, < or >. The classic example puts each employee into a salary band.

    SQL
    CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
CREATE TABLE grades (grade TEXT, min_salary INTEGER, max_salary INTEGER);
INSERT INTO grades VALUES ('C', 0, 69999), ('B', 70000, 99999), ('A', 100000, 1000000);
SELECT e.name, e.salary, g.grade
FROM employees e
JOIN grades g ON e.salary BETWEEN g.min_salary AND g.max_salary
ORDER BY e.salary DESC, e.name;

    Only Asha is in band A and only Karan in band C. Other uses: the price valid at an event's time, and overlapping bookings.

  10. What are nested loop, hash and merge joins, and when does the optimizer choose each?

    They are the three physical ways to run a join, chosen by the optimizer and shown by EXPLAIN. A nested loop looks up inner rows for each outer row and wins when the outer side is small and the inner join column is indexed. A hash join hashes the smaller input and probes it, good for large unsorted inputs joined with =. A merge join walks two inputs already sorted on the key.

    In most engines only a nested loop handles a condition like a.x < b.y. SQLite only does nested loops, and MySQL added hash joins in 8.0.18 and from 8.0.20 also runs non-equi joins as a hash join plus a filter; see the MySQL hash join docs. A nested loop over two big tables with no index on the inner side is the classic slow query.

    Practice it: Two Sum, Merge Sorted Array

Coddy programming languages illustration

Learn to code with Coddy

Get started