1. 笛卡爾積(交叉連接)

笛卡爾積是關(guān)系型數(shù)據(jù)庫，進(jìn)行多表查詢的基礎(chǔ)
A{a,b,c}
B{d,e,f,g}
A x B = {ad,ae,af,ag,bd,be,bf,bg,cd,ce,cf,cg}

查詢emp表與dept表笛卡爾積

select *
from emp cross join dept

cross join 又被稱為 交叉連接
使用emp表的每行數(shù)據(jù)與dept表的每行數(shù)據(jù)分別組合

image

2. 等值連接和不等值連接

2.1 等值連接

可以利用where條件對(duì)兩張表的笛卡爾積進(jìn)行篩選
只保留兩列deptno相等的數(shù)據(jù)

示例1： 查詢?cè)滦礁哂?000的員工的姓名，月薪和所在部門名稱

select ename, sal, dname
from emp cross join dept
where emp.deptno = dept.deptno and sal > 3000

select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and sal>3000

利用專門的join...on...語法完成等值篩選（內(nèi)連接）

select ename, sal, dname
from emp join dept on(emp.deptno = dept.deptno)
where sal > 3000

為表起別名
示例2： 查詢員工姓名，職位，月薪，所在部門編號(hào)，所在部門名稱

select e.ename, e.job, e.sal, d.dname
from emp e join dept d on(e.deptno = d.deptno)

更多張表進(jìn)行關(guān)聯(lián)

image

image

image

image

示例3：查詢員工姓名，部門名稱，工作地點(diǎn)，地址

select e.ename, d.dname, d.loc, l.adress
from emp e 
    join dept d on (e.deptno = d.deptno)
    join location l on (d.loc = l.locid)

就算查詢結(jié)果中沒有dept表的內(nèi)容，也得需要dept進(jìn)行關(guān)聯(lián)才能將emp和location關(guān)聯(lián)上
示例4：查詢員工姓名，地址

select e.ename,  l.adress
from emp e 
    join dept d on (e.deptno = d.deptno)
    join location l on (d.loc = l.locid)

select e.ename, d.dname, d.loc, l.address
from emp e,dept d, location l 
where e.deptno = d.deptno and d.loc = l.locid

2.2 不等值連接

篩選的就是連接條件不相等的那部分?jǐn)?shù)據(jù)

select ename, sal, dname
from emp join dept on(emp.deptno != dept.deptno)
where sal > 3000

3. 外連接（重點(diǎn)和難點(diǎn)）

emp表中有人沒有部門，所以笛卡爾積沒有等值關(guān)聯(lián)的項(xiàng)

image

image

3.1 左外連接（重要）

示例5：查詢所有員工的員工姓名和所在部門名稱(包括沒有部門的員工)

select * 
from emp e left join dept d on(e.deptno = d.deptno)

select *
from dept d right join emp e on(d.deptno = e.deptno)

image

3.2 右外連接

示例6：查詢所有員工的員工姓名和所在部門名稱(包括沒有員工的部門)

select * 
from emp e right join dept d on(e.deptno = d.deptno)

image

3.3 全外連接

MySQL不支持全外連接的標(biāo)準(zhǔn)寫法，在Oracle中是支持的

select * 
from emp e full join dept d on(e.deptno = d.deptno)

會(huì)將左連接和右連接的數(shù)據(jù)全部查詢出來

3.4 更多張表時(shí)外連接的傳導(dǎo)

示例7： 查詢員工的員工信息，部門信息和地點(diǎn)信息（含沒有部門的員工）

select * 
from emp e 
   left join dept d on(e.deptno = d.deptno)
   cross join location l 

image

select * 
from emp e 
   left join dept d on(e.deptno = d.deptno)
   left join location l on(d.loc = l.locid)

image

4. 自連接

一張表自己的列A去關(guān)聯(lián)自己列B

示例8：查詢員工編號(hào)，員工姓名，上司的編號(hào)和上司的姓名

select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1 
left join emp e2 on(e1.mgr = e2.empno);

分析：

• e1可以理解為員工表 mgr - 上司編號(hào)
• e2可以理解為上司表 empno - 上司的員工編號(hào)
• 員工KING沒有上司 通過left join 將他顯示出來