图片来源

内连接 Inner Join

INNER JOIN 返回两个表中满足连接条件的数据，可以简写为 JOIN。

等值连接

sql
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
JOIN job j ON (e.job_id = j.job_id)
WHERE e.emp_id = 1;

非等值连接

除了等号运算符外的条件，比如>=、!=、BETWEEN、AND 等。

sql
-- 返回当前月薪属于开发经理级别但不是开发经理的员工
SELECT e.emp_name AS "员工姓名", e.salary "月薪"
FROM employee e
JOIN job j ON (e.job_id != j.job_id
			  AND e.salary BETWEEN j.min_salary AND j.max_salary)
WHERE j.job_title = '开发经理';

外连接 Outer Join

左外连接

Left Outer Join 查询首先返回左表中的全部数据。之后，如果右表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 LEFT JOIN。

sql
-- 每个部门的员工人数
SELECT d.dept_name AS "部门名称", COUNT(e.emp_id) AS "员工人数"
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

-- 没有员工的部门
SELECT d.dept_id AS "部门编号", d.dept_name AS "部门名称"
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
WHERE e.emp_id IS NULL;

右外连接

Right Outer Join 查询首先返回右表中的全部数据。如果左表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 RIGHT JOIN。

• 右外连接和左外连接可以相互转换。

sql
-- 每个部门员工人数
-- oracle mysql mss pg
SELECT d.dept_name AS "部门名称", COUNT(e.emp_id) AS "员工人数"
FROM employee e
RIGHT JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

• sqlite 不支持右外连接（应该支持?）

全外连接

Full Outer Join 查询同时返回左表和右表中所有的数据。如果右表或者左表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 FULL JOIN。

sql
-- 获取哪些组没有员工和哪些员工没有分配到组
-- oracle mss pg
SELECT g.group_name, eg.emp_id
FROM t_group g
FULL JOIN t_emp_group eg ON (eg.group_id = g.group_id)
WHERE g.group_id IS NULL OR eg.emp_id IS NULL;

• mysql sqlite 不支持全外连接。（应该支持?）

交叉连接/笛卡尔积 Cartesian Product

CROSS JOIN 将一个表的所有数据行和另一个表的所有数据行进行两两组合，返回结果的数量为两个表中的行数相乘。

sql
SELECT COUNT(*)
FROM employee e
CROSS JOIN department d;

自然连接 Natural Join

如果连接查询同时满足以下条件，我们可以使用 USING 替代 ON 来简化连接条件的输入：

• 连接条件是等值连接。
• 两个表中的连接字段名称相同，类型也相同。

sql
-- oracle mysql pg sqlite
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
JOIN job j USING (job_id)
WHERE e.emp_id = 1;

• mss 不支持 USING。

如果等值连接条件中包含了两个表中所有同名同类型的字段，查询语句可以继续进行简化。

sql
-- oracle mysql pg sqlite
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
NATURAL JOIN job j
WHERE e.emp_id = 1;

• mss 不支持自然连接。

自连接 Self Join

Self Join 查询指的是一个表和它自己进行连接查询。自连接本质上并没有什么特殊之处，主要用于处理那些对自身进行了外键引用的表。

sql
-- 查看员工以及他的经理
SELECT e.emp_name AS "员工姓名", m.emp_name AS "经理姓名"
FROM employee e
LEFT JOIN employee m ON (m.emp_id = e.manager)
WHERE e.emp_id = 9;