一.基本概念

關(guān)于sql語句中的連接（join）關(guān)鍵字，是較為常用而又不太容易理解的關(guān)鍵字，下面這個(gè)例子給出了一個(gè)簡單的解釋 –建表user1,user2：

table1 : create table user2(id int, user_name varchar(10), over varchar(10));
insert into user1 values(1, ‘tangseng', ‘dtgdf');
insert into user1 values(2, ‘sunwukong', ‘dzsf');
insert into user1 values(1, ‘zhubajie', ‘jtsz');
insert into user1 values(1, ‘shaseng', ‘jslh');
table2 : create table user2(id int, user_name varchar(10), over varchar(10));
insert into user2 values(1, ‘sunwukong', ‘chengfo');
insert into user2 values(2, ‘niumowang', ‘chengyao');
insert into user2 values(3, ‘jiaomowang', ‘chengyao');
insert into user2 values(4, ‘pengmowang', ‘chengyao');

SQL標(biāo)準(zhǔn)中Join的類型

1. 內(nèi)連接（inner join或join）

(1).概念：內(nèi)聯(lián)接是基于連接謂詞將兩張表的列結(jié)合在一起，產(chǎn)生新的結(jié)果表

(2).內(nèi)連接維恩圖：

(3).sql語句

select a.id, a.user_name, b.over from user1 a inner join user2 b on a.user_name=b.user_name;

結(jié)果：

2. 外連接

外連接包括左向外聯(lián)接、右向外聯(lián)接或完整外部聯(lián)接

a.左外連接：left join 或 left outer join
(1)概念：左向外聯(lián)接的結(jié)果集包括 LEFT OUTER 子句中指定的左表的所有行，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值(null)。

(2)左外連接維恩圖：

(3)sql語句：

select a.id, a.user_name, b.over from user1 a left join user2 b on a.user_name=b.user_name;

結(jié)果：

b.右外連接：right join 或 right outer join
(1)右向外聯(lián)接是左向外聯(lián)接的反向聯(lián)接。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。
(2)右外連接維恩圖：

(3)sql語句

select b.user_name, b.over, a.over from user1 a right join user2 b on a.user_name=b.user_name;

結(jié)果：

c.全外連接：full join 或 full outer join

(1)完整外部聯(lián)接返回左表和右表中的所有行。當(dāng)某行在另一個(gè)表中沒有匹配行時(shí)，則另一個(gè)表的選擇列表列包含空值。如果表之間有匹配行，則整個(gè)結(jié)果集行包含基表的數(shù)據(jù)值。
(2)右外連接維恩圖：

(3)sql語句

select a.id, a.user_name, b.over from user1 a full join user2 b on a.user_name=b.user_name

在mysql中查詢?nèi)B接會(huì)報(bào)1064的錯(cuò)誤，mysql不支持全連接查詢，代替語句：

select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name = b.user_name union all select b.user_name,b.over ,a.over from user1 a right join user2 b on a.user_name = b.user_name;

結(jié)果：

3. 笛卡爾連接（交叉連接）

1.概念：沒有 WHERE 子句的交叉聯(lián)接將產(chǎn)生聯(lián)接所涉及的表的笛卡爾積。第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù)等于笛卡爾積結(jié)果集的大小。（user1和user2交叉連接產(chǎn)生4*4=16條記錄）
2.交叉連接：cross join (不帶條件on)
3.sql語句：
select a.user_name,b.user_name, a.over, b.over from user1 a cross join user2 b;

二.使用技巧

1. 使用join更新表
我們使用下面語句將user1表中同時(shí)存在user1表和user2表中記錄的over字段更新為 ‘qtda'。

update user1 set over='qtds'where user1.user_name in (select b.user_name from user1 a inner join user2 b on a.user_name = b.user_name);

這條語句在sql server, oracle中都可以正確執(zhí)行，在mysql卻報(bào)錯(cuò)，mysql不支持更新子查詢的表，那么我們使用下面語句可以在做到。

update user1 a join (select b.user_name from user1 a join user2 b on a.user_name = b.user_name) b on a.user_name = b.user_name set a.over = ‘qtds'

2. 使用join優(yōu)化子查詢

子查詢效率比較低效，使用下面語句進(jìn)行查詢
select a.user_name, a.over,(select over from user2 b where a.user_name=b.user_name) as over2 from user1 a;
使用join優(yōu)化子查詢，可以實(shí)現(xiàn)同樣的效果
select a.user_name, a.over, b.over as over2 from user1 a left join user2
b on a.user_name = b.user_name;

3. 使用join優(yōu)化聚合子查詢

引入一張新表：user_kills
create table user_kills(user_id int, timestr varchar(20), kills int(10));
insert into user_kills values(2, ‘2015-5-12', 20);
insert into user_kills values(2, ‘2015-5-15', 18);
insert into user_kills values(3, ‘2015-5-11', 16);
insert into user_kills values(3, ‘2015-5-14', 13);
insert into user_kills values(3, ‘2015-5-16', 17);
insert into user_kills values(4, ‘2015-5-12', 16);
insert into user_kills values(4, ‘2015-5-10', 13);

查詢user1中每人對(duì)應(yīng)user_kills表中kills最大的日期，使用聚合子查詢語句：

select a.user_name,b.timestr, b.kills from user1 a join user_kills b on a
.id = b.user_id where b.kills = (select MAX(c.kills) from user_kills c where c.user_id = b.user_id);

使用join優(yōu)化聚合子查詢（避免子查詢）

select a.user_name, b.timestr, b.kills from user1 a join user_kills b on
a.id = b.user_id join user_kills c on c.user_id = b.user_id group by a.user_name, b.timestr, b.kills having b.kills = max(c.kills);

結(jié)果：

4. 實(shí)現(xiàn)分組選擇數(shù)據(jù)

要求查詢出user1中每個(gè)人kills對(duì)多的前兩天。
首先，我們可以通過下面語句查詢出某個(gè)人kills最多的兩天；

select a.user_name, b.timestr, b.kills from user1 a join user_kills b on
a.id = b.user_id where a.user_name ='sunwukong' order by b.kills desc limit 2;

那么如何通過一個(gè)語句查詢出所有人kills最多的兩天的呢？看下面的語句：

WITH tmp AS (select a.user_name, b.timestr, b.kills, ROW_NUMBER() over(partition by a.user_name order by b.kills) cnt from user1 a join user_kills b on a.id = b.user_id) select * from tmp where cnt <= 2;

上面的語句在sql server和oracle都是支持的，但是mysql不支持分組排序函數(shù)ROW_NUMBER(),下面提供一種替代方法：

select d.user_name,c.timestr, kills from (select user_id, timestr, kills, (select count(*) from user_kills b where b.user_id = a.user_id and a.kills <= b.kills) as cnt from user_kills a group by user_id, timestr, kills) c join user1 d on c.user_id = d.id where cnt <= 2;

結(jié)果：

到此這篇關(guān)于SQL之Join的使用詳解的文章就介紹到這了,更多相關(guān)SQL之Join內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論