pg14-sql基础（四）-多表联查

多表联查

内联查询

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

左外联查询

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

右外联查询

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

全外联查询

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

额外条件过滤

SELECT e.department_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d
-- ON e.department_id = d.department_id AND d.department_name = 'IT'; --仍旧返回左连接结果从，不满足字段为null
ON e.department_id = d.department_id
WHERE d.department_name = 'IT';

交叉连接查询（笛卡尔积）

SELECT concat(t1, '*', t2, '=', t1*t2) --字符串连接
FROM generate_series(1,9) t1 --生成数字序列1-9
CROSS JOIN generate_series(1,9) t2;

等值连接

SELECT *
FROM employees e
JOIN departments d
--ON d.department_id = e.department_id;
USING (department_id);--两张表都有同一个字段department_id

SELECT *
FROM employees e
NATURAL JOIN departments d;--多字段相同

自连接查询

--自引用该表的manager_id的字段填写的是employee_id的字段
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;

多表连接查询

SELECT e.first_name, e.last_name, d.department_name, j.job_title
FROM employees e
JOIN departments d
ON e.manager_id = d.department_id
JOIN jobs j
ON e.job_id = j.job_id;