SQL Joins

SQL provides several join types, each with different behavior for matched and unmatched rows:

INNER JOIN Returns rows where the join condition matches in both tables. Non-matching rows from either table are excluded.

SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT (OUTER) JOIN Returns all rows from the left table + matched rows from the right. Where there is no match, right-table columns are NULL.

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- Customers with no orders appear with NULL order_id

RIGHT (OUTER) JOIN Returns all rows from the right table + matched rows from the left. Rarely used — rewriting as a LEFT JOIN with tables swapped is more readable.

FULL (OUTER) JOIN Returns all rows from both tables. Where there is no match on either side, NULL fills the missing columns. Not natively supported in MySQL (use UNION of LEFT JOIN and RIGHT JOIN).

SELECT e.name, d.name FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

CROSS JOIN Returns the Cartesian product — every row of table A paired with every row of table B. No join condition.

SELECT * FROM sizes CROSS JOIN colors;  -- generates all size+color combinations

SELF JOIN A table joined to itself using aliases — used for hierarchical data.

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

Interview tip: Know the difference between an INNER JOIN result and a LEFT JOIN result on a diagram or with a concrete example — this is a very common whiteboard question.

A SQL JOIN works by combining rows from two tables based on a matching condition defined in the ON clause. The database engine evaluates every candidate pair and returns those satisfying the condition.

Mechanical process:

1. Identify source tables: The query planner determines which tables to join and in what order.

2. Apply the join algorithm (chosen by the query optimizer):

   • Nested Loop Join: For each row in the outer table, scan the inner table for matches. O(n × m) — efficient when the inner table is small or has an index.
   • Hash Join: Build a hash table from the smaller table's join key, then probe it with rows from the larger table. O(n + m) — efficient for large unsorted tables without relevant indexes.
   • Merge Join: Sort both tables by the join key, then merge them in one pass. O(n log n + m log m) — efficient when both sides are already sorted (e.g., joined on indexed columns).

3. Filter with the ON condition: Only rows where the condition evaluates to TRUE are included in the result (for INNER JOIN).

4. Handle outer join NULLs: For LEFT/RIGHT/FULL joins, rows with no match are included with NULLs for the non-matching side.

Example with execution:

SELECT e.name, d.name
FROM employees e              -- outer table (or smaller table in hash join)
INNER JOIN departments d ON e.department_id = d.id;  -- join condition

-- If department_id is indexed, a nested loop + index scan is used
-- If not indexed, a hash join or sort-merge join is chosen

Interview follow-up: What happens if you join on a column with NULLs? NULL = NULL evaluates to UNKNOWN in SQL, so rows with NULL join keys never match — they are excluded from INNER JOINs.

A self join is a join where a table is joined to itself. It is used when rows in a table have a relationship with other rows in the same table.

You must use aliases to distinguish between the two "copies" of the table:

Most common use case — hierarchical/parent-child relationships:

-- employees table: id, name, manager_id (references employees.id)
SELECT
    e.name       AS employee,
    m.name       AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name, e.name;

Employees with no manager (the CEO) have manager_id = NULL and will appear with NULL in the manager column if using LEFT JOIN.

Finding pairs within a table:

-- Find all pairs of employees in the same department
SELECT a.name AS employee_1, b.name AS employee_2, a.department_id
FROM employees a
JOIN employees b ON a.department_id = b.department_id
                AND a.employee_id < b.employee_id  -- prevent (A,B) and (B,A) duplicates
ORDER BY a.department_id;

Other self join use cases:

• Bill-of-materials: parts that are made of other parts
• Category trees: categories with parent categories
• Org charts: any multi-level reporting hierarchy
• Finding consecutive or related records (e.g., next appointment after the current one)

Interview tip: When describing a self join, always mention the alias requirement. Also explain why LEFT JOIN vs INNER JOIN matters — in the employee-manager example, INNER JOIN would exclude employees with no manager (the top of the hierarchy).

A cross join (also called a Cartesian join) returns the Cartesian product of two tables — every row from the first table combined with every row from the second. If table A has 10 rows and table B has 5 rows, the result has 50 rows.

Syntax:

-- Explicit CROSS JOIN
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

-- Implicit (old syntax — comma-separated tables with no WHERE condition)
SELECT s.size, c.color FROM sizes s, colors c;

Legitimate use cases:

1. Generating all combinations:

   -- All possible shirt configurations
   SELECT s.size, c.color, m.material
   FROM sizes s
   CROSS JOIN colors c
   CROSS JOIN materials m;
   

2. Generating date or number sequences:

   -- Generate a series of 100 numbers using a cross join on a small numbers table
   SELECT (tens.n * 10 + units.n) AS num
   FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS tens(n)
   CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS units(n)
   ORDER BY num;
   

3. Testing or seeding databases with all combinations of test data

When to avoid cross joins: Accidental cross joins (forgetting a WHERE or ON condition in multi-table queries) produce enormous result sets that can crash queries. A cross join of two 1-million-row tables produces 1 trillion rows.

Interview note: Interviewers sometimes describe a query producing unexpected row explosions and ask you to diagnose the cause — accidental cross joins are a classic culprit.

A natural join automatically joins two tables on all columns with the same name and compatible data type. No explicit ON or USING clause is required.

Syntax:

SELECT * FROM employees NATURAL JOIN departments;
-- Automatically joins on any column that appears with the same name in both tables
-- If both have 'department_id', it joins on that

When it might seem useful: If tables are designed with consistent naming conventions (foreign keys have the same name as the primary key they reference), natural join can be concise.

Why natural join is generally avoided in practice:

1. Fragile: Adding a column with the same name to either table silently changes the join condition, causing wrong results with no error.

2. Implicit behavior is opaque: Readers of the query must know both table schemas to understand what columns are being joined on. Explicit ON or USING is self-documenting.

3. Inconsistent naming breaks it: Real-world schemas often have employee.id and order.employee_id — different names for the same relationship — which natural join cannot handle.

4. No support in all databases: SQL Server does not support NATURAL JOIN. PostgreSQL, MySQL, and Oracle do.

Best practice (the interview answer): Use explicit INNER JOIN ... ON table1.col = table2.col or JOIN ... USING (column_name) instead. Both are clear, maintainable, and schema-change-resistant.

-- Prefer this:
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;

-- Over this:
SELECT * FROM employees NATURAL JOIN departments;

An INNER JOIN returns only rows where the join condition matches in both tables. Rows with no match in either table are excluded.

A LEFT JOIN returns all rows from the left table, plus matched rows from the right. Where there is no match, right-table columns are NULL.

-- INNER JOIN: only customers with orders
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- LEFT JOIN: ALL customers, even those with no orders
SELECT c.name, o.order_id  -- o.order_id will be NULL for customers with no orders
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

When to use INNER JOIN: You only want rows where the relationship exists — e.g., orders with their products.

When to use LEFT JOIN:

• Finding rows with no match ("anti-join" pattern): WHERE o.order_id IS NULL
• Reporting that must include all entities, even those with zero activity
• Preserving a "base" table's completeness

LEFT JOIN as anti-join (find customers who never ordered):

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