SQL JOIN Interview Questions and Answers

• INNER JOIN: only rows that match in both tables.
• LEFT JOIN: all rows from the left table, plus matches (or NULLs) from the right.
• RIGHT JOIN: all rows from the right table, plus matches from the left.
• FULL OUTER JOIN: all rows from both, matched where possible.
• CROSS JOIN: every combination of rows (Cartesian product).
• SELF JOIN: a table joined to itself using two aliases.

See Introduction to JOINs.

Count matching pairs first; then add the unmatched rows each join keeps.

• customers INNER JOIN orders: 3 rows (Asha ×2, Ravi ×1).
• customers LEFT JOIN orders: 5 rows (the 3 matches + Meera and John with NULLs).

SELECT COUNT(*) AS inner_rows
FROM customers c JOIN orders o ON o.customer_id = c.customer_id;

inner_rows
----------
3

SELECT COUNT(*) AS left_rows
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id;

left_rows
---------
5

Each 1 in a matches each 1 in b. NULL never equals anything, including another NULL.

Two common wrong answers: "2" (forgetting that duplicates multiply) and counting NULL = NULL as a match. This question tests both ideas at once.

A condition in ON decides which right-side rows match; unmatched left rows are still kept with NULLs. A condition in WHERE filters after the join, so a condition on the right table removes the NULL rows and effectively turns the LEFT JOIN into an INNER JOIN.

-- Condition in ON: every customer kept
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.amount > 200
ORDER BY c.customer_id;

name  | order_id
------+---------
Asha  | 101
Ravi  | 103
Meera | NULL
John  | NULL

-- Condition in WHERE: Meera and John disappear
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 200
ORDER BY c.customer_id;

name | order_id
-----+---------
Asha | 101
Ravi | 103

See LEFT JOIN.

Yes. When one row matches several rows on the other side, it appears once per match. In the puzzle above, a 4-row table joined to a 4-row table returned up to 8 rows, and a CROSS JOIN of the same tables returns 16. This "fan-out" is also why SUM after a one-to-many join can double-count. See Multiple JOINs.

-- 1. LEFT JOIN anti-join
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- 2. NOT EXISTS (safest)
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- 3. NOT IN (only safe if orders.customer_id can never be NULL)
SELECT name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

All three return Meera and John. Mention the NOT IN + NULL trap.

SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

order_id | customer_id
---------+------------
104      | 5

Follow up by explaining that a foreign key constraint would have prevented this orphan row.

A self join with LEFT JOIN:

SELECT e.name AS employee,
       COALESCE(m.name, '(no manager)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

employee | manager
---------+-------------
Asha     | Priya
Ravi     | Meera
Meera    | (no manager)
John     | Priya
Priya    | (no manager)

See SELF JOIN.

Count orders per customer, then keep the customer(s) whose count equals the maximum count, so ties are handled.

SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(*) = (
  SELECT MAX(cnt)
  FROM (SELECT COUNT(*) AS cnt FROM orders GROUP BY customer_id) t
);

name
----
Asha

ORDER BY COUNT(*) DESC LIMIT 1 is shorter, but it silently drops ties. Mention both.