SQL Join Interview Questions with Detailed Answers

SQL Joins Interview Questions and Answers with Examples (2026)

SQL joins are the foundation of relational data analysis and are tested in virtually every SQL interview at every level. From a junior data analyst to a senior data engineer role, interviewers expect you to not only know the join types by name but to write them fluently, explain their differences, choose the right one for a problem, and handle edge cases like NULLs and duplicates that joins can introduce.

This guide covers the most frequently asked SQL joins interview questions with clear examples and the reasoning interviewers expect. For the full SQL interview preparation, visit our Top 100 SQL Interview Questions and Answers (2026).

---

The 6 SQL Join Types You Must Know

Before the individual questions, here is a quick reference:

• INNER JOIN — Returns rows that match in both tables
• LEFT JOIN (LEFT OUTER JOIN) — All rows from left, matched rows from right (NULLs for no match)
• RIGHT JOIN (RIGHT OUTER JOIN) — All rows from right, matched rows from left (NULLs for no match)
• FULL OUTER JOIN — All rows from both tables (NULLs where no match on either side)
• CROSS JOIN — Every row of left combined with every row of right (Cartesian product)
• SELF JOIN — A table joined to itself using aliases

---

SQL Join Interview Questions with Detailed Answers

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

This is the most common SQL join question. The difference is what happens when there is no match:

INNER JOIN only returns rows where the join condition is satisfied in both tables. Rows with no matching counterpart are excluded entirely.

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Result: Only orders that have a matching customer. Orphan orders (no customer record) are excluded.

LEFT JOIN returns all rows from the left table, plus matched rows from the right. Where no match exists in the right table, NULL values appear for every right-table column.

SELECT o.order_id, c.customer_name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Result: All orders, even those with no customer record. Unmatched orders show NULL for customer_name.

Interview follow-up: "When would you choose LEFT JOIN over INNER JOIN?" — When you need to preserve all records from one table regardless of whether they have a match. Common for audit reports, finding missing data, and preserving all transactions even when master data is incomplete.

---

2. How do you find records in one table that do NOT exist in another table?

This is a classic anti-join problem that appears in interviews as: "Find all customers who have never placed an order" or "Find products that have no sales."

Method 1 — LEFT JOIN + IS NULL (most readable):

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

The LEFT JOIN keeps all customers. The WHERE filters to only those where the right side is NULL — meaning no matching order was found.

Method 2 — NOT EXISTS (often most efficient):

SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

Method 3 — NOT IN (use with caution when NULLs may exist):

SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
    WHERE customer_id IS NOT NULL
);

Always add WHERE customer_id IS NOT NULL in the subquery when using NOT IN — a single NULL in the subquery result causes the entire NOT IN to return no rows due to SQL's three-valued logic.

---

3. What is a FULL OUTER JOIN and when would you use it?

A FULL OUTER JOIN returns all rows from both tables. Where a row has no match in the other table, NULLs appear for that table's columns. It is the combination of LEFT JOIN and RIGHT JOIN.

SELECT
    COALESCE(e.emp_id, m.emp_id) AS emp_id,
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
FULL OUTER JOIN managers m ON e.emp_id = m.emp_id;

Use case: Reconciliation reports — finding rows that exist in one system but not another. "Find all records that are in table A but not B, in table B but not A, and in both."

Note: MySQL does not natively support FULL OUTER JOIN. Simulate it with LEFT JOIN UNION RIGHT JOIN with a WHERE clause to exclude the overlap.

---

4. What is a SELF JOIN and when do you need one?

A SELF JOIN joins a table to itself. You must use aliases to distinguish the two instances. The classic use case is an employee-manager relationship where both employee and manager IDs live in the same table:

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Other self-join use cases: finding duplicate records, comparing rows to adjacent rows (though LAG/LEAD is better for ordered comparisons), and hierarchical tree traversal.

---

5. What is a CROSS JOIN and when is it useful?

A CROSS JOIN returns every possible combination of rows from two tables (Cartesian product). No ON clause is used.

SELECT
    p.product_name,
    s.store_name
FROM products p
CROSS JOIN stores s;

If products has 100 rows and stores has 50 rows, the result has 5,000 rows — one row for every product-store combination.

Legitimate use cases: Generating all possible combinations for reports (e.g., all product-region pairs for a sales grid), creating a date spine by cross-joining a numbers table with a date series, or generating test data. CROSS JOINs on large tables accidentally can bring a database to its knees — always be deliberate.

---

6. What causes duplicate rows in a JOIN result and how do you prevent them?

Duplicates in join results are a common interview and production debugging scenario. They occur when:

• The right table has multiple rows matching the join key (one-to-many relationship). Each left row produces multiple output rows.
• The join key is not unique in either table.
• NULLs in the join column — NULL does not equal NULL in SQL, so NULL keys never match.

Solutions:

1. Use SELECT DISTINCT if all duplicates should be removed (but understand why they appeared first).
2. Pre-aggregate or deduplicate the right table before joining: SELECT customer_id, MAX(order_date) AS latest_order FROM orders GROUP BY customer_id, then join.
3. Use a CTE with ROW_NUMBER to keep only the first row per key.
4. Re-examine whether the join cardinality is intentional — sometimes duplicates are correct business data.

---

7. What is the difference between a JOIN and a UNION?

These are fundamentally different operations that beginning SQL users sometimes confuse:

• JOIN combines data horizontally — adds columns from a second table to the result based on a matching condition. The output has more columns than either input.
• UNION combines data vertically — stacks rows from two queries on top of each other. The output has the same columns as each query but more rows. Both queries must have the same number of columns with compatible types.

Use JOIN when you need to enrich data with attributes from related tables. Use UNION when you need to combine the same type of data from multiple sources (e.g., sales from 2023 UNION sales from 2024).

---

8. Write a query to find the second-highest salary using a JOIN.

This is a classic interview problem with multiple valid approaches. Here is one using a self-join:

SELECT MAX(e1.salary) AS second_highest
FROM employees e1
WHERE e1.salary < (
    SELECT MAX(e2.salary) FROM employees e2
);

Or using DENSE_RANK (modern, preferred approach):

WITH ranked AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT salary
FROM ranked
WHERE rnk = 2;

The window function approach is more generalizable to Nth highest salary and handles ties correctly.

---

Common Join Mistakes to Avoid in Interviews

1. Using JOIN without thinking about cardinality — always consider whether the join creates a one-to-one, one-to-many, or many-to-many relationship.
2. Forgetting NULL handling — NULLs do not match NULLs in a JOIN. Use COALESCE or IS NULL checks appropriately.
3. Writing RIGHT JOIN when LEFT JOIN is clearer — RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping table order. LEFT JOIN is more readable.
4. Not testing for duplicates — always check your join results for unexpected row count increases.
5. Using SELECT * in joins — when two tables share column names (like id), SELECT * returns ambiguous columns. Always alias and specify columns.

---

Frequently Asked Questions

Q: Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes. LEFT JOIN and LEFT OUTER JOIN are identical. The OUTER keyword is optional in all modern databases. The same applies to RIGHT OUTER JOIN and FULL OUTER JOIN.

Q: Can you JOIN more than two tables in one query?

Yes. You can chain multiple JOINs in a single query. Each JOIN adds one more table to the result set. The query planner optimises the join order automatically.

Q: What is a non-equi join?

A join that uses a condition other than equality — for example, joining on ranges: ON a.start_date <= b.event_date AND b.event_date <= a.end_date. Non-equi joins are less common but valid and appear in date-range lookups and salary band assignments.

Q: Where can I find more SQL interview questions beyond joins?

Our Top 100 SQL Interview Questions (2026) covers joins, subqueries, window functions, indexing, normalisation, transactions, and stored procedures.

---

Conclusion

SQL joins are the backbone of relational data retrieval. Mastering not just the syntax but the reasoning behind each join type — when to use LEFT vs INNER, how to find anti-joins, how to avoid accidental duplicates — separates strong SQL candidates from the rest. Practise writing each join type from memory and solve the classic problems (customers with no orders, second-highest salary, employee-manager hierarchies) until they are second nature.

For the complete SQL interview preparation, see our Top 100 SQL Interview Questions and Answers (2026).