MySQL Joins, Subqueries, and CTEs Cheatsheet

SELECT o.id, u.email, o.status
FROM orders o
INNER JOIN users u ON u.id = o.user_id;

# Return rows where both tables match.

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

# Return all rows from the left table and matching rows from the right table.

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;

# Count child rows per parent.

SELECT e.id, e.full_name, m.full_name AS manager_name
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

# Join a table to itself using aliases.

SELECT id, email
FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE status = 'paid'
);

# Return users who have at least one paid order.

SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

# Return users who have orders.

WITH recent_orders AS (
  SELECT id, user_id, total_amount, created_at
  FROM orders
  WHERE created_at >= NOW() - INTERVAL 30 DAY
)
SELECT user_id, COUNT(*) AS recent_order_count, SUM(total_amount) AS revenue
FROM recent_orders
GROUP BY user_id;

# Create a readable query pipeline with `WITH`.

SELECT u.id, u.email, x.last_order_at
FROM users u
LEFT JOIN (
  SELECT user_id, MAX(created_at) AS last_order_at
  FROM orders
  GROUP BY user_id
) x ON x.user_id = u.id;

# Compute an aggregate once and join it back.