SELECT with JOINs

Code

-- INNER JOIN: only matching rows
SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed';

-- LEFT JOIN: all customers, with NULLs where no orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY order_count DESC;

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

Explanation

JOINs combine rows from two tables based on a related column, with INNER JOIN returning only matches and LEFT JOIN keeping all rows from the left table. Self-joins use table aliases to relate a table to itself, useful for hierarchical data. Always qualify ambiguous column names with the table alias.

More MySQL Snippets