1、多表查詢概覽

1.1、分類

可以根據(jù)3個(gè)角度進(jìn)行分類：

角度1：是否使用"="符號(hào)

• 等值接連：where條件中，表字段與表字段直接使用等于符號(hào)（"="）進(jìn)行判斷
• 非等值連接：where條件中，表字段與表字段使用非"="符號(hào)，如：<=（小于等于）、>=（大于等于）、between and等等。

角度2：連接表的數(shù)量是否大于1

• 自連接：一張表直接的關(guān)聯(lián)查詢，自己表連接自己進(jìn)行查詢，如菜單表查子級(jí)
• 非自連接：多表關(guān)聯(lián)查詢

角度3：多表關(guān)聯(lián)時(shí)，是否只查詢有關(guān)聯(lián)的數(shù)據(jù)

• 內(nèi)連接：合并具有同一列的兩個(gè)以上的表的行，結(jié)果集中不包含一個(gè)表與另一個(gè)表不匹配的行
• 外連接：合并具有同一列的兩個(gè)以上的表的行，結(jié)果集中包含一個(gè)表與另一個(gè)表匹配的行之外，還包含了左表 或 右表不匹配的行

1.2、外連接的分類

• 左外連接（left outer join，可縮寫為left  join）：兩個(gè)表連接過(guò)程中，除了返回滿足條件的行以外，還會(huì)返回左表中不滿足條件的行，這種連接稱為左連接
• 右外連接（right outer join，可縮寫為right join）：兩個(gè)表連接過(guò)程中，除了返回滿足條件的行以外，還會(huì)返回右表中不滿足條件的行，這種連接稱為右連接
• 全連接（full outer join，可縮寫為full  join）：又稱為"滿外連接"，兩個(gè)表連接過(guò)程中，返回兩表直接的所有數(shù)據(jù)，這種連接稱為全連接

1.3、常用的SQL語(yǔ)法標(biāo)準(zhǔn)

• SQL92：1992發(fā)布的是數(shù)據(jù)庫(kù)的一個(gè)ANSI/ISO標(biāo)準(zhǔn)（偶爾使用）
• SQL99：1999發(fā)布的是數(shù)據(jù)庫(kù)的一個(gè)ANSI/ISO標(biāo)準(zhǔn)（現(xiàn)在開發(fā)中主流標(biāo)準(zhǔn)）

• ANSI：美國(guó)國(guó)家標(biāo)準(zhǔn)學(xué)會(huì)
• ISO：國(guó)際標(biāo)準(zhǔn)化組織

2、內(nèi)外聯(lián)接案例

2.1、初始化表

<1>學(xué)生表：student

create table if not exists taobao.student
(
	id int auto_increment primary key,
	name varchar(50) null,
	classid int null,
	age int null
)
comment '學(xué)生表';

INSERT INTO student (id, name, classid, age) VALUES (1, '張三', 1, 18);
INSERT INTO student (id, name, classid, age) VALUES (2, '李四', 1, 18);
INSERT INTO student (id, name, classid, age) VALUES (3, '王五', 2, 17);
INSERT INTO student (id, name, classid, age) VALUES (4, '老六', 2, 18);
INSERT INTO student (id, name, classid, age) VALUES (5, '七七', null, 17);
INSERT INTO student (id, name, classid, age) VALUES (6, '二流子', null, 19);
INSERT INTO student (id, name, classid, age) VALUES (7, '巴哥', null, 18);

• 數(shù)據(jù)如圖所示：

• 

 <2>班級(jí)表：classinfo

create table if not exists taobao.classinfo
(
	classid int auto_increment primary key,
	name varchar(100) null
)
comment '班級(jí)表';

INSERT INTO classinfo (name) VALUES ('高一1班');
INSERT INTO classinfo (name) VALUES ('高一2班');
INSERT INTO classinfo (name) VALUES ('高一3班');

2.2、內(nèi)連接

需求：查詢已分配的學(xué)生信息，如：學(xué)生基本信息，所在班級(jí)名稱

<1>SQL92內(nèi)連接寫法：

select
    t1.id       -- 學(xué)生ID
    ,t1.name    -- 學(xué)生姓名
    ,t1.age     -- 學(xué)生年齡
    ,t2.name    -- 班級(jí)名稱
from student t1,classinfo t2
where t1.classid=t2.classid

<2>SQL99內(nèi)連接寫法：

select
    t1.id       -- 學(xué)生ID
    ,t1.name    -- 學(xué)生姓名
    ,t1.age     -- 學(xué)生年齡
    ,t2.name    -- 班級(jí)名稱
from student t1
join classinfo t2
on t1.classid=t2.classid

結(jié)果：

2.3、外連接案例

需求：查詢所有的學(xué)生信息，并查出學(xué)生所對(duì)應(yīng)的班級(jí)名稱

【注意：多表查詢時(shí)，當(dāng)查詢一個(gè)表所有數(shù)據(jù)，該查詢語(yǔ)句一定是外連接】

 <1>SQL92外連接寫法：

注意：

• 這種寫法MySQL不支持，但在Oracle中支持

• 在不需要查詢表中所有數(shù)據(jù)的那張表后，添加”(+)”，表示外連接（理解為這個(gè)表只是附加的）

select
    t1.id       -- 學(xué)生ID
     ,t1.name    -- 學(xué)生姓名
     ,t1.age     -- 學(xué)生年齡
     ,t2.name    -- 班級(jí)名稱
from student t1,classinfo t2
where t1.classid=t2.classid<strong>(+)</strong>

<2>SQL99外連接寫法：

左連接寫法：

select
    t1.id       -- 學(xué)生ID
     ,t1.name    -- 學(xué)生姓名
     ,t1.age     -- 學(xué)生年齡
     ,t2.name    -- 班級(jí)名稱
from student t1
left join classinfo t2    --注意：left join是縮寫，也可以寫為：left outer join
on t1.classid=t2.classid

右連接寫法：

select
    t2.id       -- 學(xué)生ID
    ,t2.name    -- 學(xué)生姓名
    ,t2.age     -- 學(xué)生年齡
    ,t1.name    -- 班級(jí)名稱
from classinfo t1
right join student t2
on t1.classid=t2.classid

 結(jié)果：

2.4、全連接案例

需求：查詢學(xué)生表中的所有信息，并關(guān)聯(lián)班級(jí)表信息及顯示未關(guān)聯(lián)的班級(jí)表信息

• 在SQL92中，并不直接支持全連接語(yǔ)法

SQL99全連接寫法（Oracle）：

關(guān)鍵字：full join ... on ... 或者 full outer join ... on ...

注意：MySQL不支持全連接，但是Oracle支持

select
    t1.id       -- 學(xué)生ID
     ,t1.name    -- 學(xué)生姓名
     ,t1.age     -- 學(xué)生年齡
     ,t2.name    -- 班級(jí)名稱
from student t1
full join classinfo t2
on t1.classid=t2.classid

MySQL實(shí)現(xiàn)全連接，需要使用關(guān)鍵字"union"或者"union all"

2.5、union和union all

union：聯(lián)合、合并的意思

• union：對(duì)兩個(gè)查詢的結(jié)果集，進(jìn)行合并操作，會(huì)對(duì)重復(fù)的數(shù)據(jù)進(jìn)行去重，同時(shí)進(jìn)行默認(rèn)規(guī)則（主鍵升序）的排序（因此效率比較低）。

• union all：對(duì)兩個(gè)查詢的結(jié)果集，進(jìn)行合并操作，不對(duì)數(shù)據(jù)進(jìn)行去重，也不進(jìn)行排序，直接把兩個(gè)結(jié)果進(jìn)行合并（效率高）。

例如：我們把學(xué)生表查詢兩次，并使用union或union all進(jìn)行合并

<1>union 語(yǔ)句

select * from student
union   -- 會(huì)進(jìn)行去重操作
select * from student

結(jié)果：

 <2>union  all 語(yǔ)句

select * from student
union all  -- 不去重
select * from student

結(jié)果：

注意：

•  union和union all使用時(shí)，select下的字段數(shù)量必須一致，否則會(huì)報(bào)錯(cuò)

2.6、實(shí)現(xiàn)MySQL全連接

需求：查詢學(xué)生表中的所有信息，并關(guān)聯(lián)班級(jí)表信息及顯示未關(guān)聯(lián)的班級(jí)表信息

實(shí)現(xiàn)方式有多種，這里我使用：

• 首先查詢出學(xué)生表所有信息并顯示對(duì)應(yīng)的班級(jí)表信息
• 其次查詢班級(jí)表中，classid不在學(xué)生表中的數(shù)據(jù)
• 把上述結(jié)果使用union all合并

代碼如下：

select
    t1.id       -- 學(xué)生ID
     ,t1.name    -- 學(xué)生姓名
     ,t1.age     -- 學(xué)生年齡
     ,t2.name    -- 班級(jí)名稱
from student t1
left join classinfo t2    -- 注意：left join是縮寫，也可以寫為：left outer join
on t1.classid=t2.classid
union all
select
    null        -- null：這里設(shè)置為null，只是為了與上一個(gè)select的結(jié)果行字段（數(shù)量）進(jìn)行匹配，以下2個(gè)null作用一樣
    ,null
    ,null
    ,t1.name
from classinfo t1
where t1.classid not in (
    select
        distinct classid    -- distinct表示去重
    from student t2 where t2.classid is not null
    )

結(jié)果：

2.7、內(nèi)外連接面試基礎(chǔ)

 上述圖對(duì)應(yīng)7種多表查詢，是面試及實(shí)際開發(fā)中，必會(huì)的操作，這里就不多言了

• A：看作是學(xué)生表
• B：看作是班級(jí)表

注意：當(dāng)關(guān)聯(lián)表的數(shù)量超過(guò)3個(gè)時(shí)，禁止使用join，因?yàn)橐粋€(gè)join相當(dāng)于一個(gè)for，性能會(huì)很差

2.8、SQL99多表查詢新特性

<1>natural  join

•  自然連接：就是等值（內(nèi)）連接，會(huì)自動(dòng)查詢兩張連接表中所有相同的字段，然后進(jìn)行等值連接

如：上面的內(nèi)連接SQL為：

select
    t1.id       -- 學(xué)生ID
    ,t1.name    -- 學(xué)生姓名
    ,t1.age     -- 學(xué)生年齡
    ,t2.name    -- 班級(jí)名稱
from student t1
join classinfo t2
on t1.classid=t2.classid

使用natural  join進(jìn)行改造，如下：

select
    t1.id       -- 學(xué)生ID
     ,t1.name    -- 學(xué)生姓名
     ,t1.age     -- 學(xué)生年齡
     ,t2.name    -- 班級(jí)名稱
from student t1
natural join classinfo t2    --自然連接

結(jié)果：

查詢到了0條數(shù)據(jù)，這是因?yàn)椋?/p>

• natural  join 關(guān)聯(lián)多張表時(shí)，會(huì)自動(dòng)根據(jù)表中相同的字段名稱去匹配
• 上述student表中classid（班級(jí)編號(hào)）、name（學(xué)生姓名）與classinfo表中的 classid（班級(jí)編號(hào)）、name（班級(jí)名稱）是一樣的字段，而班級(jí)名稱不可能與學(xué)生姓名相等，所以查詢不到數(shù)據(jù)

也就是上述的自然連接，轉(zhuǎn)義為內(nèi)連接的SQL為：

select
    t1.id       -- 學(xué)生ID
     ,t1.name    -- 學(xué)生姓名
     ,t1.age     -- 學(xué)生年齡
     ,t2.name    -- 班級(jí)名稱
from student t1
join classinfo t2
on t1.classid=t2.classid
and t1.name = t2.name    -- 這個(gè)條件也被自然連接附帶上了

因此，使用natural join的前提條件就是：

• 多表關(guān)聯(lián)時(shí)，關(guān)聯(lián)字段名稱必須相同
• 不進(jìn)行關(guān)聯(lián)的字段名稱必須不相同

綜上：在實(shí)際開發(fā)中，我們應(yīng)當(dāng)避免使用natural  join，造成表與表之間的耦合較高

<2>using

• 等值條件的一種優(yōu)化寫法

語(yǔ)法：

• using(多表關(guān)聯(lián)的字段名稱)

前提：

• 多表關(guān)聯(lián)時(shí)，關(guān)聯(lián)字段的名稱必須相同
• 注意：這種寫法公司一般不給使用，當(dāng)某個(gè)字段改名時(shí)，很難定位錯(cuò)誤

如：上面的內(nèi)連接SQL為：

select
    t1.id       -- 學(xué)生ID
    ,t1.name    -- 學(xué)生姓名
    ,t1.age     -- 學(xué)生年齡
    ,t2.name    -- 班級(jí)名稱
from student t1
join classinfo t2
on t1.classid=t2.classid

使用using:

select
    t1.id       -- 學(xué)生ID
     ,t1.name    -- 學(xué)生姓名
     ,t1.age     -- 學(xué)生年齡
     ,t2.name    -- 班級(jí)名稱
from student t1
join classinfo t2
using(classid)

結(jié)果：

 什么是外連接，和內(nèi)連接的區(qū)別是什么？

內(nèi)連接：假設(shè)A和B表進(jìn)行連接，使用內(nèi)連接的話，凡是A表和B表能夠匹配上的記錄查詢出來(lái)，這就是內(nèi)連接。AB兩張表沒(méi)有主副之分，兩張表是平等的。

外連接：假設(shè)A和B表進(jìn)行連接，使用外連接的話，AB兩張表中有一張表是主表，一張表是副表。主要查詢主表中的數(shù)據(jù)，捎帶查詢副表。當(dāng)副表中的數(shù)據(jù)沒(méi)有和主表中的數(shù)據(jù)匹配上，副表自動(dòng)模擬出null與之匹配。

三張表的連接查詢

案例：找出每一個(gè)員工的部門名稱以及工資等級(jí)

......

        A

join

        B

join

        C

on

        ...... 

表示：A表和B表先進(jìn)行表連接，連接之后A表繼續(xù)和C表進(jìn)行連接。

總結(jié)

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

最新評(píng)論