sql
SELECT o.id, u.email, o.status
FROM orders o
INNER JOIN users u ON u.id = o.user_id;Notes
`INNER JOIN` excludes rows without a matching parent or child.
MySQL Joins, Subqueries, and CTEs Cheatsheet
inner joins, left joins, self joins, subqueries, derived tables, and common table expressions in MySQL.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
joins
Combine related tables with inner and outer joins.
Left join to keep unmatched parent rows
Return all rows from the left table and matching rows from the right table.
sqlANYjoinleft-join
sql
SELECT u.id, u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;Notes
Rows from `users` remain even when there is no matching row in `orders`.
sql
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;Notes
A classic reporting query for dashboards and admin pages.
sql
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;Notes
Self joins are common in hierarchical data such as org charts.
subqueries and ctes
Filter, rank, and reshape results with subqueries and common table expressions.
sql
SELECT id, email
FROM users
WHERE id IN (
SELECT user_id FROM orders WHERE status = 'paid'
);Notes
A simple subquery pattern for membership-based filtering.
sql
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);Notes
`EXISTS` is often clearer and more efficient than counting when you only care whether a match exists.
Use a CTE to isolate recent orders
Create a readable query pipeline with `WITH`.
sqlANYctewithreporting
sql
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;Notes
CTEs help break complex logic into named steps.
sql
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;Notes
Derived tables are handy when you want one summarized row per parent entity.
More in MySQL
MySQL Procedures, Triggers, and Events Cheatsheet
stored procedures, stored functions, delimiters, call, triggers, and scheduled events in MySQL.
MySQL Functions, JSON, and Date Cheatsheet
now, date_add, date_sub, date_format, timestampdiff, json_extract, json_set, json_object, concat, coalesce, and case in MySQL.
MySQL Backup, Restore, Import, and Export Cheatsheet
mysqldump, mysql restore, load data infile, select into outfile, mysqlimport, and MySQL Shell dump/load workflows.
MySQL Users, Grants, and Privileges Cheatsheet
create users, alter passwords, grant privileges, show grants, revoke access, and manage MySQL accounts safely.
MySQL Transactions and Locking Cheatsheet
start transaction, commit, rollback, savepoints, select for update, isolation levels, and InnoDB lock troubleshooting.
MySQL Indexes, EXPLAIN, and Performance Cheatsheet
index design, show index, explain, explain analyze, analyze table, and practical performance tuning workflows in MySQL.