SQL JOINs

Overview

JOINs link rows across tables using related keys—typically primary and foreign keys. Choose the join type based on whether you need only matches or all rows from one side.

Syntax / Usage

-- INNER JOIN — only matching rows
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN — all users, orders may be NULL
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Multiple joins
SELECT p.title, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
JOIN brands b ON b.id = p.brand_id;

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

Examples

Users with order count:

SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;

Find products never ordered:

SELECT p.*
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;

Common Mistakes

• Cartesian product from missing ON condition
• Using INNER JOIN when LEFT JOIN is needed to keep orphan rows
• Joining on mismatched types (string id vs integer id)
• Duplicate rows when joining one-to-many without aggregation

See Also

select where-clause group-by indexes