在 PostgreSQL 连接中使用 ON 与 WHERE 子句来合并和过滤数据
介绍 在SQL查询中，可以在连接的WHERE子句或ON子句中过滤数据。本指南将研究PostgreSQL中两者之间的区别。 设置 为了检查在ON子句和WHERE子句中放置过
介绍
在 SQL 查询中，可以在连接的WHERE子句或ON子句中过滤数据。本指南将研究 PostgreSQL 中两者之间的区别。
设置
为了检查在ON子句和WHERE子句中放置过滤条件之间的区别，必须创建两个可以连接在一起的表。否则无法使用ON子句。
以下代码将创建并填充两个表，一个表包含员工数据，另一个表包含部门数据，这两个表可以根据部门编号连接在一起：
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
CREATE TABLE dept(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
);
INSERT INTO dept
(deptno, dname, loc)
VALUES
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON')
;
CREATE TABLE emp(
empno INT,
ename VARCHAR(10),
job VARCHAR(9),
mgr INT,
hiredate DATE,
sal NUMERIC(7,2),
comm NUMERIC(7,2),
deptno INT,
CONSTRAINT pk_emp PRIMARY KEY(empno),
CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
);
ALTER TABLE emp ADD CONSTRAINT fk_mgr FOREIGN KEY(mgr) REFERENCES emp(empno);
INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, NULL, 20),
(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20)
;
COMMIT;
表emp：
| 员工编号 | 埃纳梅 | 工作 | 经理 | 雇用日期 | 萨尔 | 通信 | 部门编号 |
|---|---|---|---|---|---|---|---|
| 7839 | 国王 | 总统 | 1981-11-17 | 5000.00 | 10 | ||
| 7698 | 布莱克 | 经理 | 7839 | 1981-05-01 | 2850.00 | 三十 | |
| 7782 | 克拉克 | 经理 | 7839 | 1981-06-09 | 2450.00 | 10 | |
| 7566 | 琼斯 | 经理 | 7839 | 1981-04-02 | 2975.00 | 20 | |
| 7788 | 斯科特 | 分析师 | 7566 | 0001-07-13 | 3000.00 | 20 | |
| 7902 | 福特 | 分析师 | 7566 | 1981-12-03 | 3000.00 | 20 |
表部门：
| 部门编号 | 域名 | 洛 |
|---|---|---|
| 10 | 会计 | 纽约 |
| 20 | 研究 | 达拉斯 |
| 三十 | 销售量 | 芝加哥 |
| 40 | 运营 | 波士顿 |
在内联接中使用 ON 子句与 WHERE 子句
在内连接中，在ON子句中添加过滤条件对查询结果具有相同的影响。
假设有一项业务需求，即仅当两个表中的部门编号匹配时才返回员工和部门数据，并且结果应仅包含部门名称为ACCOUNTING 的行。用户希望显示所有员工属性以及部门名称和位置。
可以编写以下三个查询来满足该要求：
SELECT emp.*, dept.dname, dept.loc
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
AND dept.dname = 'ACCOUNTING'
;
SELECT emp.*, dept.dname, dept.loc
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
WHERE
dept.dname = 'ACCOUNTING'
;
SELECT emp.*, dept.dname, dept.loc
FROM emp, dept
WHERE
emp.deptno = dept.deptno
AND dept.dname = 'ACCOUNTING'
;
| 员工编号 | 埃纳梅 | 工作 | 经理 | 雇用日期 | 萨尔 | 通信 | 部门编号 | 域名 | 洛 |
|---|---|---|---|---|---|---|---|---|---|
| 7839 | 国王 | 总统 | 1981-11-17 | 5000.00 | 10 | 会计 | 纽约 | ||
| 7782 | 克拉克 | 经理 | 7839 | 1981-06-09 | 2450.00 | 10 | 会计 | 纽约 |
无论过滤器和连接条件是放在ON子句还是WHERE子句中，所有三个查询都返回完全相同的结果。
就性能而言，过滤条件放在PostgreSQL 中的ON子句还是WHERE中都没有区别。
不过，就可读性而言，尤其是在具有多个连接的复杂查询中，将连接条件放在ON子句中更容易发现，将过滤条件放在WHERE子句中更容易发现。在上面的示例查询中，第二个查询是这样编写的。
在左连接中使用 ON 子句与 WHERE 子句
在左连接中，将过滤条件放在ON子句中会对查询结果产生不同的影响。
假设有业务需求，需要从emp表中返回所有员工，并仅查找属于ACCOUNTING部门的员工的部门信息。用户希望显示所有员工属性以及部门名称和位置。
可以通过在ON子句中放置过滤条件来满足此要求，以便在dept表与emp左连接之前根据dept.dname = 'ACCOUNTING'进行过滤：
SELECT emp.*, dept.dname, dept.loc
FROM emp
LEFT JOIN dept
ON emp.deptno = dept.deptno
AND dept.dname = 'ACCOUNTING'
;
| 员工编号 | 埃纳梅 | 工作 | 经理 | 雇用日期 | 萨尔 | 通信 | <font style="vertical-a |
|---|
免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！
阅读全文
请先 登录后发表评论 ~