PgSQL——学习笔记11：连接(JOIN)_pgsql join

作者：小小林熬夜学编程 | 2024-04-17 01:52:42

踩

pgsql join

连接(JOIN)

交叉连接（CROSS JOIN）

内连接（INNER JOIN）

左外连接（ LEFT OUTER JOIN ）

右外连接（ RIGHT OUTER JOIN）

外连接（ FULL OUTER JOIN）

连接(JOIN)

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

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

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

练习JOIN前准备：


postgres=# \c mydb;
您现在已经连接到数据库 "mydb",用户 "postgres".
mydb=# \d
                关联列表
 架构模式 |   名称   |  类型  |  拥有者
----------+----------+--------+----------
 public   | company  | 数据表 | postgres
 public   | company1 | 数据表 | postgres
 public   | company2 | 数据表 | postgres
(3 行记录)
 
mydb=# select * from company;
 id | name  | age |                      address                       | salary | join_date
----+-------+-----+----------------------------------------------------+--------+-----------
  1 | Paul  |  32 | California                                         |  20000 |
  2 | Allen |  25 | Texas                                              |  15000 |
  3 | Teddy |  23 | Norway                                             |  20000 |
  4 | Mark  |  25 | Rich-Mond                                          |  65000 |
  5 | David |  27 | South-Hall                                         |  85000 |
  6 | Kim   |  22 | South-Hall                                         |  45000 |
  7 | James |  24 | Houston                                            |   1000 |
  8 | Paul  |  24 | Houston                                            |  20000 |
  9 | James |  44 | Norway                                             |   5000 |
 10 | James |  45 | Texas                                              |   5000 |
(10 行记录)
 
/*创建表DEPARTMENT。*/
mydb=# CREATE TABLE DEPARTMENT(
mydb(#    ID INT PRIMARY KEY      NOT NULL,
mydb(#    DEPT           CHAR(50) NOT NULL,
mydb(#    EMP_ID         INT      NOT NULL
mydb(# );
CREATE TABLE
mydb=# select * from department;
 id | dept | emp_id
----+------+--------
(0 行记录)
 
/*往表里添加几条数据：*/
mydb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 ),(2, 'Engineering', 2 ),(3, 'Finance', 7 );
INSERT 0 3
mydb=# select * from department;
 id |                        dept                        | emp_id
----+----------------------------------------------------+--------
  1 | IT Billing                                         |      1
  2 | Engineering                                        |      2
  3 | Finance                                            |      7
(3 行记录)

交叉连接（CROSS JOIN）

交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。

如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。

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


/*下面是 CROSS JOIN 的基础语法：*/
SELECT ... 
FROM table1
CROSS JOIN table2;

实例：


/*基于上面的表，我们可以写一个交叉连接（CROSS JOIN），如下所示：*/
mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
 emp_id | name  |                        dept
--------+-------+----------------------------------------------------
      1 | Paul  | IT Billing
      2 | Paul  | Engineering
      7 | Paul  | Finance
      1 | Allen | IT Billing
      2 | Allen | Engineering
      7 | Allen | Finance
      1 | Teddy | IT Billing
      2 | Teddy | Engineering
      7 | Teddy | Finance
      1 | Mark  | IT Billing
      2 | Mark  | Engineering
      7 | Mark  | Finance
      1 | David | IT Billing
      2 | David | Engineering
      7 | David | Finance
      1 | Kim   | IT Billing
      2 | Kim   | Engineering
      7 | Kim   | Finance
      1 | James | IT Billing
      2 | James | Engineering
      7 | James | Finance
      1 | Paul  | IT Billing
      2 | Paul  | Engineering
      7 | Paul  | Finance
      1 | James | IT Billing
      2 | James | Engineering
      7 | James | Finance
      1 | James | IT Billing
      2 | James | Engineering
      7 | James | Finance
(30 行记录)

内连接（INNER JOIN）

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

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

内连接是最常见的连接类型，是默认的连接类型。

INNER 关键字是可选的。


/*下面是内连接（INNER JOIN）的语法：*/
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

实例：


/*基于上面的表，我们可以写一个内连接，如下所示：*/
mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |                        dept
--------+-------+----------------------------------------------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
(3 行记录)

左外连接（ LEFT OUTER JOIN ）

外部连接是内部连接的扩展。

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

对于左外连接，首先执行一个内连接。

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


/*下面是左外连接（ LEFT OUTER JOIN ）的基础语法：*/
SELECT ... 
FROM table1 LEFT 
OUTER JOIN table2 
ON conditional_expression ...

实例：


/*基于上面两张表，我们可以写个左外连接，如下：*/
mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |                        dept
--------+-------+----------------------------------------------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
        | James |
        | David |
        | Paul  |
        | Kim   |
        | Mark  |
        | Teddy |
        | James |
(10 行记录)

右外连接（ RIGHT OUTER JOIN）

首先，执行内部连接。

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


/*下面是右外连接（ RIGHT OUT JOIN）的基本语法：*/
SELECT ... 
FROM table1 
RIGHT OUTER JOIN table2 
ON conditional_expression ...

实例：


/*基于上面两张表，我们建立一个右外连接：*/
mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |                        dept
--------+-------+----------------------------------------------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
(3 行记录)
 
mydb=# INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (4,'lili',15);
INSERT 0 1
 
mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |                        dept
--------+-------+----------------------------------------------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
     15 |       | lili
(4 行记录)

外连接（ FULL OUTER JOIN）

首先，执行内部连接。

然后，对于表 T1 中不满足表 T2 中任何行连接条件的每一行，如果 T2 的列中有 null 值也会添加一个到结果中。

此外，对于 T2 中不满足与 T1 中的任何行连接条件的每一行，将会添加 T1 列中包含 null 值的到结果中。


/*下面是外连接的基本语法：*/
SELECT ... 
FROM table1 
FULL OUTER JOIN table2 
ON conditional_expression ...

实例：


/*基于上面两张表，可以建立一个外连接：*/
mydb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
 emp_id | name  |                        dept
--------+-------+----------------------------------------------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
     15 |       | lili
        | James |
        | David |
        | Paul  |
        | Kim   |
        | Mark  |
        | Teddy |
        | James |
(11 行记录)

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/437563

PgSQL——学习笔记11： 连接(JOIN)_pgsql join

连接(JOIN)

交叉连接（CROSS JOIN）

内连接（INNER JOIN）

左外连接（ LEFT OUTER JOIN ）

右外连接（ RIGHT OUTER JOIN）

外连接（ FULL OUTER JOIN）

PgSQL——学习笔记11：连接(JOIN)_pgsql join