SQL-关联查询【转】

淡淡的烟草味﹌ 2021-12-21 03:57 361阅读 0赞

T\_A A表 T\_B B标，id为表与表相关联的字段\`  
创建相关表结构

CREATE TABLE Table_B( id INT(2), serNum VARCHAR(10) ); CREATE TABLE Table_A( id INT(2), serNum VARCHAR(10) ); INSERT INTO table_a (id, serNum) VALUES (1,'A000101'),(2,'A000102'),(3,'A000103'),(5,'A000104'),(8,'A000105'),(4,'A000106'); INSERT INTO table_b (id, serNum) VALUES (1,'B000201'),(2,'B000202'),(3,'B000203'),(6,'B000204'),(7,'B000205'),(9,'B000206');

![1389653-20190621074142476-128998543.png][]

Table\_A  
id serNum  
\------ ---------  
1 A000100  
2 A000102  
3 A000103  
5 A000104  
8 A000105  
4 A000106

Table\_B  
id serNum  
\------ ---------  
1 B000201  
2 B000202  
3 B000203  
6 B000204  
7 B000205  
9 B000206

## 1. inner join 内连接查询 ##

![1336600-20190220174724532-2009232488.png][]

SELECT a.*,b.*
    FROM table_a a
    INNER JOIN table_b b
    ON a.id=b.id

查询结果：  
id serNum id serNum  
\------ ------- ------ ---------  
1 A000100 1 B000201  
2 A000102 2 B000202  
3 A000103 3 B000203

## 2. left join 左关联查询 ##

以左表作为基础表去关联右表，查询的结果为左表的子集

![1336600-20190220174829563-2104234923.png][]

SELECT a.*,b.*
    FROM table_a a
    LEFT JOIN table_b b
    ON a.id=b.id

查询结果：  
id serNum id serNum  
\------ ------- ------ ---------  
1 A000100 1 B000201  
2 A000102 2 B000202  
3 A000103 3 B000203  
5 A000104 (NULL) (NULL)  
8 A000105 (NULL) (NULL)  
4 A000106 (NULL) (NULL)

## 3.right join 右关联查询 ##

以右表作为基础表去关联左表，查询的结果为右表的子集

![1336600-20190220175137351-1413903383.png][]

SELECT a.*,b.*
    FROM table_a a
    RIGHT JOIN table_b b
    ON a.id=b.id

查询结果：  
id serNum id serNum  
\------ ------- ------ ---------  
1 A000100 1 B000201  
2 A000102 2 B000202  
3 A000103 3 B000203  
(NULL) (NULL) 6 B000204  
(NULL) (NULL) 7 B000205  
(NULL) (NULL) 9 B000206

## 4.左连接-内连接 ##

取左表的部分集合，但又不存在右表中  
![1336600-20190220185321821-816934833.png][]

SELECT a.*,b.*
    FROM table_a a
    LEFT JOIN table_b b
    ON a.id=b.id WHERE b.id IS NULL

查询结果：  
id serNum id serNum  
\------ ------- ------ --------  
5 A000104 (NULL) (NULL)  
8 A000105 (NULL) (NULL)  
4 A000106 (NULL) (NULL)

## 5. 右连接-内连接 ##

取有表的部分数据，但又不存在左表中  
![1336600-20190220185529426-1437724343.png][]

SELECT a.*,b.*
    FROM table_a a
    RIGHT JOIN table_b b
    ON a.id=b.id WHERE a.id IS NULL

查询结果：  
id serNum id serNum  
\------ ------ ------ ---------  
(NULL) (NULL) 6 B000204  
(NULL) (NULL) 7 B000205  
(NULL) (NULL) 9 B000206

转载于:https://www.cnblogs.com/cwind/p/11062440.html

[1389653-20190621074142476-128998543.png]: /images/20211221/fdc380b7ec7c4f2d9c6d1da668d13d82.png
[1336600-20190220174724532-2009232488.png]: /images/20211221/b876227237084ceba80fd4b894757dce.png
[1336600-20190220174829563-2104234923.png]: /images/20211221/afb51e7afdb44812bbf311f9303bb7b8.png
[1336600-20190220175137351-1413903383.png]: /images/20211221/bd253cc37c994110a68988c393c2c4c2.png
[1336600-20190220185321821-816934833.png]: /images/20211221/1c63f727bc3542acabe6105cd51a2c7a.png
[1336600-20190220185529426-1437724343.png]: /images/20211221/cbfda8ec265d45cfadf61e1bd0c290c3.png