PostgreSQL15——查询详解

一、简单查询

1.1、单表查询

select first_name, last_name
from employees;

select first_name, last_name, salary * 12 as annual_income
from employees;

1.2、无表查询

有的时候，我们可能会遇到这样的查询语句：

也就是省略了FROM 子句的查询，这是PostgreSQL 的扩展语法。这种查询通常用于返回系统信息，或者当作计算器使用。需要注意的是，并非所有的关系数据库都支持这种写法，因此它并不具有可移植性。

1.3、消除重复结果

SQL 提供了消除查询结果重复值的 DISTINCT 关键字。

select distinct department_id
from employees;

DISTINCT 也可以针对多个字段进行去重操作，例如：

select distinct first_name, last_name
from employees;

1.4、使用注释

标准SQL注释：

-- 这是标准SQL注释方式
SELECT DISTINCT first_name
FROM employees;

注释的内容会在语法分析之前替换成空格，因此不会被服务器执行。另外，PostgreSQL 还支持 C 语言风格的注释方法（/* … */）。例如：

SELECT DISTINCT first_name
/* 这是一个多行注释，
DISTINCT 表示排除重复值
/* 这是一个嵌套的注释 */
*/
FROM employees;

二、查询条件

2.1、WHERE子句

-- 语法
SELECT column1, column2, ...
FROM table
WHERE conditions;

WHERE 子句位于 FROM 之后，用于指定一个或者多个逻辑条件，用于过滤返回的结果。满足条件的行将会返回，否则将被忽略。PostgreSQL 提供了各种运算符和函数，用于构造逻辑
条件。

以上这些运算符的作用都比较明显，不做详细介绍。需要注意的是 BETWEEN 包含了两端的值，等价于>=加上<=。

select first_name, last_name, salary
from employees
where salary between 11000 and 12000;

2.2、模式匹配

PostgreSQL 支持各种字符串模式匹配的功能。最简单的方式就是使用 LIKE 运算符，以下查询返回了姓氏（last_name）以“Kin”开头的员工。

其中的百分号（%）可以匹配零个或者多个任意字符；另外，下划线（_）可以匹配一个任意字符。例如：

• “%en”匹配以“en”结束的字符串；
• “%en%”匹配包含“en”的字符串；
• “B_g”匹配“Big”、“Bug”等。

如果字符串中存在这两个通配符（%或），可以在它们前面加上一个反斜杠（\）进行转义。

也可以通过 ESCAPE 子句指定其他的转义字符。

另外，NOT LIKE 运算符匹配与 LIKE 相反的结果。

LIKE 运算符区分大小写，PostgreSQL 同时还提供了不区分大小写的 ILIKE 运算符。

2.3、空值判断

根据SQL 标准，空值使用 NULL 表示。空值是一个特殊值，代表了未知数据。如果使用常规的比较运算符与 NULL 进行比较，总是返回空值。

NULL = 0; -- 结果为空值
NULL = NULL; -- 结果为空值
NULL ！= NULL; -- 结果为空值

如果在查询条件中使用这种方式，将不会返回任何结果。因此，对于 NULL 值的比较，需要使用特殊的运算符：IS NULL。

IS NOT NULL 执行与 IS NULL 相反的操作，返回值不为空的数据。

2.4、复杂条件

WHERE 子句可以包含多个条件，使用逻辑运算符（AND、OR、NOT）将它们进行组合，并根据最终的逻辑值进行过滤。

AND（逻辑与）运算符的逻辑真值表如下：

对于 AND 运算符，只有当它两边的结果都为真时，最终结果才为真；否则最终结果为假，不返回结果。

OR（逻辑或）运算符的逻辑真值表如下：

OR 逻辑或运算符只要有一个条件为真，结果就为真。以下查询返回薪水为 10000，或者姓氏为“King”的员工：

对于逻辑运算符 AND 和 OR，需要注意的是，它们使用短路运算。也就是说，只要前面的表达式能够决定最终的结果，不进行后面的计算。这样能够提高运算效率。因此，以下语句不会产生除零错误：

SELECT 1 WHERE 1 = 0 AND 1/0 = 1;
SELECT 1 WHERE 1 = 1 OR 1/0 = 1;

还需要注意的一个问题是，当我们组合 AND 和 OR 运算符时，AND 运算符优先级更高，总是先执行。

由于 AND 优先级高，查询返回的是薪水为 24000 并且姓氏为“King”的员工，或者薪水为10000 的员工。如果相要返回姓氏为“King”，并且薪水为 10000 或24000 的员工，可以使用括号修改优先级：

NOT（逻辑非）运算符用于取反操作，它的逻辑真值表如下：

注意，对于未知的 NULL 值，经过 NOT 处理之后仍然是未知值。除此之外，NOT 还可以结合前面介绍的运算符一起使用：

• NOT BETWEEN，位于范围之外
• NOT IN，不在列表之中
• NOT LIKE，不匹配模式
• NOT IS NULL，不为空，等价于 IS NOT NULL

最后，当查询条件包含复杂逻辑时，它们的运算优先级从高到低排列如下：

三、排序显示

3.1、单列排序

单列排序是指按照某个字段或者表达式进行排序，用法如下：

SELECT column1, column2, ...
FROM table
ORDER BY column1 [ASC | DESC];

ORDER BY 表示按照某个字段进行排序，ASC 表示升序排序（Ascending），DESC 表示降序排序（Descending），默认值为 ASC。

以下查询返回部门编号为 60 的员工，并且按照薪水从高到低进行排序显示：

3.2、多列排序

-- 语法
SELECT column1, column2, ...
FROM table
ORDER BY column1 ASC, column2 DESC, ...;

以下查询返回部门编号为 60 的员工，并且按照薪水从高到低进行排序显示，如果薪水相同，再按照名字（first_name）降序排列：

ORDER BY 后的排序字段可以是SELECT 列表中没有的字段。以下语句返回了员工的姓名和薪水，按照入职先后进行显示：

SELECT first_name, last_name, salary
FROM employees
ORDER BY hire_date;

除了在 ORDER BY 后指定字段名或者表达式之外，也可以简单的使用它们在SELECT 列表中出现的顺序来表示：

-- 以上语句表示先按照第 1 个字段（first_name）进行排序，再按照第 3 个字段（salary）进行排序。
SELECT first_name,last_name,salary
FROM employees
ORDER BY 1, 3;

PostgreSQL 对于字符类型的数据进行排序时不区分大小写， “CAT”和“cat”顺序相同。

3.3、空值排序

select first_name, last_name, commission_pct
from employees
where first_name = 'Peter'
order by commission_pct;

以上查询按照佣金百分比（commission_pct）进行升序显示。对于“Peter Vargas”，由于他没有佣金提成，相应的值为空，PostgreSQL 默认将他排在了最后。

PostgreSQL 支持使用 NULLS FIRST（空值排在最前）和 NULLS LAST（空值排在最后）指定空值的排序位置；升序排序时默认为 NULLS LAST，降序排序时默认为 NULLS FIRST。

select first_name, last_name, commission_pct
from employees
where first_name = 'Peter'
order by commission_pct nulls first;

四、限定结果数量

查询语句的结果可能包含成百上千行数据，但是前端显示时也许只需要其中的小部分，例如TOP-N 排行榜；或者为了便于查看，每次只显示一定数量的结果，例如分页功能。为了处理这类应用，SQL 提供了标准的FETCH 和 OFFSET 子句。另外，PostgreSQL 还实现了扩展的 LIMIT语法。

4.1、Top-N查询

这类查询通常是为了找出排名中的前N 个记录，例如以下语句查询薪水最高的前10 名员工，使用 FETCH 语法：

select first_name, last_name, salary
from employees
order by salary desc
fetch first 10 rows only;-- 其中，FIRST 也可以写成 NEXT，ROWS 也可以写成 ROW。结果返回了排序之后的前 10条记录。

使用LIMIT 语法也可以实现相同的功能：

select first_name, last_name, salary
from employees
order by salary desc
limit 10;

4.2、分页查询

许多应用都支持分页显示的功能，即每页显示一定数量的记录（例如 10 行、20 行等），同时提供类似上一页和下一页的导航。使用SQL 实现这种功能需要引入另一个子句：OFFSET。

假设我们的应用提供了分页显示，每页显示 10 条记录。现在用户点击了下一页，需要显示第 11 到第 20 条记录。使用标准SQL 语法实现如下：

select first_name, last_name, salary
from employees
order by salary desc
offset 10 rows
fetch first 10 rows only;-- OFFSET 表示先忽略掉多少行数据，然后再返回后面的结果。ROWS 也可以写成 ROW。对于应用程序而言，只需要传入不同的OFFSET 偏移量和FETCH 数量，就可以在结果中任意导航。

使用LIMIT 加上 OFFSET 同样可以实现分页效果：

select first_name, last_name, salary
from employees
order by salary desc
limit 10 offset 10;

4.3、注意事项

先看一下完整的FETCH 和LIMIT 语法：

SELECT column1, column2, ...
FROM table
[WHERE conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[OFFSET m {ROW | ROWS}]
[FETCH { FIRST | NEXT } [ num_rows] { ROW | ROWS } ONLY];SELECT column1, column2, ...
FROM table
[WHERE conditions]
[ORDER BY column1 ASC, column2 DESC, ...]
[LIMIT { num_rows| ALL } ]
[OFFSET m {ROW | ROWS}];

在使用以上功能时需要注意以下问题：

• FETCH 是标准 SQL 语法，LIMIT 是PostgreSQL 扩展语法。
• 如果没有指定 ORDER BY，限定数量之前并没有进行排序，是一个随意的结果。
• OFFSET 偏移量必须为 0 或者正整数。默认为0，NULL 等价于 0。
• FETCH 限定的数量必须为 0 或者正整数。默认为 1，NULL 等价于不限定数量。
• LIMIT 限定的数量必须为0 或者正整数， 没有默认值。 ALL 或者NULL 表示不限定数量。
• 随着 OFFSET 的增加，查询的性能会越来越差。因为服务器需要计算更多的偏移量，即使这些数据不需要被返回前端。

五、分组汇总

5.1、聚合函数

PostgreSQL支持以下常见的聚合函数：

• AVG - 计算一组值的平均值。
• COUNT - 统计一组值的数量。
• MAX - 计算一组值的最大值。
• MIN - 计算一组值的最小值。
• SUM - 计算一组值的和值。
• STRING_AGG - 连接一组字符串。

以下示例分别返回了 IT 部门所有员工的平均薪水、员工总数、最高薪水、最低薪水、以及薪水总计：

select avg(salary),count(*),max(salary),min(salary),sum(salary)
from employees
where department_id = 60;

关于聚合函数，需要注意两点：

• 函数参数前添加 DISTINCT 关键字，可以在计算时排除重复值。
• 忽略参数中的 NULL 值。

select count(*),count(distinct salary),count(commission_pct)
from employees
where department_id = 60;

其中，COUNT(*)返回了该部门员工的总数（5）,COUNT(DISTINCT salary)返回了薪水不相同的员工数量（4），COUNT(commission_pct)返回了佣金百分比不为空值的数量（0），该部门员工都没有佣金提成。

另外，PostgreSQL 为聚合函数提供了一个FILTER 扩展选项，可以用于汇总满足特定条件的数据。例如：

select count(*) filter (where salary >= 10000) high_sal,count(*) filter (where salary < 10000) low_sal
from employees;

其中， FILTER 选项可以指定一个 WHERE 条件，只有满足条件的数据才会进行汇总。因此，示例中的第一个 COUNT 函数返回了月薪大于等于 10000 的员工数量， 第二个COUNT 函数返回了月薪小于 10000 的员工数量。

以下示例使用 STRING_AGG 函数将 IT 部门员工的名字使用分号进行分隔，按照薪水从高到低排序后连接成一个字符串：

select string_agg(first_name, ';' order by salary desc)
from employees
where department_id = 60;

5.2、分组聚合

如果我们想要知道每个部门内所有员工的平均薪水、员工总数、最高薪水、最低薪水、以及薪水总计，可以使用以下查询语句：

select department_id, avg(salary),count(*),max(salary),min(salary),sum(salary)
from employees
group by department_id
order by department_id;

查询执行时，首先根据 GROUP BY 子句中的列（department_id）进行分组，然后使用聚合函数汇总组内的数据。最后一条数据是针对部门编号字段为空的数据进行的分组汇总，GROUP BY 将所有的 NULL 分为一组。

GROUP BY 并不一定需要与聚合函数一起使用，例如：

select department_id
from employees
group by department_id
order by department_id;

查询的结果就是不同的部门编号分组，这种查询的结果与 DISTINCT 效果相同：

select distinct department_id
from employees
order by department_id;

GROUP BY 不仅可以按照一个字段进行分组，也可以使用多个字段将数据分成更多的组。例如，以下查询将员工按照不同的部门和职位组合进行分组，然后进行汇总：

select department_id, job_id, avg(salary),count(*),max(salary),min(salary),sum(salary)
from employees
group by department_id, job_id
order by department_id, job_id;

使用了 GROUP BY 子句进行分组操作之后需要注意一点，就是SELECT 列表中只能出现分组字段或者聚合函数，不能再出现表中的其他字段。下面是一个错误的示例：

select department_id, job_id, avg(salary),count(*),max(salary),min(salary),sum(salary)
from employees
group by department_id;-- ERROR:  column "employees.job_id" must appear in the GROUP BY clause or be used in an aggregate function
-- LINE 1: select department_id, job_id, 

5.3、分组过滤

当我们需要针对分组汇总后的数据再次进行过滤时，例如找出平均薪水值大于 10000 的部门：

select department_id,avg(salary),count(*),max(salary),min(salary),sum(salary)
from employees
group by department_id
having avg(salary) > 10000
order by department_id;

HAVING 出现在 GROUP BY 之后，也在它之后执行，因此能够使用聚合函数进行过滤。我们可以同时使用 WHERE 子句进行数据行的过滤，使用 HAVING 进行分组结果的过滤。

以下示例用于查找哪些部门中薪水大于 10000 的员工的数量多于2 个：

select department_id,count(*) as headcount
from employees
where salary > 10000
group by department_id
having count(*) > 2;

查询时，首先通过 WHERE 子句找出薪水大于 10000 的所有员工；然后，按照部门编号进行分组，计算每个组内的员工数量；最后使用 HAVING 子句过滤员工数量多于2 个人的部门。

5.4、高级选项

PostgreSQL 除了支持基本的 GROUP BY 分组操作之外，还支持 3 种高级的分组选项：GROUPING SETS、ROLLUP 以及 CUBE。

5.4.1、GROUPING SETS选项

GROUPING SETS 是GROUP BY 的扩展选项，用于指定自定义的分组集。举例来说，以下是一个销售数据表：

CREATE TABLE sales (item VARCHAR(10),year VARCHAR(4),quantity INT
);INSERT INTO sales VALUES('apple', '2018', 800);
INSERT INTO sales VALUES('apple', '2018', 1000);
INSERT INTO sales VALUES('banana', '2018', 500);
INSERT INTO sales VALUES('banana', '2018', 600);
INSERT INTO sales VALUES('apple', '2019', 1200);
INSERT INTO sales VALUES('banana', '2019', 1800);

按照产品（item）和年度（year）进行分组汇总时，所有可能的4 种分组集包括：

• 按照产品和年度的组合进行分组；
• 按照产品进行分组；
• 按照年度进行分组；
• 所有数据分为一组。

-- 按照产品和年度的组合进行分组
select item, year, sum(quantity)
from sales
group by item, year;

-- 按照产品进行分组
SELECT item, NULL AS year, SUM(quantity)
FROM sales
GROUP BY item;

-- 按照年度进行分组
SELECT NULL AS item, year, SUM(quantity)
FROM sales
GROUP BY year;

-- 所有数据分为一组
SELECT NULL AS item, NULL AS year, SUM(quantity)
FROM sales;

GROUPING SETS 是GROUP BY 的扩展选项，能够为这种查询需求提供更加简单有效的解决方法。我们使用分组集改写上面的示例：

SELECT item, year, SUM(quantity)
FROM sales
GROUP BY GROUPING SETS ((item, year),(item),(year),()
);

GROUPING SETS 选项用于定义分组集，每个分组集都需要包含在单独的括号中，空白的括号（()）表示将所有数据当作一个组处理。查询的结果等于前文 4 个查询的合并结果，但是语句更少，可读性更强；而且 PostgreSQL 执行时只需要扫描一次销售表，性能更加优化。

另外，默认的 GROUP BY 使用由所有分组字段构成的一个分组集，本示例中为 ((item, year))。

5.4.2、CUBE选项

随着分组字段的增加，即使通过 GROUPING SETS 列出所有可能的分组方式也会显得比较麻烦。设想一下使用 4 个字段进行分组统计的场景，所有可能的分组集共计有 16 个。这种情况下编写查询语句仍然很复杂，为此PostgreSQL 提供了简写形式的 GROUPING SETS：CUBE 和ROLLUP。

CUBE 表示所有可能的分组集，例如：

CUBE(c1, c2, c3)-- 等价于GROUPING SETS((c1, c2, c3),(c1, c2),(c1, c3),(c2, c3),(c1),(c2),(c3),()
)

因此，我们可以进一步将上面的示例改写如下：

select item, year, sum(quantity)
from sales
group by cube (item, year);

5.4.3、ROLLUP选项

GROUPING SETS 第二种简写形式就是 ROLLUP，用于生成按照层级进行汇总的结果，类似于财务报表中的小计、合计和总计。例如：

ROLLUP(c1, c2, c3)--  等价于GROUPING SETS((c1, c2, c3),(c1, c2),(c1),()
)

以下查询返回按照产品和年度组合进行统计的销量小计，加上按照产品进行统计的销量合计，再加上所有销量的总计：

select item, year, sum(quantity)
from sales
group by rollup(item, year);

查看结果时，需要根据每个字段上的空值进行判断。比如第一行的产品和年度都为空，因此它是所有销量的总计。为了便于查看，可以将空值进行转换显示：

select coalesce(item, '所有产品') as "产品",coalesce(year, '所有年度') as "年度",sum(quantity) as "销量"
from sales
group by rollup(item, year);

可以根据需要返回按照某些组合进行统计的结果，以下查询返回按照产品和年度组合进行统计的销量小计，加上按照产品进行统计的销量合计：

select coalesce(item, '所有产品') as "产品",coalesce(year, '所有年度') as "年度",sum(quantity) as "销量"
from sales
group by item, rollup(year);

对于CUBE 和ROLLUP 而言，每个元素可以是单独的字段或表达式，也可以是使用括号包含的列表。如果是括号中的列表，产生分组集时它们必须作为一个整体。例如：

CUBE((c1,c2), (c3,c4))
-- 等价于
GROUPING SETS((c1,c2, c3,c4),(c1,c2),(c3,c4),()
)
-- 因为c1 和 c2 是一个整体，c3 和c4 是一个整体。

同样：

ROLLUP ( c1, (c2, c3), c4 )
-- 等价于
GROUPING SETS (( c1, c2, c3, c4 ),( c1, c2, c3 ),( c1 ),( )
)

5.4.4、GROUPING函数

虽然有时候可以通过空值来判断数据是不是某个字段上的汇总，比如说按照年度进行统计的结果在字段 year 上的值为空。但是情况并非总是如此，考虑以下示例：

-- 未知产品在 2018 年的销量为 5000
INSERT INTO sales VALUES(NULL, '2018', 5000);SELECT coalesce(item, '所有产品') AS "产品",coalesce(year, '所有年度') AS "年度",SUM(quantity) AS "销量"
FROM sales
GROUP BY ROLLUP (item,year);

其中第 5 行和第 7 行的显示存在问题，它们分别应该是未知产品在 2018 年的销量小计和所有年度的销量合计。问题的关键在于无法区分是分组产生的 NULL 还是源数据中的 NULL。为了解决这个问题，PostgreSQL 提供了一个分组函数：GROUPING。

-- 以下查询显示了 GROUPING 函数的结果：
SELECT item AS "产品",year AS "年度",SUM(quantity) AS "销量",GROUPING(item),GROUPING(year),GROUPING(item, year)
FROM sales
GROUP BY ROLLUP (item,year);

GROUPING 函数如果只有一个参数，返回整数 0 或者 1。如果某个统计结果使用的分组集包含了函数中的参数字段，该函数返回 0，否则返回 1。比如说，第 1 行数据是所有产品所有年度的统计（分组集为空），所以 GROUPING(item)和 GROUPING(year)结果都是 1；第 7 行数据是未知产品所有年度的统计（分组集为(item, )），所以GROUPING(item)结果为0， GROUPING(year)结果为 1。

GROUPING 函数如果包含多个参数，针对每个参数返回整数 0 或者 1，然后将它们按照二进制数值连接到一起。比如说，第1 行数据中的GROUPING(item, year)结果等于GROUPING(item)和 GROUPING(year)结果的二进制数值连接，也就是 3（二进制的 11）。

通过使用 GROUPING 函数，我们可以正确显示分组中的 NULL 值和源数据中的 NULL 值：

SELECT CASE GROUPING(item) WHEN 1 THEN '所有产品' ELSE item END AS "产品",CASE GROUPING(year) WHEN 1 THEN '所有年度' ELSE year END AS "年度",SUM(quantity) AS "销量"
FROM sales
GROUP BY ROLLUP (item,year);

六、多表连接

PostgreSQL 支持各种类型的 SQL 连接查询：

• 内连接（INNER JOIN）
• 左外连接（LEFT OUTER JOIN）
• 右外连接（RIGHT OUTER JOIN）
• 全外连接（FULL OUTER JOIN）
• 交叉连接（CROSS JOIN）
• 自然连接（NATURAL JOIN）
• 自连接（Self Join）

其中，左外连接、右外连接以及全外连接统称为外连接（OUTER JOIN）。

6.1、内连接

内连接用于返回两个表中匹配的数据行，使用关键字INNER JOIN 表示，也可以简写成JOIN；以下是内连接的示意图（基于两个表的 id 进行连接）：

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

其中，JOIN 表示内连接，ON 表示连接条件。另外，SELECT 列表中的字段名加上了表名限定，例如 d.department_id，这是因为两个表中都存在部门编号，必须明确指定需要显示哪个表中的字段。不过，如果某个字段只存在于一个表中，可以省略表名，例如 first_name。

-- 对于内连接而言，也可以使用FROM 和 WHERE 表示：
SELECT d.department_id,e.department_id,d.department_name,e.first_name,e.last_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

在这种语法中，多个表在 FROM 子句中使用逗号进行分割，连接条件使用 WHERE 子句表示。实际上，在 SQL 历史中定义了两种多表连接的语法：

• ANSI SQL/86 标准使用FROM 和 WHERE 关键字指定表的连接条件。
• ANSI SQL/92 标准使用JOIN 和 ON 关键字指定表的连接条件。

6.2、左/右外连接

左外连接返回左表中所有的数据行；对于右表，如果没有匹配的数据，显示为空值。左外连接使用关键字 LEFT OUTER JOIN 表示，也可以简写成 LEFT JOIN。 左外连接参考以下示意图（基于两个表的 id 进行连接）：

由于某些部门刚刚成立，可能还没有员工，因此前面的内连接查询不会显示这些部门的信息。如果想要在连接查询中返回这些部门的信息，需要使用左外连接：

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

右外连接返回右表中所有的数据行；对于左表，如果没有匹配的数据，显示为空值。右外连接使用关键字RIGHT OUTER JOIN 表示，也可以简写成RIGHT JOIN。也就是说：

table1 RIGHT JOIN table2
-- 等价于
table2 LEFT JOIN table1

因此，上面的查询也可以使用右外连接来表示：

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

6.3、全外连接

全外连接等效于左外连接加上右外连接，返回左表和右表中所有的数据行。全外连接使用关键字 FULL OUTER JOIN 表示，也可以简写成 FULL JOIN。

全外连接的示意图如下（基于两个表的 id 进行连接）：

以下查询将员工表和部门表进行全外连接，连接字段为部门编号：

SELECT d.department_id,e.department_id,d.department_name,e.first_name,e.last_name
FROM departments d FULL JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IN (176, 177, 178) 
OR d.department_id IN (110, 120, 130);

查询结果不但包含了没有员工的部门，同时还存在一个没有部门的员工。

对于外连接，需要注意 WHERE 条件和 ON 条件之间的差异：ON 条件是针对连接之前的数据进行过滤，WHERE 是针对连接之后的数据进行过滤，同一个条件放在不同的子句中可能会导致不同的结果。

以下示例将部门表与员工表进行左外连接查询，并且在 ON 子句中指定了多个条件：

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

ON 子句指定了一个不存在的员工（e.employee_id = 0），因此员工表不会返回任何数据。但是由于查询指定的是左外连接，仍然会返回部门信息。

对于相同的查询条件，使用 WHERE 子句的示例如下：

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

查询结果没有返回任何数据，因为左连接产生的结果经过 WHERE 条件（e.employee_id = 0）过滤之后没有任何满足的数据。

6.4、交叉连接

当连接查询没有指定任何连接条件时，就称为交叉连接。交叉连接使用关键字CROSS JOIN表示，也称为笛卡尔积（Cartesian product）。

两个表的笛卡尔积相当于一个表的所有行和另一个表的所有行两两组合，结果数量为两个表的行数相乘。假如第一个表有 100 行，第二个表有 200 行，它们的交叉连接将会产生100 × 200 = 20000 行结果。交叉连接的示意图如下（基于两个表的id 进行连接）：

以下查询通过笛卡儿积返回九九乘法表：

SELECT v || '*' || h || '=' || v*h
FROM generate_series(1,9) v
CROSS JOIN generate_series(1,9) h;

上面的交叉连接也可以使用以下等效写法：

SELECT v || '*' || h || '=' || v*h
FROM generate_series(1,9) v, generate_series(1,9) h;SELECT v || '*' || h || '=' || v*h
FROM generate_series(1,9) v
JOIN generate_series(1,9) h ON TRUE;

6.5、自然连接

对于连接查询，如果满足以下条件，可以使用 USING 替代 ON 子句，简化连接条件的输入：

• 连接条件是等值连接，即 t1.col1 = t2.col1；
• 两个表中的列必须同名同类型，即t1.col1 和t2.col1 的类型相同。

由于 employees 表和 departments 表中的 department_id 字段名称和类型都相同，可以使用USING 简写前文中的连接查询：

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

USING 条件中的字段不需要指定表名，它是公共的字段。

如果USING 子句中包含了两个表中所有的这种同名同类型字段，可以使用更加简单的自然连接（NATURAL JOIN）表示。例如，employees 表和departments 表拥有2 个同名同类型字段：department_id 和manager_id，如果基于这 2 个字段进行等值连接，可以使用自然连接：

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

6.6、自连接

连接（Self Join）是指连接操作符的两边都是同一个表，即把一个表和它自己进行连接。自连接本质上并没有什么特殊之处，主要用于处理那些对自己进行了外键引用的表。

例如，员工表中的经理字段（manager_id）是一个外键列，指向了员工表自身的员工编号字段（employee_id）。如果要显示员工姓名以及他们经理的姓名，可以通过自连接实现：

SELECT e.first_name||', '||e.last_name AS employee_name,
m.first_name||', '||m.last_name AS manageer_name
FROM employees m
JOIN employees e
ON m.employee_id = e.manager_id;

如果还需要知道员工的职位信息，比如职位名称，可以在连接查询中加上 jobs 表。以下是三个表连接查询的示例：

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