SQL JOIN Interview Questions

INNER, OUTER, CROSS and FULL joins — how they differ and when to reach for each.

4 Questions
Detailed Answers
All Topics

Filter by Difficulty

3
Intermediate

Explain different types of JOINs in SQL

JOINs 5 min read

JOINs are used to combine rows from two or more tables based on a related column. There are several types of JOINs in SQL:

JOIN Types:

  • INNER JOIN: Returns matching rows from both tables
  • LEFT JOIN: Returns all rows from left table and matching rows from right
  • RIGHT JOIN: Returns all rows from right table and matching rows from left
  • FULL OUTER JOIN: Returns all rows when there's a match in either table
  • CROSS JOIN: Returns Cartesian product of both tables
INNER JOIN Example: 
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
LEFT JOIN Example: 
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
10
Intermediate

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

CROSS JOIN 3 min read

A CROSS JOIN produces the Cartesian product of two tables, meaning it combines each row from the first table with every row from the second table.

CROSS JOIN Example: 
-- Create sample tables
CREATE TABLE colors (color_name VARCHAR(20));
INSERT INTO colors VALUES ('Red'), ('Green'), ('Blue');

CREATE TABLE sizes (size_name VARCHAR(20));
INSERT INTO sizes VALUES ('Small'), ('Medium'), ('Large');

-- CROSS JOIN to get all combinations
SELECT color_name, size_name
FROM colors
CROSS JOIN sizes;

Result (9 rows):

  • Red - Small
  • Red - Medium
  • Red - Large
  • Green - Small
  • Green - Medium
  • Green - Large
  • Blue - Small
  • Blue - Medium
  • Blue - Large

When to Use CROSS JOIN:

  • Generating all possible combinations (colors × sizes)
  • Creating test data with all permutations
  • Mathematical operations requiring Cartesian product
  • Generating calendar or grid data
  • Warning: Can produce very large result sets (m × n rows)
15
Advanced

What is a FULL OUTER JOIN and how does it differ from other joins?

FULL OUTER JOIN 4 min read

FULL OUTER JOIN returns all rows from both tables, matching them where possible and filling with NULLs where there's no match.

FULL OUTER JOIN Example: 
-- Sample data
CREATE TABLE customers (id INT, name VARCHAR(50));
INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

CREATE TABLE orders (id INT, customer_id INT, amount DECIMAL);
INSERT INTO orders VALUES (101, 1, 100), (102, 2, 200), (103, 4, 300);

-- FULL OUTER JOIN
SELECT 
    c.name as customer_name,
    o.id as order_id,
    o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Result:

  • Alice - 101 - 100 (matched)
  • Bob - 102 - 200 (matched)
  • Charlie - NULL - NULL (customer with no orders)
  • NULL - 103 - 300 (order with no customer)

Comparison with Other JOINs:

  • INNER JOIN: Only matching rows from both tables
  • LEFT JOIN: All rows from left + matching rows from right
  • RIGHT JOIN: All rows from right + matching rows from left
  • FULL OUTER JOIN: All rows from both tables
  • Note: MySQL doesn't support FULL OUTER JOIN directly (use UNION of LEFT and RIGHT JOIN)
FULL OUTER JOIN in MySQL (workaround): 
-- Using UNION of LEFT and RIGHT JOIN
SELECT c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;
29
Intermediate

What is the difference between INNER JOIN and OUTER JOIN?

JOIN Types 4 min read

INNER JOIN returns only matching rows from both tables, while OUTER JOIN returns all rows from one or both tables with NULLs for non-matching rows.

Sample Data: 
-- Customers table
customer_id | name
----------- | ----------
1           | Alice
2           | Bob
3           | Charlie

-- Orders table
order_id | customer_id | amount
-------- | ----------- | ------
101      | 1          | 100
102      | 2          | 200
103      | 4          | 300   -- No matching customer
INNER JOIN (only matches): 
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Result (2 rows):
-- Alice - 101 - 100
-- Bob   - 102 - 200
LEFT OUTER JOIN (all customers + orders): 
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id;

-- Result (3 rows):
-- Alice    - 101 - 100
-- Bob      - 102 - 200
-- Charlie  - NULL - NULL
RIGHT OUTER JOIN (all orders + customers): 
SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT OUTER JOIN orders o ON c.customer_id = o.customer_id;

-- Result (3 rows):
-- Alice  - 101 - 100
-- Bob    - 102 - 200
-- NULL   - 103 - 300
FULL OUTER JOIN (all from both tables): 
SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

-- Result (4 rows):
-- Alice    - 101 - 100
-- Bob      - 102 - 200
-- Charlie  - NULL - NULL
-- NULL     - 103 - 300

Venn Diagram Visualization:

  • INNER JOIN: Intersection of both circles
  • LEFT JOIN: Entire left circle + intersection
  • RIGHT JOIN: Entire right circle + intersection
  • FULL JOIN: Both entire circles
  • OUTER JOIN is optional keyword (LEFT JOIN = LEFT OUTER JOIN)
  • MySQL doesn't support FULL OUTER JOIN (use UNION of LEFT and RIGHT)

Browse other topics

Fundamentals Queries Functions Performance Advanced