Joins

SELECT columns
FROM table1
INNER JOIN table2 ON table1.col = table2.col;

SELECT u.first_name, o.order_id, o.total_amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- Only users who have orders, paired with their order data

Note INNER JOIN returns only rows with matching values in both tables. Users with no orders and orders with no matching user are excluded. JOIN without a prefix defaults to INNER JOIN.

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.col = table2.col;

SELECT u.first_name, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- All users appear. Users with no orders show NULL for order columns.

Note LEFT JOIN keeps all rows from the left table regardless of matches. Filtering the right table in WHERE (instead of ON) converts the LEFT JOIN into an INNER JOIN. Put right-table filters in the ON clause instead.

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.col = table2.col;

SELECT o.order_id, u.first_name
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- All users appear. Orders without a matching user show NULL for order columns.

Note RIGHT JOIN is the mirror of LEFT JOIN. Most developers avoid RIGHT JOIN and rewrite it as a LEFT JOIN by swapping table order — it is easier to read consistently.

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.col = table2.col;

SELECT u.first_name, o.order_id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

-- All users and all orders appear.
-- Unmatched rows on either side get NULLs.

Note MySQL does not support FULL OUTER JOIN natively. Simulate it with a UNION of a LEFT JOIN and a RIGHT JOIN. PostgreSQL and SQL Server support it directly.

SELECT columns
FROM table1
CROSS JOIN table2;

SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;

-- Every combination of size and color
-- If 4 sizes and 5 colors, result has 20 rows

Note CROSS JOIN produces the Cartesian product — every row in table1 paired with every row in table2. Row count = rows_in_A * rows_in_B, so it can explode quickly on large tables.

SELECT a.col, b.col
FROM table a
JOIN table b ON a.related_col = b.id;

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

-- employee | manager
-- Alice    | Bob
-- Bob      | NULL  (Bob has no manager)

Note A self join joins a table to itself. You must use different aliases to distinguish the two references. Common for hierarchical data like org charts or threaded comments.

SELECT columns
FROM table1
JOIN table2 ON ...
JOIN table3 ON ...;

SELECT
  u.first_name,
  o.order_id,
  p.product_name,
  oi.quantity
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.id = oi.product_id;

-- Connects users to their orders, line items, and product details

Note Chain as many JOINs as needed. Each JOIN condition should reference a column from a table already in the query. Watch performance on many-table joins — the optimizer may struggle with 8+ tables.

SELECT columns
FROM table1
JOIN table2 USING (shared_column);

SELECT order_id, user_id, first_name
FROM orders
JOIN users USING (user_id);

-- Works when both tables share the same column name

Note USING is shorthand for ON when the join column has the same name in both tables. The shared column appears only once in the output. Not supported in SQL Server.

SELECT columns
FROM table1
NATURAL JOIN table2;

SELECT first_name, order_id
FROM users
NATURAL JOIN orders;

-- Joins on ALL columns with matching names in both tables

Note Avoid NATURAL JOIN in production. It implicitly joins on every shared column name, so adding a column like 'status' to both tables silently changes the join condition. Always use explicit ON or USING.

SELECT columns
FROM table1
LEFT JOIN table2 ON t1.col = t2.col AND t2.filter = value;

SELECT u.first_name, o.order_id, o.total_amount
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
  AND o.status = 'completed';

-- All users. Only completed orders appear; others show NULL.

Note Putting the filter in ON vs WHERE matters for outer joins. In ON, unmatched left rows still appear with NULLs. In WHERE, those rows get eliminated, turning it into an inner join.