JOINs — INNER, LEFT, RIGHT, FULL, CROSS — visual mental model

Intermediate5 min read·eng-06-002

sqlinterview

Concept

JOINs combine rows from two or more tables based on a related column. Every JOIN has a join condition — the ON clause.

Venn diagram mental model (simplified but intuitive):

INNER JOIN: Only rows where BOTH sides match. The intersection.
LEFT JOIN (LEFT OUTER JOIN): All rows from the LEFT table. Matching rows from the RIGHT. Non-matching right rows are NULL.
RIGHT JOIN: All rows from the RIGHT table. Non-matching left rows are NULL. (Prefer LEFT JOIN — reorder tables instead.)
FULL OUTER JOIN: All rows from BOTH tables. Non-matching sides are NULL. Not supported in MySQL — emulate with UNION of LEFT and RIGHT.
CROSS JOIN: Every row from the left paired with every row from the right. No ON clause. N × M rows. Rarely used intentionally — often a bug.

The LEFT JOIN null check trick: To find rows in A that have NO match in B: LEFT JOIN B ON ... WHERE B.id IS NULL. Faster than a subquery in many cases.

JOIN performance: JOINs perform well when the join columns are indexed. Always index foreign keys. EXPLAIN your JOIN queries — look for "Full Table Scan" on joined tables.

Self-join: Join a table to itself. Common for hierarchical data. users u1 JOIN users u2 ON u2.id = u1.manager_id.

Multiple JOINs: You can chain JOINs. Each JOIN adds a table to the FROM clause. Order matters for readability, less so for performance (the optimizer reorders).

USING clause: JOIN orders ON orders.user_id = users.id can be written as JOIN orders USING (user_id) when the column names match. Less verbose.

Code Example

sql

-- INNER JOIN — only users who have at least one order
SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed';

-- LEFT JOIN — all users, even those with no orders (NULL order columns)
SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS lifetime_value
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- LEFT JOIN + NULL check — users who have NEVER placed an order
SELECT u.id, u.name, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Multiple JOINs — users, their orders, and the products in those orders
SELECT
    u.name       AS customer,
    o.id         AS order_id,
    p.name       AS product,
    oi.quantity,
    oi.unit_price
FROM users u
JOIN orders      o  ON o.user_id    = u.id
JOIN order_items oi ON oi.order_id  = o.id
JOIN products    p  ON p.id         = oi.product_id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;

-- Self-join — employees and their managers
SELECT
    e.name   AS employee,
    m.name   AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- FULL OUTER JOIN in MySQL (emulated)
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

php

<?php
// Eloquent equivalent
$users = User::with('orders')->get(); // eager load (separate SELECT + IN)

// Manual join when you need columns from both tables
$results = DB::table('users as u')
    ->leftJoin('orders as o', 'o.user_id', '=', 'u.id')
    ->select('u.name', DB::raw('COUNT(o.id) as order_count'))
    ->groupBy('u.id', 'u.name')
    ->get();

PreviousSQL fundamentals — SELECT, FROM, WHERE, ORDER BY, LIMITSQL Deep Dive NextIndexes — B-tree, hash, full-text — when each is usedSQL Deep Dive