SQL JOIN Interview Questions (2026): 8 Real Questions With Answers

Overview

JOIN questions are the bread and butter of any SQL interview because they reveal how candidates think about set operations and NULLs. The eight questions below cover every JOIN type interviewers actually use: INNER, LEFT, RIGHT (rare but tested), FULL OUTER, SELF, CROSS, anti-join (NOT EXISTS / LEFT JOIN ... IS NULL), and semi-join. If you can write all of these from memory, you will pass the JOIN portion of any data interview.

Questions & Answers

Q1. INNER JOIN — orders with customer info Common · Easy

Prompt: List all orders with the customer name and email. Skip orders without a matching customer.

SELECT
  o.id AS order_id,
  o.total,
  c.name,
  c.email
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

Why this matters: INNER JOIN keeps only rows where the join condition matches in both tables. Orders without a customer (e.g., from data corruption or soft-deleted users) are dropped. Always state this trade-off out loud in the interview.

Q2. LEFT JOIN — customers with order count (including zero) Common · Easy

Prompt: For every customer, return their total number of orders. Customers with zero orders should still appear with count = 0.

SELECT
  c.id,
  c.name,
  COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY order_count DESC;

Why this matters: LEFT JOIN keeps every customer; non-matching orders become NULL. COUNT(o.id) skips NULLs, so customers with zero orders correctly show 0. Using COUNT(*) here would return 1 for those customers — a classic trap.

Q3. FULL OUTER JOIN — reconciliation Common · Medium

Prompt: Given two snapshot tables expected(id, amount) and actual(id, amount), find rows that differ or are missing on either side.

SELECT
  COALESCE(e.id, a.id) AS id,
  e.amount AS expected,
  a.amount AS actual,
  CASE
    WHEN e.id IS NULL THEN 'missing in expected'
    WHEN a.id IS NULL THEN 'missing in actual'
    WHEN e.amount <> a.amount THEN 'mismatch'
    ELSE 'match'
  END AS status
FROM expected e
FULL OUTER JOIN actual a ON a.id = e.id
WHERE e.id IS NULL
   OR a.id IS NULL
   OR e.amount <> a.amount
ORDER BY id;

Why this matters: FULL OUTER JOIN keeps non-matching rows from both sides. Reconciliation queries are the most common real-world use. MySQL <8.0 has no FULL OUTER JOIN — emulate with UNION of LEFT and RIGHT joins, and state that limitation in the interview.

Q4. SELF JOIN — employees and their managers Common · Medium

Prompt: Given employees(id, name, manager_id), list every employee with their manager's name. Show the CEO (no manager) too.

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

Why this matters: A self-join joins a table to itself with different aliases. LEFT JOIN keeps the CEO (whose manager_id is NULL) in the result. Switching to INNER JOIN would silently drop the CEO — a common bug.

Q5. Anti-join — customers with no orders Common · Medium

Prompt: Find every customer who has not placed any orders.

-- Approach 1: LEFT JOIN ... IS NULL
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Approach 2: NOT EXISTS (often faster on large tables)
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Why this matters: Both approaches return the same rows. NOT EXISTS is usually faster because the engine can short-circuit on the first match. NOT IN can produce wrong results when the inner query returns NULL — avoid it for anti-joins.

Q6. Semi-join — customers with at least one order Common · Medium

Prompt: List customers who have placed at least one order, but do not duplicate customers with multiple orders.

-- Approach 1: EXISTS (canonical semi-join)
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Approach 2: DISTINCT on a JOIN
SELECT DISTINCT c.id, c.name
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Why this matters: EXISTS is the textbook semi-join: it asks "does any matching row exist" without producing one row per match. The JOIN + DISTINCT version works but can be slower on large data because the engine materializes all matches before deduplicating.

Q7. CROSS JOIN — generate combinations Common · Medium

Prompt: Given regions(name) and product_categories(name), produce every region/category combination for a coverage matrix.

SELECT r.name AS region, p.name AS category
FROM regions r
CROSS JOIN product_categories p
ORDER BY r.name, p.name;

Why this matters: CROSS JOIN produces the Cartesian product — every row from the left table paired with every row from the right. Use it intentionally for matrices and date-spine generation, never accidentally (a missing JOIN condition can silently become a CROSS JOIN with millions of rows).

Q8. Multiple JOINs with NULL preservation Common · Hard

Prompt: For each customer, get their last order date and the product name from that order. Customers without orders should still appear.

WITH last_order AS (
  SELECT customer_id, MAX(order_date) AS last_order_date
  FROM orders
  GROUP BY customer_id
)
SELECT
  c.id,
  c.name,
  lo.last_order_date,
  p.name AS last_product
FROM customers c
LEFT JOIN last_order lo ON lo.customer_id = c.id
LEFT JOIN orders o
  ON o.customer_id = lo.customer_id
 AND o.order_date = lo.last_order_date
LEFT JOIN products p ON p.id = o.product_id
ORDER BY c.id;

Why this matters: Chaining LEFT JOINs requires care — once you add an INNER JOIN downstream, customers with no orders silently disappear. Keep every join LEFT until you are certain the right side has full coverage. The CTE for last_order avoids correlated subqueries and runs faster on large data.

Common Pitfalls

• WHERE clause on a LEFT JOIN right side turns it into an INNER JOIN — put the filter in the ON clause if you want to keep unmatched rows.
• Counting with COUNT(*) instead of COUNT(column) on a LEFT JOIN — COUNT(*) returns 1 for unmatched rows; COUNT(column) returns 0.
• Using NOT IN with a subquery that can return NULL — produces empty results. Use NOT EXISTS instead.
• Joining on a non-unique key without realizing — row counts multiply (a 1:N join becomes N rows per left row). Always verify cardinality.
• CROSS JOIN by accident — happens when you forget the ON clause in some dialects. Modern PostgreSQL and SQL Server require explicit ON; MySQL does not.

Practice With AI2SQL

The fastest way to lock in these patterns is to write them by hand, then check against AI2SQL output. Generate variations of every question above with different schemas, rewrite them from scratch, and you will have these patterns memorized within a week.

Frequently Asked Questions