20 SQL JOIN Interview Questions and Answers for 2026

SQL JOIN questions test more than syntax. Strong answers explain row multiplicity, unmatched-row preservation, three-valued logic, tie handling, vendor differences, and what the execution plan does with real data.

Table of Contents

1. JOIN Fundamentals Questions
2. Visual JOIN Examples
3. Self-Join Questions
4. Practical JOIN Patterns
5. ON vs WHERE Questions
6. CROSS JOIN Questions
7. Advanced JOIN Questions
8. JOIN Performance
9. Quick Reference

JOIN Fundamentals Questions

These questions test your understanding of the core JOIN types.

What is the difference between JOIN types?

INNER JOIN emits every row pair for which the join condition is TRUE. LEFT JOIN emits those pairs plus one NULL-extended result row for each unmatched left row. RIGHT JOIN is the mirrored form. FULL OUTER JOIN also preserves unmatched rows from both sides. Set-style Venn diagrams hide duplicate rows and one-to-many multiplicity, so reason from row pairs instead.

Think of two tables: users and orders. A user might have many orders, some orders, or no orders.

INNER JOIN only shows users who have placed orders. Users without orders are excluded entirely.

LEFT JOIN shows all users. Users with orders show their order data; users without orders show NULL in the order columns. This is useful for finding users who haven't ordered.

RIGHT JOIN shows all orders. Orders with users show user data; orders with deleted or missing users show NULL. Less common but useful for data audits.

FULL OUTER JOIN shows everything—users without orders AND orders without users. Great for finding orphaned records.

The choice depends on what question you're answering. "Show me order totals per user" might be INNER. "Show me all users and their orders if any" needs LEFT.

How do duplicate join keys affect the result?

A JOIN operates on matching row pairs, not distinct key values. If key 42 occurs twice on the left and three times on the right, an equality JOIN produces six rows for that key. This many-to-many multiplication is correct SQL behavior, but it can silently inflate counts and sums.

Before aggregating, confirm the intended relationship and the uniqueness constraints. If only one row per key is valid, fix or deduplicate the source according to a deterministic business rule rather than adding DISTINCT as a reflex. If the relationship is genuinely many-to-many, aggregate at the correct grain.

Visual JOIN Examples

Sample Data

USERS                    ORDERS
+----+--------+          +----+---------+--------+
| id | name   |          | id | user_id | amount |
+----+--------+          +----+---------+--------+
| 1  | Alice  |          | 1  | 1       | 100    |
| 2  | Bob    |          | 2  | 1       | 150    |
| 3  | Carol  |          | 3  | 3       | 200    |
+----+--------+          +----+---------+--------+

Alice has 2 orders, Bob has 0 orders, Carol has 1 order

What does INNER JOIN return?

SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

+--------+--------+
| name   | amount |
+--------+--------+
| Alice  | 100    |
| Alice  | 150    |
| Carol  | 200    |
+--------+--------+
-- Bob excluded (no matching orders)

What does LEFT JOIN return?

SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

+--------+--------+
| name   | amount |
+--------+--------+
| Alice  | 100    |
| Alice  | 150    |
| Bob    | NULL   |  <- Bob included with NULL
| Carol  | 200    |
+--------+--------+

What does RIGHT JOIN return?

SELECT users.name, orders.amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- With the sample rows above, the result contains Alice twice and Carol once.
-- If the ORDERS table also contained (4, 99, 75), the result would be:
+--------+--------+
| name   | amount |
+--------+--------+
| Alice  | 100    |
| Alice  | 150    |
| Carol  | 200    |
| NULL   | 75     |  <- Order with no matching user
+--------+--------+

In production, a valid foreign key from orders.user_id to users.id normally prevents that orphan row. RIGHT JOIN is also expressible as a LEFT JOIN with the table order reversed, which many teams find easier to scan.

What does FULL OUTER JOIN return?

SELECT users.name, orders.amount
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

+--------+--------+
| name   | amount |
+--------+--------+
| Alice  | 100    |
| Alice  | 150    |
| Bob    | NULL   |  <- User without orders
| Carol  | 200    |
| NULL   | 75     |  <- Order without user
+--------+--------+

The last row again assumes an extra orphan order. PostgreSQL supports FULL OUTER JOIN; MySQL 8.4 does not. For MySQL, combine a LEFT JOIN with only the unmatched rows from the reversed LEFT JOIN:

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION ALL
SELECT u.name, o.amount
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

Self-Join Questions

These questions test your ability to work with hierarchical data.

How do you write an employee-manager self-join?

This question appears in almost every SQL interview. Given an employees table with id, name, and manager_id, show each employee with their manager's name.

The Schema:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);
 
INSERT INTO employees VALUES
(1, 'Alice', NULL),    -- CEO, no manager
(2, 'Bob', 1),         -- Reports to Alice
(3, 'Carol', 1),       -- Reports to Alice
(4, 'Dave', 2),        -- Reports to Bob
(5, 'Eve', 2);         -- Reports to Bob

The Solution:

-- Basic self-join
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

+----------+---------+
| employee | manager |
+----------+---------+
| Alice    | NULL    |  <- CEO has no manager
| Bob      | Alice   |
| Carol    | Alice   |
| Dave     | Bob     |
| Eve      | Bob     |
+----------+---------+

How do you show the full management hierarchy?

-- PostgreSQL: traverse every level from each root employee
WITH RECURSIVE org AS (
    SELECT
        id,
        name,
        manager_id,
        0 AS depth,
        ARRAY[id] AS path
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    SELECT
        e.id,
        e.name,
        e.manager_id,
        org.depth + 1,
        org.path || e.id
    FROM employees e
    JOIN org ON e.manager_id = org.id
    WHERE NOT e.id = ANY(org.path)
)
SELECT id, name, manager_id, depth, path
FROM org
ORDER BY path;

A fixed chain of self-joins only reaches a fixed depth. A recursive common table expression (CTE) handles an arbitrary hierarchy. The path check prevents bad cyclic data from recursing forever; PostgreSQL also supports a CYCLE clause. Recursive-CTE and cycle-detection syntax varies by database.

Practical JOIN Patterns

How do you find records with no match (anti-join)?

-- Find users who have never placed an order
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
 
-- Equivalent intent using NOT EXISTS
SELECT u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Both forms can produce an anti-join plan. Do not choose between them from a blanket speed claim: inspect the plan and measure with representative data. In the LEFT JOIN form, check a non-nullable right-side key such as o.id.

How do you find duplicate records?

-- Find duplicate email addresses
SELECT a.email, a.id, b.id
FROM users a
JOIN users b ON a.email = b.email AND a.id < b.id;

How do you join multiple tables?

-- Orders with user and product information
SELECT
    u.name AS customer,
    p.name AS product,
    oi.quantity,
    o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= :from_timestamp;

How do you aggregate with JOINs?

-- Total spent per user (including users with no orders)
SELECT
    u.name,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

How do you find the latest record per group?

-- Returns every order tied at the maximum timestamp
SELECT u.name, o.amount, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at = (
    SELECT MAX(o2.created_at)
    FROM orders o2
    WHERE o2.user_id = u.id
);
 
-- Returns exactly one order per user using a deterministic tiebreaker
SELECT name, amount, created_at
FROM (
    SELECT
        u.name,
        o.amount,
        o.created_at,
        ROW_NUMBER() OVER (
            PARTITION BY u.id
            ORDER BY o.created_at DESC, o.id DESC
        ) AS rn
    FROM users u
    JOIN orders o ON u.id = o.user_id
) ranked
WHERE rn = 1;

These queries have different tie semantics. Use RANK() instead of ROW_NUMBER() when all records tied for first place should be returned. Neither form is universally faster; compare actual plans and support the access pattern with an appropriate index, commonly starting with (user_id, created_at) and sometimes including the tiebreaker.

ON vs WHERE Questions

This is a common interview gotcha.

Why do ON and WHERE return different results with LEFT JOIN?

-- These return DIFFERENT results with LEFT JOIN!
 
-- Query 1: Condition in ON
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;
 
-- Query 2: Condition in WHERE
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

Query 1 Result:

+--------+--------+
| name   | amount |
+--------+--------+
| Alice  | 150    |  <- Alice's 100 order excluded from match
| Bob    | NULL   |  <- Still shown (all left rows)
| Carol  | 200    |
+--------+--------+

Query 2 Result:

+--------+--------+
| name   | amount |
+--------+--------+
| Alice  | 150    |
| Carol  | 200    |
+--------+--------+
-- Bob excluded because NULL > 100 is FALSE

NULL > 100 evaluates to UNKNOWN, and WHERE keeps only TRUE rows. The rule is therefore semantic: ON determines which row pairs match, while WHERE filters the joined result. Put a right-side predicate in ON when unmatched left rows must remain; put it in WHERE when those rows should be removed.

CROSS JOIN Questions

What is a CROSS JOIN and when do you use it?

-- Generate all size-color combinations
SELECT sizes.name AS size, colors.name AS color
FROM sizes
CROSS JOIN colors;
 
-- Equivalent explicit syntax
SELECT sizes.name, colors.name
FROM sizes, colors;
 
-- PostgreSQL: include dates that have zero orders
SELECT d.day, COUNT(o.id) AS order_count
FROM generate_series(
    DATE '2026-01-01',
    DATE '2026-01-07',
    INTERVAL '1 day'
) AS d(day)
LEFT JOIN orders o
    ON o.created_at >= d.day
   AND o.created_at < d.day + INTERVAL '1 day'
GROUP BY d.day
ORDER BY d.day;

The range predicate avoids wrapping the indexed orders.created_at column in a function. Other databases use different date-series generators, such as a recursive CTE or a calendar table.

Advanced JOIN Questions

What is the difference between JOIN and UNION?

JOIN combines tables horizontally—adding columns from related tables based on a key. UNION combines queries vertically—stacking rows from multiple SELECT statements.

JOIN needs a relationship between tables. UNION needs queries with the same column structure.

-- JOIN: More columns
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;
 
-- UNION: More rows
SELECT name, email FROM customers
UNION
SELECT name, email FROM leads;
-- Removes duplicates; use UNION ALL to keep them

How do you find records that exist in one table but not another?

Use LEFT JOIN with a NULL check, NOT EXISTS, or a carefully NULL-safe NOT IN. The best plan depends on the engine, statistics, indexes, and data distribution.

-- Method 1: LEFT JOIN + NULL check
SELECT a.*
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
WHERE b.id IS NULL;
 
-- Method 2: NOT EXISTS
SELECT a.*
FROM table_a a
WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE b.a_id = a.id);
 
-- Method 3: NOT IN (careful with NULLs!)
SELECT a.*
FROM table_a a
WHERE a.id NOT IN (SELECT b.a_id FROM table_b b WHERE b.a_id IS NOT NULL);

How do you handle NULL values in JOINs?

An ordinary comparison with NULL evaluates to UNKNOWN, not TRUE or FALSE. Therefore NULL = NULL does not match rows in a JOIN.

If two NULLs should be considered equal, express that explicitly or use the database's null-safe comparison. Avoid casually using COALESCE with a sentinel value: the sentinel can collide with real data and the expression can affect index use.

-- NULLs won't match
SELECT * FROM a JOIN b ON a.value = b.value;
-- Rows where value IS NULL won't join
 
-- To match NULLs:
SELECT * FROM a JOIN b ON a.value = b.value OR (a.value IS NULL AND b.value IS NULL);
 
-- PostgreSQL: IS NOT DISTINCT FROM treats NULLs as equal
SELECT * FROM a JOIN b ON a.value IS NOT DISTINCT FROM b.value;
 
-- MySQL: <=> is the NULL-safe equality operator
SELECT * FROM a JOIN b ON a.value <=> b.value;

When would you use a subquery instead of a JOIN?

Subqueries are useful when you need aggregation before joining, you're checking existence (EXISTS/NOT EXISTS), or you need values from a correlated lookup.

JOINs are usually better for combining data from multiple tables. Modern optimizers often convert subqueries to JOINs anyway.

Choose the construct that expresses the required multiplicity and existence semantics. Then verify the plan: optimizers may decorrelate a subquery or transform it to a semi-join, but not every query can be rewritten safely.

JOIN Performance

How do you optimize JOIN performance?

Start with evidence, not a fixed ordering recipe:

1. Use the engine's plan tools—such as PostgreSQL EXPLAIN (ANALYZE, BUFFERS) or MySQL EXPLAIN ANALYZE—on representative parameters and data.
2. Compare estimated and actual row counts. Large gaps often point to stale statistics, correlated columns, skew, or a predicate the estimator models poorly.
3. Index columns used for selective lookups and joins when the workload benefits. A foreign-key declaration does not necessarily create an index on the referencing column.
4. Keep searchable predicates sargable when possible; for example, use a timestamp range instead of applying DATE() to every stored timestamp.
5. Select only required columns and verify that one-to-many joins are not multiplying rows unintentionally.
6. Measure the whole query. Optimizers commonly reorder inner joins and push predicates; outer joins restrict legal reorderings because unmatched-row preservation changes semantics.

INNER JOIN is not universally faster than LEFT JOIN, and writing the smaller table first is not a portable optimization rule. Correct semantics come first; the actual plan determines what the database executes.

Sources and further reading

• PostgreSQL: table and joined-table syntax
• PostgreSQL: joins between tables
• PostgreSQL: comparison predicates and NULL
• PostgreSQL: recursive queries and cycle detection
• PostgreSQL: using EXPLAIN
• MySQL 8.4: outer join optimization
• MySQL 8.4: nested join optimization
• MySQL 8.4: optimizing SELECT statements

Quick Reference

Practice Questions

Test yourself before your interview:

1. Given tables products and categories, write a query to show all products with their category name, including products with no category.

2. Find all customers who have placed more than 3 orders.

3. Find pairs of employees who work in the same department.

4. What's the result of this query?

SELECT a.x, b.y
FROM A a
LEFT JOIN B b ON a.id = b.id
WHERE b.y = 'test';

Answers:

SELECT p.name, c.name AS category
FROM products p
LEFT JOIN categories c ON p.category_id = c.id;

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 3;

SELECT e1.name, e2.name, e1.department
FROM employees e1
JOIN employees e2 ON e1.department = e2.department AND e1.id < e2.id;

4. This removes rows where b.y is NULL, so unmatched rows from A are excluded and the result is equivalent to an INNER JOIN for this predicate. NULL = 'test' evaluates to UNKNOWN, and WHERE keeps only TRUE rows.

Related Articles

• Complete Node.js Backend Developer Interview Guide - comprehensive preparation guide for backend interviews
• PostgreSQL & Node.js Interview Guide - Connection pooling, transactions, and query optimization
• MongoDB Interview Guide - NoSQL alternative with Mongoose and aggregation pipelines
• System Design Interview Guide - Scalability, reliability, and distributed systems
• REST API Interview Guide - API design principles and best practices

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows where there's a match in both tables - if a row in either table has no matching row in the other, it's excluded. LEFT JOIN returns all rows from the left table plus matching rows from the right table - if there's no match, the right side columns contain NULL. Use INNER JOIN when you only want matched data, LEFT JOIN when you need all records from one table regardless of matches.

What is a self-join and when would you use it?

A self-join is when a table is joined with itself, treating it as two separate tables using aliases. Common uses include: hierarchical data (employees and their managers in one table), comparing rows within the same table (finding duplicates), or finding sequential records (current row and previous row). Example: SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id.

What is the difference between WHERE and ON in a JOIN?

ON determines which row pairs match for that join; WHERE keeps only rows for which its predicate is TRUE after the joined table is formed. For an INNER JOIN, an optimizer can often treat equivalent predicates similarly. For a LEFT JOIN, a right-side predicate in ON changes matching while preserving every left row, whereas a null-rejecting right-side predicate in WHERE removes unmatched rows and may make the result equivalent to an INNER JOIN. Place a predicate according to the result semantics, not a slogan.

What is a CROSS JOIN?

CROSS JOIN produces a Cartesian product - every row from the first table paired with every row from the second table. If table A has 10 rows and table B has 5 rows, CROSS JOIN produces 50 rows. It has no ON clause. Use cases include: generating all combinations (sizes × colors for a product), creating date ranges, or test data generation. Use carefully as the result set grows multiplicatively.

How do you optimize JOIN performance?

Start with correct cardinality and inspect the actual plan with engine-specific EXPLAIN tools. Verify statistics and row estimates, index useful lookup and filtering keys, keep predicates sargable where possible, and return only needed data. The optimizer commonly reorders inner joins and pushes predicates, while outer joins constrain legal rewrites. INNER is not universally faster than OUTER and writing the smaller table first is not a general tuning rule; measure with representative data.

What is the difference between UNION and JOIN?

JOIN combines columns from multiple tables horizontally based on a relationship - you get more columns in the result. UNION combines rows from multiple queries vertically - you stack results on top of each other. UNION requires the same number and compatible types of columns. UNION removes duplicates by default; use UNION ALL to keep duplicates. JOIN relates data across tables; UNION concatenates similar result sets.