SQL Cheatsheet

Joins

Use this SQL reference while you build software engineering projects, review code for technical interview prep, or polish examples for a software engineer resume.

JOIN Syntax Overview

SELECT columns
FROM   table1 [AS alias1]
JOIN_TYPE table2 [AS alias2]
    ON table1.col = table2.col
[JOIN_TYPE table3 ON ...]
WHERE  ...;

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN are the core types. JOIN without a qualifier means INNER JOIN.

INNER JOIN

Returns only rows that have a match in both tables.

SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- Shorthand: JOIN = INNER JOIN
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Multiple join conditions
SELECT *
FROM employees e
JOIN dept_assignments da ON da.emp_id = e.id
                         AND da.active = TRUE;

LEFT (OUTER) JOIN

Returns all rows from the left table plus matched rows from the right. Unmatched right columns are NULL.

-- All users, even those with no orders
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Find users with NO orders (anti-join pattern)
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- LEFT OUTER JOIN (OUTER is optional)
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.a_id;

RIGHT (OUTER) JOIN

Returns all rows from the right table plus matched rows from the left. Unmatched left columns are NULL. Equivalent to a LEFT JOIN with tables swapped — prefer LEFT JOIN for readability.

-- All orders, even if user was deleted
SELECT u.name, o.id, o.total
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

FULL OUTER JOIN

Returns rows from both tables; unmatched sides are NULL.

SELECT a.id AS a_id, b.id AS b_id
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id;

-- Rows with no match on either side
SELECT a.id AS a_id, b.id AS b_id
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.a_id
WHERE a.id IS NULL OR b.id IS NULL;

-- MySQL does not support FULL OUTER JOIN natively; emulate with UNION:
SELECT a.id, b.id FROM a LEFT  JOIN b ON a.id = b.a_id
UNION
SELECT a.id, b.id FROM a RIGHT JOIN b ON a.id = b.a_id;

CROSS JOIN

Produces the Cartesian product — every row in table1 paired with every row in table2 (n × m rows).

-- Every combination of sizes and colors
SELECT s.name AS size, c.name AS color
FROM sizes s
CROSS JOIN colors c;

-- Implicit cross join (comma syntax — avoid for clarity)
SELECT * FROM sizes, colors;

-- Common use: generate series of rows
SELECT d.day, p.product_id
FROM (VALUES ('Mon'),('Tue'),('Wed'),('Thu'),('Fri')) AS d(day)
CROSS JOIN products p;

SELF JOIN

A table joined to itself, typically to compare rows within the same table.

-- Find employees and their managers (both in same table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

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

-- Hierarchical traversal (single-level)
SELECT parent.name AS category, child.name AS subcategory
FROM categories parent
JOIN categories child ON child.parent_id = parent.id;

NATURAL JOIN

Automatically joins on all columns with identical names in both tables. Fragile — avoid in production code.

-- Joins on any column name that matches in both tables
SELECT * FROM orders NATURAL JOIN products;
-- Equivalent to: ON orders.id = products.id AND orders.name = products.name ...
-- (every shared column name is an implicit join condition)

JOIN with USING

When join columns share the same name, USING is a concise alternative to ON.

-- Equivalent to ON o.user_id = u.user_id, but column appears only once in *
SELECT * FROM users u JOIN orders o USING (user_id);

-- Multiple shared columns
SELECT * FROM a JOIN b USING (col1, col2);

Multiple Joins

-- Three-way join
SELECT u.name, o.id AS order_id, p.name AS product, oi.qty
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;

-- Mix of join types
SELECT u.name, o.id, c.code AS coupon
FROM users u
JOIN orders o        ON o.user_id  = u.id
LEFT JOIN coupons c  ON c.order_id = o.id;   -- optional coupon

Anti-Join Patterns

Find rows in one table with no match in another.

-- Method 1: LEFT JOIN + IS NULL (most portable)
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Method 2: NOT EXISTS
SELECT u.*
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Method 3: NOT IN (avoid if subquery can return NULL)
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

Semi-Join Patterns

Find rows in one table that have a match, without duplicating rows.

-- Method 1: EXISTS (returns each user once even with multiple orders)
SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Method 2: DISTINCT (deduplicate after join)
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Method 3: IN (simple, but can be slow on large sets)
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Lateral Joins

A LATERAL join lets the right side reference columns from the left side (like a correlated subquery per row).

-- PostgreSQL: LATERAL
SELECT u.name, last_order.total
FROM users u
LEFT JOIN LATERAL (
    SELECT total FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 1
) last_order ON TRUE;

-- MySQL: LATERAL (8.0+)
-- SQL Server: APPLY (CROSS APPLY / OUTER APPLY)
SELECT u.name, lo.total
FROM users u
OUTER APPLY (
    SELECT TOP 1 total FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
) lo;

JOIN Performance Tips

-- Always index foreign key columns
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Filter before joining when possible (push WHERE down)
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01'   -- index on orders.created_at helps
  AND u.status = 'active';           -- index on users.status helps

-- Avoid joining on expressions (prevents index use)
-- Bad:
ON LOWER(a.email) = LOWER(b.email)
-- Better: normalize data at write time, join on clean columns
ON a.email_lower = b.email_lower

-- EXPLAIN / EXPLAIN ANALYZE to inspect join strategy
EXPLAIN ANALYZE
SELECT * FROM users u JOIN orders o ON o.user_id = u.id;