SQL JOIN Cheatsheet - SQL JOIN Query Reference
SQL 多表关联查询必备命令都在这里了，从内连接到外连接，按连接类型分类整理，写关联查询时直接复制。
Back to DatabasesINNER JOIN 5
SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id只返回两表匹配的行
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id简写 JOIN（默认 INNER）
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id AND o.status = 'paid'ON 子句多个条件，共同决定匹配
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total > 100JOIN 后用 WHERE 过滤整体结果
SELECT u.name, o.total, p.amount FROM users u JOIN orders o ON u.id = o.user_id JOIN payments p ON o.id = p.order_id多表 INNER JOIN 链式关联
LEFT / RIGHT JOIN 6
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id返回左表所有行，右表无匹配则为 NULL
SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id返回右表所有行，左表无匹配则为 NULL
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id WHERE orders.id IS NULL查找左表独有（无订单的用户）
SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'ON 条件放在 LEFT JOIN 不影响左表行数
SELECT * FROM orders o RIGHT JOIN users u ON u.id = o.user_id WHERE o.id IS NULLRIGHT JOIN 版反连接（查找无订单用户）
SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.idLEFT JOIN + 聚合，统计所有用户含零订单
FULL / CROSS JOIN 5
SELECT * FROM users FULL OUTER JOIN orders ON users.id = orders.user_id返回两表所有行，无匹配则为 NULL
SELECT * FROM users u FULL JOIN orders o ON u.id = o.user_id WHERE u.id IS NULL OR o.user_id IS NULL查找两表独有（对称差）
SELECT * FROM table1 CROSS JOIN table2笛卡尔积，每行与另一表所有行组合
SELECT * FROM table1, table2隐式 CROSS JOIN（逗号分隔）
SELECT * FROM sizes CROSS JOIN colors生成所有规格组合（如尺码 × 颜色）
自连接与多表 5
SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id自连接（员工-经理关系）
SELECT a.name, b.name AS parent FROM categories a JOIN categories b ON a.parent_id = b.id自连接（分类-父分类）
SELECT * FROM a JOIN b ON a.id = b.a_id JOIN c ON b.id = c.b_id多表三表连接
SELECT t1.name AS grandchild, t3.name AS grandparent FROM tree t1 JOIN tree t2 ON t1.parent_id = t2.id JOIN tree t3 ON t2.parent_id = t3.id多级自连接（祖孙三代）
SELECT u.name, o.total, p.amount FROM users u JOIN orders o ON u.id = o.user_id JOIN payments p ON o.id = p.order_id三表关联（用户-订单-支付）
JOIN 与聚合 5
SELECT u.name, COUNT(o.id) AS order_count FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id每个用户的订单数
SELECT u.name, SUM(o.total) AS total_spent FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id HAVING SUM(o.total) > 1000HAVING 过滤聚合结果（消费超 1000）
SELECT c.name, AVG(o.total) AS avg_order FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.id分组求平均值
SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id HAVING COUNT(o.id) = 0LEFT JOIN + HAVING 查找零订单用户
SELECT DATE(o.created_at) AS d, COUNT(*) FROM orders o JOIN users u ON u.id = o.user_id GROUP BY DATE(o.created_at)按日期分组统计
JOIN 性能优化 6
EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id查看执行计划
EXPLAIN ANALYZE SELECT * FROM users u JOIN orders o ON u.id = o.user_id实际执行并显示耗时（PostgreSQL / MySQL 8.0+）
CREATE INDEX idx_orders_user_id ON orders(user_id)为外键建索引加速 JOIN
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id只查需要的列，避免 SELECT * 回表
SELECT * FROM users u STRAIGHT_JOIN orders o ON u.id = o.user_idMySQL 强制左表为驱动表按指定顺序连接
SELECT * FROM users u JOIN orders o FORCE INDEX(idx_user_id) ON u.id = o.user_idMySQL 强制使用指定索引
USING 与自然连接 4
SELECT * FROM users JOIN orders USING (user_id)USING 简写（两表关联列同名）
SELECT * FROM users u JOIN orders o USING (user_id, tenant_id)USING 多列简写，结果合并同名列
SELECT * FROM users NATURAL JOIN orders自然连接（自动按所有同名列匹配，慎用）
SELECT * FROM users u JOIN orders o ON u.id = o.user_id AND u.tenant_id = o.tenant_id多列关联（可用 USING 简化）
子查询与 EXISTS 5
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)查找有订单的用户
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)查找无订单的用户
SELECT * FROM orders o WHERE o.user_id IN (SELECT id FROM users WHERE status = 'active')IN 子查询过滤
SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt FROM users u相关子查询（返回标量）
SELECT * FROM orders WHERE user_id = (SELECT MAX(user_id) FROM orders)子查询返回单值
💡 Tips
- LEFT JOIN 的 WHERE 条件如果放在 ON 里，只影响右表匹配；放在 WHERE 里会过滤整个结果集。
- CROSS JOIN 会产生笛卡尔积，数据量大时慎用。
- 用表别名（u、o）可以让多表查询更简洁易读。
- EXISTS 子查询在某些场景下比 JOIN 性能更好，特别是只需要判断存在性时。
- JOIN 的连接列建议建索引，否则会触发全表扫描导致性能下降。
- USING(a, b) 比 ON x.a = y.a AND x.b = y.b 更简洁，且结果集会合并同名列。