Context

Joins are fundamental in SQL because most real datasets are split across related tables. Interviewers often ask this question to test whether you understand both result shape and business implications.

Core Question

Explain the main types of SQL joins: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN, and optionally SELF JOIN as a pattern. For each, describe what rows are returned and how unmatched records are handled.

Then give a practical use case for a LEFT OUTER JOIN. Your answer should explain why LEFT OUTER JOIN is the correct choice instead of INNER JOIN, and what kind of business question it helps answer.

Scope Guidance

The interviewer expects a clear conceptual explanation, not a long theory lecture. Focus on row-matching behavior, NULL handling, and one realistic example such as customers without orders, employees without managers, or products with no sales.

INNER JOIN

An INNER JOIN returns only rows where the join condition matches in both tables. If a row exists in one table but not the other, it is excluded from the result.

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

LEFT OUTER JOIN

A LEFT OUTER JOIN returns all rows from the left table and matching rows from the right table. If there is no match on the right side, the right-table columns appear as NULL.

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

RIGHT OUTER JOIN and FULL OUTER JOIN

A RIGHT OUTER JOIN keeps all rows from the right table, while a FULL OUTER JOIN keeps all rows from both tables. FULL OUTER JOIN is useful when you need to identify unmatched records on either side.

SELECT c.customer_id, o.order_id
FROM customers c
FULL OUTER JOIN orders o
  ON c.customer_id = o.customer_id;

CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables, meaning every row from the first table is paired with every row from the second. It is useful for generating combinations, but can grow very large quickly.

SELECT d.day_name, s.shift_name
FROM days d
CROSS JOIN shifts s;

NULL Handling in Outer Joins

Outer joins introduce NULLs for missing matches, and those NULLs are often the key to answering business questions about missing relationships. For example, filtering with WHERE o.order_id IS NULL after a LEFT JOIN finds customers who never placed an order.

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