SQL中 LEFT JOIN 實(shí)用技巧
點(diǎn)擊下方“IT牧場(chǎng)”，選擇“設(shè)為星標(biāo)”
責(zé)編：樂(lè)樂(lè) | 來(lái)自：ZhaoYingChao88
鏈接：zyc88.blog.csdn.net/article/details/83002882
建表：
CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) DEFAULT NULL,
`age` varchar(200) DEFAULT NULL,
`sponsor_id` varchar(20) DEFAULT NULL COMMENT '業(yè)務(wù)發(fā)起人',
`gmt_create_user` int(11) NOT NULL COMMENT '創(chuàng)建人id',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
`gmt_modified` datetime DEFAULT NULL COMMENT '修改時(shí)間',
`gmt_modified_user` int(11) DEFAULT NULL COMMENT '修改人id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='測(cè)試表1';
CREATE TABLE `table2` (
`kid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) DEFAULT NULL,
`sponsor_id` varchar(20) DEFAULT NULL COMMENT '業(yè)務(wù)發(fā)起人',
`type` int(11) NOT NULL COMMENT '創(chuàng)建人id',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
`gmt_modified` datetime DEFAULT NULL COMMENT '修改時(shí)間',
`gmt_modified_user` int(11) DEFAULT NULL COMMENT '修改人id',
PRIMARY KEY (`kid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='測(cè)試表2';插入數(shù)據(jù)：
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (1, 't1', '11', '10', 1, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (2, 't2', '12', '10', 2, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (3, 't3', '13', '10', 3, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (4, 't4', '14', '20', 4, '2018-10-10 20:34:03', NULL, NULL);INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (1, 't1', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (2, 't2', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (3, 't3', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (4, 't4', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (5, 't5', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (6, 't6', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (7, 't7', '10', 2, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (8, 't1', '11', 1, '2018-10-10 20:38:10', NULL, NULL);查詢異常：
SELECT
a.*,
b.type
FROM
table1 a
LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id
WHERE
b.type = 1
AND a.sponsor_id = 10;簡(jiǎn)單說(shuō)明問(wèn)題出現(xiàn)的原因：
MySQL left join 語(yǔ)句格式為：
A LEFT JOIN B ON 條件表達(dá)式left join 是以A表為基礎(chǔ)，A表即左表，B表即右表。
左表(A)的記錄會(huì)全部顯示，而右表(B)只會(huì)顯示符合條件表達(dá)式的記錄，如果在右表(B)中沒(méi)有符合條件的記錄，則記錄不足的地方為NULL。
使用left join， A表與B表所顯示的記錄數(shù)為 1:1 或 1:0，A表的所有記錄都會(huì)顯示，B表只顯示符合條件的記錄。
但如果B表符合條件的記錄數(shù)大于1條，就會(huì)出現(xiàn)1:n的情況，這樣left join后的結(jié)果，記錄數(shù)會(huì)多于A表的記錄數(shù)。
所以解決辦法 都是從一個(gè)出發(fā)點(diǎn)出發(fā)，使A表與B表所顯示的記錄數(shù)為 1:1對(duì)應(yīng)關(guān)系。
解決方法：
使用非唯一標(biāo)識(shí)的字段做關(guān)聯(lián)
1 DISTINCT
select DISTINCT(id) from a left join b on a.id=b.aidDISTINCT查詢結(jié)果是 第一個(gè)表唯一的數(shù)據(jù) 重復(fù)的結(jié)果沒(méi)顯示出來(lái)
SELECT
DISTINCT(a.id), a.*,
b.type
FROM
table1 a
LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id
WHERE
b.type = 1
AND a.sponsor_id = 10;
SELECT
DISTINCT a.*,
b.type
FROM
table1 a
LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id
WHERE
b.type = 1
AND a.sponsor_id = 10;2 GROUP BY
select * from a left join(select id from b group by id) as b on a.id=b.aid拿出b表的一條數(shù)據(jù)關(guān)聯(lián) 使A表與B表所顯示的記錄數(shù)為 1:1對(duì)應(yīng)關(guān)系。
SELECT
a.*,
b.type
FROM
table1 a
LEFT JOIN ( SELECT * FROM table2 GROUP BY sponsor_id ) AS b ON a.sponsor_id = b.sponsor_id
WHERE
b.type = 1
AND a.sponsor_id = 10;3 max取唯一
select * from a left join (select max(id) from table group by id) as b on a.id=b.aid拿出b表的最后一條數(shù)據(jù)關(guān)聯(lián)
SELECT
a.*,
b.type
FROM
table1 a
LEFT JOIN ( SELECT MAX( kid ), type, sponsor_id FROM table2 GROUP BY sponsor_id ) AS b ON a.sponsor_id = b.sponsor_id
WHERE
b.type = 1
AND a.sponsor_id = 10;4 IN巧用
SELECT
a.*
FROM
table1 a
WHERE
a.sponsor_id IN ( SELECT sponsor_id FROM table2 WHERE type = 1 AND sponsor_id = 10 );
SELECT
a.*,
1
FROM
table1 a
WHERE
a.sponsor_id IN ( SELECT sponsor_id FROM table2 WHERE type = 1 AND sponsor_id = 10 );相信對(duì)于熟悉SQL的人來(lái)說(shuō)，LEFT JOIN非常簡(jiǎn)單，采用的時(shí)候也很多，但是有個(gè)問(wèn)題還是需要注意一下。假如一個(gè)主表M有多個(gè)從表的話A B C …..的話，并且每個(gè)表都有篩選條件，那么把篩選條件放到哪里，就得注意嘍。
（搜索公眾號(hào)Java知音，回復(fù)“2021”，送你一份Java面試題寶典）
比如有個(gè)主表M，卡號(hào)是主鍵。
有個(gè)從表A，客戶號(hào)、聯(lián)系方式是聯(lián)合主鍵，其中聯(lián)系方式，1-座機(jī)，2-手機(jī)號(hào)碼
如果想要查詢所有卡號(hào)對(duì)應(yīng)的手機(jī)號(hào)碼兩個(gè)字段，很簡(jiǎn)單，SQL語(yǔ)句如下：
SELECT A.卡號(hào),B.手機(jī)號(hào)碼
FROM A
LEFT JOIN B
ON A.客戶號(hào)=B.客戶號(hào)
WHERE B.聯(lián)系方式='2'相信很多人這樣寫(xiě)，估計(jì)實(shí)際工作中也會(huì)看到這樣的語(yǔ)句，并不是說(shuō)這么寫(xiě)一定會(huì)錯(cuò)誤，實(shí)際SQL表達(dá)的思想一定是要符合業(yè)務(wù)邏輯的。
前面已經(jīng)說(shuō)清楚，所有卡號(hào)對(duì)應(yīng)的手機(jī)號(hào)碼。所有卡號(hào)，所以首先肯定以A表作為主表，并且左關(guān)聯(lián)B表，這樣A表所有的卡號(hào)一定會(huì)顯示出來(lái)，但是如果B表的篩選條件放到最外層，這樣就相當(dāng)于將A表關(guān)聯(lián)B表又做了一遍篩選，結(jié)果就是
就會(huì)篩選出來(lái)這么一條數(shù)據(jù)，丟失了A表中其他的卡號(hào)。
實(shí)際工作中表結(jié)構(gòu)肯定沒(méi)這么簡(jiǎn)單，關(guān)聯(lián)的表也會(huì)很多，當(dāng)有很多條件時(shí)，最好這么寫(xiě)
SELECT A.卡號(hào),B.手機(jī)號(hào)碼
FROM A
LEFT JOIN (
SELECT * FROM B
B.聯(lián)系方式='2'
)B
ON A.客戶號(hào)=B.客戶號(hào)這么寫(xiě)的話，A表中的數(shù)據(jù)肯定會(huì)完全保留，又能與B表的匹配，不會(huì)丟失數(shù)據(jù)。
PS:
表結(jié)構(gòu) Left Join Right Join Inner Join 表的關(guān)聯(lián)修改和刪除 笛卡爾積 1、表結(jié)構(gòu)
表A
2、Left Join
示例：2.1
Select * From A left join B on A.aid = B.bid;left join是以A表的記錄為基礎(chǔ)的，A可以看成左表，B可以看成右表，left join是以左表為準(zhǔn)的。換句話說(shuō)，左表A的記錄將會(huì)全部表示出來(lái)，而右表B只會(huì)顯示符合搜索條件的記錄(例子中為: A.aid = B.bid)，B表記錄不足的地方均為NULL.
搜索公眾號(hào)后端架構(gòu)師后臺(tái)回復(fù)“架構(gòu)整潔”，獲取一份驚喜禮包。
A表所有記錄都會(huì)顯示，A表中沒(méi)有被匹配的行（如aid=5、6的行）相應(yīng)內(nèi)容則為NULL。 返回的記錄數(shù)一定大于A表的記錄數(shù)，如A表中aid=7行被B表匹配了3次（因?yàn)锽表有三行bid=7）。 注意：在Access中A.aid、B.bid不能縮寫(xiě)成aid、bid，否則會(huì)提示“不支持鏈接表達(dá)式”，這一點(diǎn)不同于Where查詢。
3、Right Join
示例：3.1
Select * From A right join B on A.aid = B.bid;仔細(xì)觀察一下，就會(huì)發(fā)現(xiàn)，和left join的結(jié)果剛好相反，這次是以右表(B)為基礎(chǔ)的，A表不足的地方用NULL填充。
4、Inner Join
示例：4.1
Select * From A inner join B on A.aid = B.bid;這里只顯示出了 A.aid = B.bid的記錄.這說(shuō)明inner join并不以誰(shuí)為基礎(chǔ)，它只顯示符合條件的記錄。
inner join 等同于Where查詢?nèi)纾?/p>
Select * From A， B Where A.aid = B.bid5、表的關(guān)聯(lián)修改和刪除
5.1修改
示例：5.1.1
update A left join B on A.aid = B.bid
set A.aname = B.bname上述SQL實(shí)際操作的表為"Select * From A left join B on A.aid = B.bid"，因此Access會(huì)提示更新13條記錄（Select查詢出的記錄就是13條）。對(duì)比“示例：2.1”返回的結(jié)果，分析update后的A表：
aid=5、6的記錄，被更新為NULL aid=7的記錄，被更新了3次，依次是“b1997-1”、“b1997-2”、“b1997-3”，因此其結(jié)果為最后一次更新“b1997-3” 對(duì)于上述SQL同樣可以將“A.aname = B.bname”改成“B.bname = A.aname”，執(zhí)行后B表將會(huì)被修改，但是執(zhí)行后B表會(huì)增加三行“0, a2005-1;0, a2005-2;0, a2006”，這也不難理解，因?yàn)長(zhǎng)eft Join執(zhí)行后，B表會(huì)出現(xiàn)三行空值。
示例：5.1.2
Where條件查詢?cè)谏厦娴腟QL中同樣可以使用，其作用的表也是Select查詢出的關(guān)聯(lián)表。如下SQL
update A left join B on A.aid = B.bid
set A.aname = B.bname
where A.aid <> 5執(zhí)行后A表的結(jié)果：
對(duì)比第一次update可以發(fā)現(xiàn)，aid=5的并沒(méi)有被更新。
這里只講述left join，因?yàn)閞ight join 和 inner join的處理過(guò)程等同于left join。另外Access中update語(yǔ)句中不能含有From關(guān)鍵字，這一點(diǎn)不同于其他數(shù)據(jù)庫(kù)。
5.2刪除
在Access中是不可以通過(guò)Left Join、Right Join、Inner Join來(lái)刪除某張表的記錄
示例：5.2.2
Delete From A inner join B on A.aid = B.bid
where B.bname = "b1991"上述SQL的本意是刪除A表中aid=1的記錄，但執(zhí)行后表A和表B均未發(fā)生任何變化。若想實(shí)現(xiàn)此目的，下述SQL可以實(shí)現(xiàn)
Delete From A
Where A.aid In (Select bid From B Where B.bname="b1991")6、笛卡爾積
如果A表有20條記錄，B表有30條記錄，則二者關(guān)聯(lián)后的笛卡爾積工
20*30=600條記實(shí)錄。也就是說(shuō)A表中的每條記錄都會(huì)于B表的所有記錄關(guān)聯(lián)一次，三種關(guān)聯(lián)方式實(shí)際上就是對(duì)“笛卡爾積”的處理方式不同。
干貨分享
最近將個(gè)人學(xué)習(xí)筆記整理成冊(cè)，使用PDF分享。關(guān)注我，回復(fù)如下代碼，即可獲得百度盤(pán)地址，無(wú)套路領(lǐng)?。?/p>
?001：《Java并發(fā)與高并發(fā)解決方案》學(xué)習(xí)筆記；?002：《深入JVM內(nèi)核——原理、診斷與優(yōu)化》學(xué)習(xí)筆記；?003：《Java面試寶典》?004：《Docker開(kāi)源書(shū)》?005：《Kubernetes開(kāi)源書(shū)》?006：《DDD速成（領(lǐng)域驅(qū)動(dòng)設(shè)計(jì)速成）》?007：全部?008：加技術(shù)群討論
關(guān)注我
喜歡就點(diǎn)個(gè)"在看"唄^_^