9.PostgreSQL的Join，Union，Null

小鱼儿 2022-10-08 12:24 94阅读 0赞

# 一、PostgreSQL 连接(JOIN) #

PostgreSQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

在 PostgreSQL 中，JOIN 有五种连接类型：

*  CROSS JOIN ：交叉连接
 *  INNER JOIN：内连接
 *  LEFT OUTER JOIN：左外连接
 *  RIGHT OUTER JOIN：右外连接
 *  FULL OUTER JOIN：全外连接

接下来让我们创建两张表 **COMPANY** 和 **DEPARTMENT**。

## 1.1 实例 ##

创建 COMPANY 表（[下载 COMPANY SQL 文件][COMPANY SQL] ），数据内容如下：

runoobdb# select * from COMPANY;
     id | name  | age | address   | salary
    ----+-------+-----+-----------+--------
      1 | Paul  |  32 | California|  20000
      2 | Allen |  25 | Texas     |  15000
      3 | Teddy |  23 | Norway    |  20000
      4 | Mark  |  25 | Rich-Mond |  65000
      5 | David |  27 | Texas     |  85000
      6 | Kim   |  22 | South-Hall|  45000
      7 | James |  24 | Houston   |  10000
    (7 rows)

我们往表里添加几条数据：

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
    INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
    INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

此时，COMPANY 表的记录如下：

id | name  | age | address      | salary
     ----+-------+-----+--------------+--------
       1 | Paul  |  32 | California   |  20000
       2 | Allen |  25 | Texas        |  15000
       3 | Teddy |  23 | Norway       |  20000
       4 | Mark  |  25 | Rich-Mond    |  65000
       5 | David |  27 | Texas        |  85000
       6 | Kim   |  22 | South-Hall   |  45000
       7 | James |  24 | Houston      |  10000
       8 | Paul  |  24 | Houston      |  20000
       9 | James |  44 | Norway       |   5000
      10 | James |  45 | Texas        |   5000
    (10 rows)

创建一张 DEPARTMENT 表，添加三个字段：

CREATE TABLE DEPARTMENT(
       ID INT PRIMARY KEY      NOT NULL,
       DEPT           CHAR(50) NOT NULL,
       EMP_ID         INT      NOT NULL
    );

向 DEPARTMENT 表插入三条记录：

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
    
    INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
    
    INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

此时，DEPARTMENT 表的记录如下：

id | dept        | emp_id
    ----+-------------+--------
      1 | IT Billing  |  1
      2 | Engineering |  2
      3 | Finance     |  7

--------------------

## 1.2 交叉连接 ##

交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x\*y 行。

由于交叉连接（CROSS JOIN）有可能产生非常大的表，使用时必须谨慎，只在适当的时候使用它们。

下面是 CROSS JOIN 的基础语法：

SELECT ... FROM table1 CROSS JOIN table2 ...

基于上面的表，我们可以写一个交叉连接（CROSS JOIN），如下所示：

runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

得到结果如下：

--------------------

## 1.3 内连接 ##

内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。

当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。

INNER 关键字是可选的。

下面是内连接（INNER JOIN）的语法：

SELECT table1.column1, table2.column2...
    FROM table1
    INNER JOIN table2
    ON table1.common_filed = table2.common_field;

基于上面的表，我们可以写一个内连接，如下所示：

--------------------

## 1.4 左外连接 ##

外部连接是内部连接的扩展。SQL 标准定义了三种类型的外部连接: LEFT、RIGHT 和 FULL, PostgreSQL 支持所有这些。

对于左外连接，首先执行一个内连接。然后，对于表 T1 中不满足表 T2 中连接条件的每一行，其中 T2 的列中有 null 值也会添加一个连接行。因此，连接的表在 T1 中每一行至少有一行。

下面是左外连接（ LEFT OUTER JOIN ）的基础语法：

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

基于上面两张表，我们可以写个左外连接，如下：

--------------------

## 1.5 右外连接 ##

首先，执行内部连接。然后，对于表T2中不满足表T1中连接条件的每一行，其中T1列中的值为空也会添加一个连接行。这与左联接相反;对于T2中的每一行，结果表总是有一行。

下面是右外连接（ RIGHT OUT JOIN）的基本语法：

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

基于上面两张表，我们建立一个右外连接：

--------------------

## 1.6 外连接 ##

首先，执行内部连接。然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。

下面是外连接的基本语法：

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

基于上面两张表，可以建立一个外连接：

# 二、UNION 操作符 #

PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

## 2.1 语法 ##

UNIONS 基础语法如下：

SELECT column1 [, column2 ]
    FROM table1 [, table2 ]
    [WHERE condition]
    
    UNION
    
    SELECT column1 [, column2 ]
    FROM table1 [, table2 ]
    [WHERE condition]

这里的条件语句可以根据您的需要设置任何表达式。

### 2.1.1 实例 ###

创建 COMPANY 表（[下载 COMPANY SQL 文件][COMPANY SQL] ），数据内容如下：

创建 DEPARTMENT 表（[下载 DEPARTMENT SQL 文件][DEPARTMENT SQL] ），数据内容如下：

现在，我们在 SELECT 语句中使用 UNION 子句将两张表连接起来，如下所示：

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
       ON COMPANY.ID = DEPARTMENT.EMP_ID
       UNION
    SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

得到结果如下：

--------------------

## 2.2 UNION ALL 子句 ##

UNION ALL 操作符可以连接两个有重复行的 SELECT 语句，默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

### 2.2.1 语法 ###

UINON ALL 子句基础语法如下：

SELECT column1 [, column2 ]
    FROM table1 [, table2 ]
    [WHERE condition]
    
    UNION ALL
    
    SELECT column1 [, column2 ]
    FROM table1 [, table2 ]
    [WHERE condition]

这里的条件语句可以根据您的需要设置任何表达式。

### 2.2.2 实例 ###

现在，让我们把上面提到的两张表用 SELECT 语句结合 UNION ALL 子句连接起来：

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
       ON COMPANY.ID = DEPARTMENT.EMP_ID
       UNION ALL
    SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

得到结果如下：

# 三、NULL 值 #

NULL 值代表遗漏的未知数据。

默认地，表的列可以存放 NULL 值。

本章讲解 IS NULL 和 IS NOT NULL 操作符。

## 3.1 语法 ##

当创建表时，NULL 的基本语法如下：

CREATE TABLE COMPANY(
       ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL
    );

这里，NOT NULL 表示强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

具有 NULL 值的字段表示在创建记录时可以留空。

在查询数据时，NULL 值可能会导致一些问题，因为一个未知的值去与其他任何值比较，结果永远是未知的。

另外无法比较 NULL 和 0，因为它们是不等价的。

### 3.1.1 实例 ###

创建 COMPANY 表（[下载 COMPANY SQL 文件][COMPANY SQL] ），数据内容如下：

接下来我们用 UPDATE 语句把几个可设置为空的字段设置为 NULL ：

runoobdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

现在 COMPANY 表长这样：：

runoobdb=# select * from company;
     id | name  | age |         address     | salary 
    ----+-------+-----+---------------------+--------
      1 | Paul  |  32 | California          |  20000
      2 | Allen |  25 | Texas               |  15000
      3 | Teddy |  23 | Norway              |  20000
      4 | Mark  |  25 | Rich-Mond           |  65000
      5 | David |  27 | Texas               |  85000
      6 | Kim   |  22 |                     |       
      7 | James |  24 |                     |       
    (7 rows)

#### IS NOT NULL ####

现在，我们用 IS NOT NULL 操作符把所有 SALARY（薪资） 值不为空的记录列出来：

runoobdb=# SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;

得到结果如下：

id | name  | age | address    | salary
    ----+-------+-----+------------+--------
      1 | Paul  |  32 | California |  20000
      2 | Allen |  25 | Texas      |  15000
      3 | Teddy |  23 | Norway     |  20000
      4 | Mark  |  25 | Rich-Mond  |  65000
      5 | David |  27 | Texas      |  85000
    (5 rows)

#### IS NULL ####

IS NULL 用来查找为 NULL 值的字段。

下面是 IS NULL 操作符的用法，列出 SALARY（薪资） 值为空的记录：

runoobdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;

得到结果如下：

id | name  | age | address | salary
    ----+-------+-----+---------+--------
      6 | Kim   |  22 |         |
      7 | James |  24 |         |
    (2 rows)

[COMPANY SQL]: https://static.runoob.com/download/company.sql
[DEPARTMENT SQL]: https://static.runoob.com/download/department.sql