【PostgreSQL 17】5 多表连接

内连接

返回两边匹配上的行。

SELECT
	d.department_id, e.first_name, d.department_name
FROM
	employees e
JOIN
	departments d
ON
	e.department_id = d.department_id
;

简洁写法

SELECT
	d.department_id, e.first_name, d.department_name
FROM
	employees e
JOIN
	departments d
USING
	(department_id)
;

等价写法 →

SELECT
	d.department_id, e.first_name, d.department_name
FROM
	employees e, departments d
WHERE
	e.department_id = d.department_id
;

左（右）外连接

左（右）表有的行一定会返回，没有匹配到的留空。

SELECT
	d.department_id, e.first_name, d.department_name
FROM
	employees e
LEFT JOIN
	departments d
ON
	e.department_id = d.department_id
;

全外连接

两边的行都会返回，没有匹配到的留空。

SELECT
	d.department_id, e.first_name, d.department_name
FROM
	employees e
FULL JOIN
	departments d
ON
	e.department_id = d.department_id
;

交叉连接（笛卡尔积）

两边行一一匹配

SELECT
	concat(t1, '*', t2, '=', t1*t2)
FROM
	generate_series(1, 3) t1
CROSS JOIN
	generate_series(1, 2) t2
;

自然连接

同字段名匹配

SELECT
	d.department_id, e.first_name, d.department_name
FROM
	employees e
NATURAL JOIN
	departments d
;

自连接

自己连接自己

SELECT
	e.first_name, e.last_name, m.first_name, m.last_name
FROM
	employees e
LEFT JOIN
	employees m
ON
	e.manager_id = m.employee_id
;

参考资料

[1] 不剪发的Tony老师 PostgreSQL开发指南 第15节~第16节