连接查询

📄字数 2.4K

👁️阅读量加载中...

当一个查询涉及两张及以上的表时，则需要用到连接查询，连接查询会将两张表中的内容以指定的规则组合成一个新的结果集。连接查询主要有三种方式，内连接、外连接、交叉连接。

一、内连接

1.1 功能描述

内连接根据连接条件分为等值连接、不等值连接、自然连接。使用内连接时，返回满足条件的所有行数据，如果两个表的相关字段满足连接条件且查询未指定应返回的表的字段，则从这两个表中获取满足条件的数据并组合成新的记录。自连接是一种特殊的内连接，是对表自身的连接。

等值连接：连接条件为等值比较，即使用=运算符，或者使用JOIN...USING(col_name)格式，此种格式要求两张表中存在同名列(col_name)，且连接查询结果中仅会出现一列col_name。
不等值连接：等值连接的相反情况，使用>、<、<>、!=、>=、<=比较运算符。
自然连接：特殊的等值连接，不可指定连接条件，数据库根据关系表中的相同字段进行条件连接，使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列，若无相同字段则返回连接表笛卡尔积。

1.2 示例

sql

SQL> CREATE TABLE tab_pre(id INT,name VARCHAR(30));

Execute successful.
Use time:29 ms.

SQL> INSERT INTO tab_pre VALUES(1,'abc')(2,null)(3,'def');

Total 3 records effected.
Use time:1 ms.

SQL> CREATE TABLE tab_pre1(id INT,col1 VARCHAR(30));

Execute successful.
Use time:29 ms.

SQL> INSERT INTO tab_pre1 VALUES(1,'abc')(2,null)(3,'def')(5,'two');

Total 4 records effected.
Use time:0 ms.

-- 等值连接
SQL> SELECT * FROM tab_pre t1 JOIN tab_pre1 t2 ON t1.id=t2.id;

+----+--------+----+--------+
| ID |  NAME  | ID |  COL1  |
+----+--------+----+--------+
| 1  | abc    | 1  | abc    |
| 2  | <NULL> | 2  | <NULL> |
| 3  | def    | 3  | def    |
+----+--------+----+--------+

(3 rows)
Use time:0 ms.

-- 使用JOIN...USING格式的等值连接
SQL> SELECT * FROM tab_pre t1 JOIN tab_pre1 t2 USING(id);

+----+--------+--------+
| ID |  NAME  |  COL1  |
+----+--------+--------+
| 1  | abc    | abc    |
| 2  | <NULL> | <NULL> |
| 3  | def    | def    |
+----+--------+--------+

(3 rows)
Use time:0 ms.

-- 自然连接
SQL> SELECT * FROM tab_pre t1 NATURAL JOIN tab_pre1 t2;

+----+--------+--------+
| ID |  NAME  |  COL1  |
+----+--------+--------+
| 1  | abc    | abc    |
| 2  | <NULL> | <NULL> |
| 3  | def    | def    |
+----+--------+--------+

(3 rows)
Use time:0 ms.

-- 不等值连接
SQL> SELECT * FROM tab_pre t1 INNER JOIN tab_pre1 t2 ON t1.id>t2.id;

+----+--------+----+--------+
| ID |  NAME  | ID |  COL1  |
+----+--------+----+--------+
| 2  | <NULL> | 1  | abc    |
| 3  | def    | 1  | abc    |
| 3  | def    | 2  | <NULL> |
+----+--------+----+--------+

(3 rows)
Use time:2 ms.

二、外连接

2.1 功能描述

外连接根据需要返回的行分为左外连接、右外连接、全外连接。外连接返回两张表中匹配的行和不匹配但需要保留的行，其中不匹配的部分会用NULL填充。

左外连接：返回左表的所有行，即使右表中没有匹配项。若没有匹配，右表字段将以NULL填充。
右外连接：返回右表的所有行，即使左表中没有匹配项。若没有匹配，左表字段将以NULL填充。
全外连接：返回左表和右表中所有的数据，无论是否匹配，没有匹配的一方以NULL填充。
左外连接以左表为基准，右外连接以右表为基准，全外连接则以左右连表为基准。

2.2 示例

sql

SQL> CREATE TABLE tab_out(id INT,name VARCHAR(30));

Execute successful.
Use time:31 ms.

SQL> INSERT INTO tab_out VALUES(1,'abc')(2,null)(3,'def')(4,'aaa');

Total 4 records effected.
Use time:0 ms.

SQL> CREATE TABLE tab_out1(id INT,col1 VARCHAR(30));

Execute successful.
Use time:28 ms.

SQL> INSERT INTO tab_out1 VALUES(1,'abc')(2,null)(3,'def')(5,'two');

Total 4 records effected.
Use time:0 ms.

-- 左外连接
SQL> SELECT * FROM tab_out t1 LEFT JOIN tab_out1 t2 ON t1.id=t2.id;

+----+--------+--------+--------+
| ID |  NAME  |   ID   |  COL1  |
+----+--------+--------+--------+
| 1  | abc    | 1      | abc    |
| 2  | <NULL> | 2      | <NULL> |
| 3  | def    | 3      | def    |
| 4  | aaa    | <NULL> | <NULL> |
+----+--------+--------+--------+

(4 rows)
Use time:0 ms.

-- 右外连接
SQL> SELECT * FROM tab_out t1 RIGHT JOIN tab_out1 t2 ON t1.id=t2.id;

+--------+--------+----+--------+
|   ID   |  NAME  | ID |  COL1  |
+--------+--------+----+--------+
| 1      | abc    | 1  | abc    |
| 2      | <NULL> | 2  | <NULL> |
| 3      | def    | 3  | def    |
| <NULL> | <NULL> | 5  | two    |
+--------+--------+----+--------+

(4 rows)
Use time:0 ms.

-- 全外连接
SQL> SELECT * FROM tab_out t1 FULL JOIN tab_out1 t2 ON t1.id=t2.id;

+--------+--------+--------+--------+
|   ID   |  NAME  |   ID   |  COL1  |
+--------+--------+--------+--------+
| 1      | abc    | 1      | abc    |
| 2      | <NULL> | 2      | <NULL> |
| 3      | def    | 3      | def    |
| 4      | aaa    | <NULL> | <NULL> |
| <NULL> | <NULL> | 5      | two    |
+--------+--------+--------+--------+

(5 rows)
Use time:0 ms.

三、交叉连接

3.1 功能描述

交叉连接即笛卡尔积，无连接条件，它会将两个表中的每一行彼此配对，产生一个组合后的结果集，结果行数等于两个表行数的乘积。

提示

若连接表数据量过多（如千万级以上）则应尽量避免使用交叉连接产生过多数据。

3.2 示例

tab_pre表包含4行数据，tab_pre1表包含4行数据，则交叉连接后共16行数据。

sql

SQL> CREATE TABLE tab_pre(id INT,name VARCHAR(30));

Execute successful.
Use time:29 ms.

SQL> INSERT INTO tab_pre VALUES(1,'abc')(2,null)(3,'def')(4,'aaa');

Total 3 records effected.
Use time:1 ms.

SQL> CREATE TABLE tab_pre1(id INT,col1 VARCHAR(30));

Execute successful.
Use time:29 ms.

SQL> INSERT INTO tab_pre1 VALUES(1,'abc')(2,null)(3,'def')(5,'two');

Total 4 records effected.
Use time:0 ms.

SQL> SELECT * FROM tab_out t1 CROSS JOIN tab_out1 t2;

+----+--------+----+--------+
| ID |  NAME  | ID |  COL1  |
+----+--------+----+--------+
| 1  | abc    | 1  | abc    |
| 1  | abc    | 2  | <NULL> |
| 1  | abc    | 3  | def    |
| 1  | abc    | 5  | two    |
| 2  | <NULL> | 1  | abc    |
| 2  | <NULL> | 2  | <NULL> |
| 2  | <NULL> | 3  | def    |
| 2  | <NULL> | 5  | two    |
| 3  | def    | 1  | abc    |
| 3  | def    | 2  | <NULL> |
| 3  | def    | 3  | def    |
| 3  | def    | 5  | two    |
| 4  | aaa    | 1  | abc    |
| 4  | aaa    | 2  | <NULL> |
| 4  | aaa    | 3  | def    |
| 4  | aaa    | 5  | two    |
+----+--------+----+--------+

(16 rows)
Use time:0 ms.

连接查询 ​

一、内连接 ​

1.1 功能描述 ​

1.2 示例 ​

二、外连接 ​

2.1 功能描述 ​

2.2 示例 ​

三、交叉连接 ​

3.1 功能描述 ​

3.2 示例 ​