SQL JOIN interview questions, with answers

The questions, with answers

1. 1.What is the difference between INNER JOIN and LEFT JOIN?

   An INNER JOIN returns only the rows where the join condition matches in both tables. A LEFT JOIN returns every row from the left (first-listed) table, and for rows with no match on the right it still returns the row, with the right-hand columns filled with NULL.

   The interview follow-up is always the same: "so which one loses rows?" INNER JOIN drops unmatched rows from both sides; LEFT JOIN drops unmatched rows only from the right side.

   SELECT d.dept_name, e.name
   FROM Departments d
   LEFT JOIN Employees e ON e.dept_id = d.dept_id;
   -- departments with no employees still appear; for them e.name comes back NULL

2. 2.When would you use a RIGHT JOIN instead of a LEFT JOIN?

   Almost never in practice — a RIGHT JOIN is a LEFT JOIN with the tables written in the other order, and it returns exactly the same rows. Most teams standardise on LEFT JOIN so the "preserved" table is always the one you read first. If you're asked, say that, then show you can rewrite one as the other.

   One detail worth adding: with SELECT * the column order flips too, because columns come out in FROM order — so name the columns when you rewrite.

   SELECT A.id, B.a_id FROM A RIGHT JOIN B ON A.id = B.a_id;
   -- returns the same rows as
   SELECT A.id, B.a_id FROM B LEFT JOIN A ON A.id = B.a_id;

3. 3.What does a FULL OUTER JOIN return, and which databases support it?

   A FULL OUTER JOIN returns every row from both tables: matched pairs once, and unmatched rows from either side with NULLs for the missing half. PostgreSQL, SQL Server, Oracle and SQLite (3.39 and later) support it directly. MySQL and MariaDB do not — the workaround is a LEFT JOIN, UNION ALL, and a RIGHT JOIN restricted to the rows the LEFT JOIN missed. Knowing that MySQL detail is a common tie-breaker question.

   The trap in the popular version of this answer is using plain UNION: UNION removes duplicate rows, so if the join legitimately produces two identical rows, one of them disappears. UNION ALL plus the IS NULL filter keeps every row exactly once.

   -- MySQL / MariaDB emulation
   SELECT A.id, B.a_id FROM A LEFT JOIN B ON A.id = B.a_id
   UNION ALL
   SELECT A.id, B.a_id FROM A RIGHT JOIN B ON A.id = B.a_id
   WHERE A.id IS NULL;   -- only the right-side rows the LEFT JOIN did not already return

4. 4.Why does my JOIN return more rows than either table?

   Because a JOIN produces one output row per matching pair. If a key appears twice on the left and three times on the right, those rows alone produce 2 × 3 = 6 output rows. This is the single most common bug in reporting queries: joining to a one-to-many table and then summing a column from the "one" side, which gets counted once per child row.

   The fix is to aggregate the many side first (in a subquery or CTE) and join the aggregated result.

5. 5.What is a self join and when do you need one?

   A self join joins a table to itself, using two aliases so the two "copies" can be told apart. You need it whenever rows in a table refer to other rows in the same table — employees and their managers, categories and their parent categories, a follows relationship between users.

   SELECT e.name AS employee, m.name AS manager
   FROM Employees e
   LEFT JOIN Employees m ON m.emp_id = e.manager_id;
   -- LEFT, so the top-level manager (manager_id NULL) is not dropped

6. 6.What is a CROSS JOIN, and is it ever useful?

   A CROSS JOIN returns the Cartesian product: every row of the first table paired with every row of the second, with no condition. Ten rows joined to ten rows gives a hundred. It is useful deliberately — generating every combination of sizes and colours, or every date paired with every store to find missing sales — and harmful accidentally: in the old comma-style syntax (FROM A, B) the join condition lives in WHERE, so forgetting the WHERE clause gives you the full product. MySQL will also quietly treat A JOIN B with no ON clause as a cross join; PostgreSQL rejects it.

7. 7.How do NULLs behave in a JOIN condition?

   NULL never equals anything, including NULL. So a row whose join key is NULL will not match any row on the other side, even one whose key is also NULL. In an INNER JOIN it disappears; in a LEFT JOIN it survives from the left side with NULLs on the right. If you genuinely need NULLs to match, you have to say so explicitly — IS NOT DISTINCT FROM in PostgreSQL, the null-safe <=> operator in MySQL, or a COALESCE on both sides.

8. 8.What is the difference between putting a condition in ON and putting it in WHERE?

   For an INNER JOIN, nothing — the two are logically equivalent, so the optimiser is free to treat them the same. For a LEFT JOIN, everything. A condition in ON decides which right-hand rows are considered matches; left rows are kept either way. The same condition in WHERE runs after the join and filters out rows — including the left rows whose right-hand columns are NULL, which quietly turns your LEFT JOIN back into an INNER JOIN.

   -- keeps every customer; orders filtered to 2024
   SELECT c.name, o.id
   FROM Customers c
   LEFT JOIN Orders o ON o.customer_id = c.id AND o.year = 2024;
   
   -- drops customers with no 2024 orders (o.year is NULL for them)
   SELECT c.name, o.id
   FROM Customers c
   LEFT JOIN Orders o ON o.customer_id = c.id
   WHERE o.year = 2024;

9. 9.How do you find rows in one table that have no match in another?

   Three ways, and interviewers like you to know more than one: a LEFT JOIN with WHERE right.key IS NULL (the anti-join pattern — test a column that can never be NULL on a real match, such as the right table's primary key, or a nullable column will give false positives); NOT EXISTS with a correlated subquery; or NOT IN — with the caveat that NOT IN returns no rows at all if the subquery contains a single NULL, which is a classic trap. NOT EXISTS is the safest answer to give.

   SELECT c.*
   FROM Customers c
   LEFT JOIN Orders o ON o.customer_id = c.id
   WHERE o.id IS NULL;   -- o.id is the primary key: NULL only when there was no match

How the diagnostic asks it

One question from the SQL bank, exactly as a sitting would show it. The bank has 4 on joins and 30 across SQL.