Log in Sign up

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;
JoinReturns
INNER JOINrows that match in both tables
LEFT JOINall rows from the left table; NULL where the right has no match
RIGHT JOINthe same, mirrored (rarely needed – swap the tables and use LEFT)
CROSS JOINevery 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;
FunctionResult
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.