一、SQL 連接(JOIN)

1、笛卡爾積

（1）當(dāng)多張表進(jìn)行連接查詢(xún)，沒(méi)有任何條件限制的時(shí)候，最終查詢(xún)結(jié)果條數(shù)，是多張表?xiàng)l數(shù)的乘積

如A表15條（行）數(shù)據(jù)，B表20條（行）數(shù)據(jù)，結(jié)果查詢(xún)兩張表時(shí)，會(huì)產(chǎn)生 15 * 20 = 300條（行）數(shù)據(jù)

select empname,deptname from emp, dept;

（2）避免笛卡爾積現(xiàn)象

select 
		empname,deptname 
	from 
		emp, dept
	where
		emp.deptno = dept.deptno;
 
	
// 或者
	select 
		e.empname,d.deptname 
	from 
		emp e, dept d
	where
		e.deptno = d.deptno; //SQL92語(yǔ)法

最終得出結(jié)果會(huì)減少，但是查詢(xún)次數(shù)依然是兩張表行數(shù)的乘積

因此：通過(guò)笛卡爾積現(xiàn)象得出，表的連接次數(shù)越多效率越低，盡量避免表的連接次數(shù)

2、SQL JOIN 子句

用于把來(lái)自?xún)蓚€(gè)或多個(gè)表的行結(jié)合起來(lái)，基于這些表之間的共同字段（跨表查詢(xún)）

• SQL92：1992年的SQL語(yǔ)法
• SQL99：1999年的SQL語(yǔ)法

從一張表中單獨(dú)查詢(xún)，稱(chēng)為單表查詢(xún)

下圖展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相關(guān)的 7 種用法

3、SQL JOIN 類(lèi)型

（1）INNER JOIN：內(nèi)連接

【1】等值連接，返回兩個(gè)表中連接字段相等的行（條件是等量關(guān)系）

【2】非等值連接，條件不是一個(gè)等量關(guān)系

【3】自連接，同一張表看成多張表

注：

• INNER 可以省略
• 兩張表沒(méi)有主次關(guān)系；平等

（2）OUTER JOIN ：外連接

• LEFT (OUTER) JOIN：左（外）連接，即使右表中沒(méi)有匹配，也從左表返回所有的行（將join關(guān)鍵字左邊的表看成主表，主要是為了將左表的數(shù)據(jù)全部查詢(xún)出來(lái)，捎帶著關(guān)聯(lián)查詢(xún)右邊的表）
• RIGHT (OUTER) JOIN：右（外）連接，即使左表中沒(méi)有匹配，也從右表返回所有的行（將join關(guān)鍵字右邊的表看成主表，主要是為了將右表的數(shù)據(jù)全部查詢(xún)出來(lái)，捎帶著關(guān)聯(lián)查詢(xún)左邊的表）
• FULL (OUTER) JOIN ：全（外）連接

外連接，只要其中一個(gè)表中存在匹配，則返回；即返回兩個(gè)表中的行：left join + right join

注：

• OUTER 可以省略
• 在外連接當(dāng)中，兩張表連接，產(chǎn)生了主次關(guān)系

（3）交叉連接

CROSS JOIN: 結(jié)果是笛卡爾積，就是第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù)

4、SQL INNER JOIN

INNER JOIN 關(guān)鍵字在表中存在至少一個(gè)匹配時(shí)返回行

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
 
或者
 
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
INNER JOIN 與 JOIN 是相同的

（1）等值連接

SQL92語(yǔ)法：
	select 
		e.ename,d.dname
	from
		emp e, dept d
	where
		e.deptno = d.deptno;
//sql92的缺點(diǎn)：結(jié)構(gòu)不清晰，表的連接條件，和后期進(jìn)一步篩選的條件，都放到了where后面。
 
SQL99語(yǔ)法：
	select 
		e.ename,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;
	
 
	//inner可以省略（帶著inner可讀性更好?。?！一眼就能看出來(lái)是內(nèi)連接）
	select 
		e.ename,d.dname
	from
		emp e
	inner join
		dept d
	on
		e.deptno = d.deptno; // 條件是等量關(guān)系，所以被稱(chēng)為等值連接。
//sql99優(yōu)點(diǎn)：表連接的條件是獨(dú)立的，連接之后，如果還需要進(jìn)一步篩選，再往后繼續(xù)添加where

 inner可以省略，帶著inner可讀性更好

• sql92的缺點(diǎn)：結(jié)構(gòu)不清晰，表的連接條件，和后期進(jìn)一步篩選的條件，都放到了where后面
• sql99優(yōu)點(diǎn)：表連接的條件是獨(dú)立的，連接之后，如果還需要進(jìn)一步篩選，再往后繼續(xù)添加where

（2）非等值連接

select 
	e.ename, e.sal, s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal; // 條件不是一個(gè)等量關(guān)系，稱(chēng)為非等值連接。
 
 
select 
	e.ename, e.sal, s.grade
from
	emp e
inner join
	salgrade s
on
	e.sal between s.losal and s.hisal;

（3）自連接

一張表看成兩張表

select 
	a.ename as '員工名', b.ename as '領(lǐng)導(dǎo)名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno; //員工的領(lǐng)導(dǎo)編號(hào) = 領(lǐng)導(dǎo)的員工編號(hào)

 INNER JOIN 與 JOIN 是相同的

5、SQL LEFT JOIN

LEFT JOIN 關(guān)鍵字從左表（table1）返回所有的行，即使右表（table2）中沒(méi)有匹配。如果右表中沒(méi)有匹配，則結(jié)果為 NULL

將join關(guān)鍵字左邊的表看成主表，主要是為了將左表的數(shù)據(jù)全部查詢(xún)出來(lái)，捎帶著關(guān)聯(lián)查詢(xún)右邊的表

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
 
或
 
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
在某些數(shù)據(jù)庫(kù)中，LEFT JOIN 稱(chēng)為 LEFT OUTER JOIN

如下： 

select 
	e.ename,d.dname
from
	dept d 
left (outer) join 
	emp e
on
	e.deptno = d.deptno;

在某些數(shù)據(jù)庫(kù)中，LEFT JOIN 稱(chēng)為 LEFT OUTER JOIN

關(guān)鍵字 on  

數(shù)據(jù)庫(kù)在通過(guò)連接兩張或多張表來(lái)返回記錄時(shí)，都會(huì)生成一張中間的臨時(shí)表，然后再將這張臨時(shí)表返回給用戶(hù)

在使用 left jion 時(shí)，on 和 where 條件的區(qū)別如下：

（1） on 條件是在生成臨時(shí)表時(shí)使用的條件，它不管 on 中的條件是否為真，都會(huì)返回左邊表中的記錄。

（2）where 條件是在臨時(shí)表生成好后，再對(duì)臨時(shí)表進(jìn)行過(guò)濾的條件。這時(shí)已經(jīng)沒(méi)有 left join 的含義（必須返回左邊表的記錄）了，條件不為真的就全部過(guò)濾掉。

假設(shè)有兩張表：

 兩條 SQL:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
 
select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

以上結(jié)果的關(guān)鍵原因就是 left join、right join、full join 的特殊性，不管 on 上的條件是否為真都會(huì)返回 left 或 right 表中的記錄，full 則具有 left 和 right 的特性的并集。 而 inner jion 沒(méi)這個(gè)特殊性，則條件放在 on 中和 where 中，返回的結(jié)果集是相同的。

【6】SQL RIGHT JOIN

RIGHT JOIN 關(guān)鍵字從右表（table2）返回所有的行，即使左表（table1）中沒(méi)有匹配。如果左表中沒(méi)有匹配，則結(jié)果為 NULL

將join關(guān)鍵字右邊的表看成主表，主要是為了將右表的數(shù)據(jù)全部查詢(xún)出來(lái)，捎帶著關(guān)聯(lián)查詢(xún)左邊的表

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
 
或
 
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
在某些數(shù)據(jù)庫(kù)中，RIGHT JOIN 稱(chēng)為 RIGHT OUTER JOIN

如下： 

select 
	e.ename,d.dname
from
	emp e 
right (outer) join 
	dept d
on
	e.deptno = d.deptno;

在某些數(shù)據(jù)庫(kù)中，RIGHT JOIN 稱(chēng)為 RIGHT OUTER JOIN

【7】SQL FULL OUTER JOIN

FULL OUTER JOIN 關(guān)鍵字只要左表（table1）和右表（table2）其中一個(gè)表中存在匹配，則返回行

FULL OUTER JOIN 關(guān)鍵字結(jié)合了 LEFT JOIN 和 RIGHT JOIN 的結(jié)果（MySQL中不支持 FULL OUTER JOIN）

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

總結(jié)如下：

• A inner join B 取交集。
• A left join B 取 A 全部，B 沒(méi)有對(duì)應(yīng)的值為 null。
• A right join B 取 B 全部 A 沒(méi)有對(duì)應(yīng)的值為 null。
• A full outer join B 取并集，彼此沒(méi)有對(duì)應(yīng)的值為 null

如： "user" 表中的 "deptId" 列指向 "dept" 表中的字段 "id"；上面這兩個(gè)表是通過(guò) "deptId" 列聯(lián)系起來(lái)的

select u.id,d.id,d.name,d.number 
from user u left join dept d 
on u.deptId = d.id;
 
或
 
select u.id,u.name,d.id,d.name,d.number 
from user u inner join dept d 
on u.deptId = d.id;

查詢(xún)結(jié)果相同

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論