SQL-关联查询【转】 淡淡的烟草味﹌ 2021-12-21 03:57 315阅读 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
还没有评论,来说两句吧...