The examples use customers and orders from MySQL Databases, Tables and Data Types: each order has a customer_id.
Joins
INNER JOIN – only matching rows
SELECT o.id, o.total, c.name, c.email
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'paid';JOIN and INNER JOIN are the same. Short table aliases (o, c) keep queries readable.
LEFT JOIN – all rows from the left table
-- every customer, with their orders if they have any
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;
-- customers who have never ordered
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;| Join | Returns |
|---|---|
INNER JOIN | rows that match in both tables |
LEFT JOIN | all rows from the left table; NULL where the right has no match |
RIGHT JOIN | the same, mirrored (rarely needed – swap the tables and use LEFT) |
CROSS JOIN | every combination of rows |
MySQL has no FULL OUTER JOIN; combine a LEFT and a RIGHT join with UNION if you need one.
Put a condition on the right table in the ON clause, not in WHERE, if you want to keep the left rows:
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';Grouping and aggregate functions
SELECT status, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY status;| Function | Result |
|---|---|
COUNT(*) | number of rows |
COUNT(col) | number of non-NULL values |
COUNT(DISTINCT col) | number of different values |
SUM(col), AVG(col) | total, average |
MIN(col), MAX(col) | smallest, largest |
GROUP_CONCAT(col) | values joined into one string |
HAVING – filtering groups
WHERE filters rows before grouping, HAVING filters the groups afterwards:
SELECT c.name, COUNT(o.id) AS orders, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
HAVING spent > 500
ORDER BY spent DESC;Every selected column must either be in GROUP BY or inside an aggregate function. MySQL enforces this with the ONLY_FULL_GROUP_BY mode; the error "isn't in GROUP BY" means exactly this.
Revenue per month
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(total) AS revenue
FROM orders
WHERE status IN ('paid', 'shipped')
GROUP BY month
ORDER BY month;Subqueries
-- orders above the average
SELECT id, total FROM orders
WHERE total > (SELECT AVG(total) FROM orders);
-- customers with at least one paid order
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid');Common table expressions (WITH)
CTEs (MySQL 8.0, MariaDB 10.2) give a subquery a name and make long queries readable:
WITH spend AS (
SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
)
SELECT c.name, s.spent
FROM spend s
JOIN customers c ON c.id = s.customer_id
WHERE s.spent > 1000;Window functions
Window functions (MySQL 8.0, MariaDB 10.2) calculate across rows without collapsing them into groups:
-- each customer's latest order
SELECT * FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders o
) t
WHERE rn = 1;
-- running total per day
SELECT DATE(created_at) AS day,
SUM(total) AS revenue,
SUM(SUM(total)) OVER (ORDER BY DATE(created_at)) AS running_total
FROM orders
GROUP BY day;Other window functions: RANK(), DENSE_RANK(), LAG(), LEAD().
UNION
SELECT email FROM customers
UNION -- removes duplicates; UNION ALL keeps them and is faster
SELECT email FROM newsletter_subscribers;Joins on large tables need indexes on the join columns – see MySQL Indexes and Query Performance.
Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.