【openGauss笔记】SQL语法-6 连接操作

6. 连接操作

如果FROM关键字后有超过2个及以上（含2个）的表参与连接操作，则该查询可以称为连接查询，也可以叫作多表查询。
连接查询是SQL中最基本的操作，它的本质是多个表之间做笛卡儿积，借由这个思想又衍生出自然连接、θ连接等。

CREATE TABLE t1 (
	C1 INTEGER,
	C2 INTEGER
);
INSERT INTO t1 VALUES (1,2), (1,NULL), (2,2);

CREATE TABLE t2 (
	C1 INTEGER,
	C2 INTEGER
);
INSERT INTO t2 VALUES (1,2), (1,1), (NULL,2);

CREATE TABLE t3 (
	C1 INTEGER,
	C2 INTEGER
);
INSERT INTO t3 VALUES (1,1), (1,2);

6.1 WHERE连接

通常的多表连接可以通过如下形式来实现：

SELECT projection FROM t1, t2, t3 ... WHERE selection;

例2-19： 对t1、t2、t3这3个表做连接操作，通过“，”间隔，位于FROM关键字的后面，表示需要将这3个表做连接操作。

SELECT * FROM t1, t2, t3 WHERE t1.c1 = 1;

6.2 JOIN连接

如果2个基本表确定做笛卡儿积操作，则可以在SQL中显式地指定做笛卡儿积的关键字。

例2-20： 对表t1、表t2做笛卡儿积

SELECT * FROM t1 CROSS JOIN t2;

 c1 | c2 | c1 | c2 
----+----+----+----
  1 |  2 |  1 |  2
  1 |  2 |  1 |  1
  1 |  2 |    |  2
  1 |    |  1 |  2
  1 |    |  1 |  1
  1 |    |    |  2
  2 |  2 |  1 |  2
  2 |  2 |  1 |  1
  2 |  2 |    |  2
(9 rows)

6.2.1 等值连接与自然连接

1. 等值连接

   连接操作还能指定连接条件，如果连接条件中是等值条件，那么这种连接可以称为等值连接。

   

   例2-21： 对表t1、t2做等值内连接

   SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
   c1 | c2 | c1 | c2 
   ----+----+----+----
    1 |  2 |  1 |  1
    1 |  2 |  1 |  2
    1 |    |  1 |  1
    1 |    |  1 |  2
   (4 rows)
   

2. 自然连接

   在等值连接的基础上，还衍生出来一种新的连接方式：自然连接。如果进行连接的两个基本表中有相同的属性，那么自然连接会在这些相同的属性上自动做等值连接，而且会自动去掉重复的属性，而等值连接会保留两个表中重复的属性。
   

   例2-22： 对表t1、t2做自然连接

   SELECT * FROM t1 NATURAL JOIN t2;
    c1 | c2 
   ----+----
     1 |  2
   (1 row)
   

6.2.2 连接结果 - 内、外、半连接

另外从连接结果的角度来划分，连接又可以分为内连接（Inner Join）、外连接（Outer Join）、半连接（Semi Join）

1. 内连接

   例2-23： 对表t2、表t3做等值内连接
   

   SELECT * FROM t2 INNER JOIN t3 ON t2.c1 = t3.c1;
    c1 | c2 | c1 | c2 
   ----+----+----+----
     1 |  2 |  1 |  2
     1 |  2 |  1 |  1
     1 |  1 |  1 |  2
     1 |  1 |  1 |  1
   (4 rows)
   

2. 外连接

   例2-24： 对表t1、表t2做等值左外连接

   

   SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
    c1 | c2 | c1 | c2 
   ----+----+----+----
     1 |  2 |  1 |  1
     1 |  2 |  1 |  2
     1 |    |  1 |  1
     1 |    |  1 |  2
     2 |  2 |    |   
   (5 rows)
   

   例2-25： 对表t1、表t2做等值右外连接
   

   SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;
    c1 | c2 | c1 | c2 
   ----+----+----+----
     1 |    |  1 |  2
     1 |  2 |  1 |  2
     1 |    |  1 |  1
     1 |  2 |  1 |  1
       |    |    |  2
   (5 rows)
   

3. 全连接
   例2-26： 对表t1、表t2做等值全连接

   

   SELECT * FROM t1 FULL JOIN t2 ON t1.c1 = t2.c1;
    c1 | c2 | c1 | c2 
   ----+----+----+----
     1 |  2 |  1 |  1
     1 |  2 |  1 |  2
     1 |    |  1 |  1
     1 |    |  1 |  2
     2 |  2 |    |   
       |    |    |  2
   (6 rows)
   

6.2.3 Semi Join

例2-27： 对表t1、表t2做Semi Join操作，对于t1表中的t1.c1，都在t2表中探测有没有和其相等的t2.c1，如果能找到就代表符合条件，和普通的连接不同的是，只要找到第一个和其相等的t2.c1就代表满足连接条件.

SELECT * FROM t1 WHERE t1.c1 IN (SELECT t2.c1 FROM t2);
 c1 | c2 
----+----
  1 |  2
  1 |   
(2 rows)

6.2.4 Anti-Semi Join

例2-28： 对表t1、表t2做Anti-Semi Join操作，和Semi Join操作相对应，对于t1表中的t1.c1，只要在t2表中找到一个相等的t2.c1，就不满足连接条件

SELECT * FROM t1 WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2 WHERE t2.c1 IS NOT NULL);
 c1 | c2 
----+----
  2 |  2
(1 row)