腳本之家服務器常用軟件

快捷導航

MySQL多表查詢內連接外連接詳解(使用join、left?join、right?join和full?join)

更新時間：2023年12月01日 10:50:06 作者：睡竹

這篇文章主要給大家介紹了關于MySQL多表查詢內連接外連接的相關資料,文中主要介紹的是使用join、left?join、right?join和full?join,文中通過代碼介紹的非常詳細,需要的朋友可以參考下

1、多表查詢概覽

1.1、分類

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

角度1：是否使用"="符號

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

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

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

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

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

1.2、外連接的分類

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

1.3、常用的SQL語法標準

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

ANSI：美國國家標準學會
ISO：國際標準化組織

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

2.1、初始化表

<1>學生表：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 '學生表';

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>班級表：classinfo

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

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

2.2、內連接

需求：查詢已分配的學生信息，如：學生基本信息，所在班級名稱

<1>SQL92內連接寫法：

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

<2>SQL99內連接寫法：

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

結果：

2.3、外連接案例

需求：查詢所有的學生信息，并查出學生所對應的班級名稱

【注意：多表查詢時，當查詢一個表所有數(shù)據(jù)，該查詢語句一定是外連接】

<1>SQL92外連接寫法：

注意：

這種寫法MySQL不支持，但在Oracle中支持
在不需要查詢表中所有數(shù)據(jù)的那張表后，添加”(+)”，表示外連接（理解為這個表只是附加的）

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

<2>SQL99外連接寫法：

左連接寫法：

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

右連接寫法：

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

結果：

2.4、全連接案例

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

在SQL92中，并不直接支持全連接語法

SQL99全連接寫法（Oracle）：

關鍵字：full join ... on ... 或者 full outer join ... on ...

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

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

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

2.5、union和union all

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

union：對兩個查詢的結果集，進行合并操作，會對重復的數(shù)據(jù)進行去重，同時進行默認規(guī)則（主鍵升序）的排序（因此效率比較低）。
union all：對兩個查詢的結果集，進行合并操作，不對數(shù)據(jù)進行去重，也不進行排序，直接把兩個結果進行合并（效率高）。

例如：我們把學生表查詢兩次，并使用union或union all進行合并

<1>union 語句

select * from student
union   -- 會進行去重操作
select * from student

結果：

<2>union all 語句

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

結果：

注意：

union和union all使用時，select下的字段數(shù)量必須一致，否則會報錯

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

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

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

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

代碼如下：

select
    t1.id       -- 學生ID
     ,t1.name    -- 學生姓名
     ,t1.age     -- 學生年齡
     ,t2.name    -- 班級名稱
from student t1
left join classinfo t2    -- 注意：left join是縮寫，也可以寫為：left outer join
on t1.classid=t2.classid
union all
select
    null        -- null：這里設置為null，只是為了與上一個select的結果行字段（數(shù)量）進行匹配，以下2個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
    )

結果：

2.7、內外連接面試基礎

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

A：看作是學生表
B：看作是班級表

注意：當關聯(lián)表的數(shù)量超過3個時，禁止使用join，因為一個join相當于一個for，性能會很差

2.8、SQL99多表查詢新特性

<1>natural join

自然連接：就是等值（內）連接，會自動查詢兩張連接表中所有相同的字段，然后進行等值連接

如：上面的內連接SQL為：

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

使用natural join進行改造，如下：

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

結果：

查詢到了0條數(shù)據(jù)，這是因為：

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

也就是上述的自然連接，轉義為內連接的SQL為：

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

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

多表關聯(lián)時，關聯(lián)字段名稱必須相同
不進行關聯(lián)的字段名稱必須不相同

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

<2>using

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

語法：

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

前提：

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

如：上面的內連接SQL為：

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

使用using:

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

結果：

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

內連接：假設A和B表進行連接，使用內連接的話，凡是A表和B表能夠匹配上的記錄查詢出來，這就是內連接。AB兩張表沒有主副之分，兩張表是平等的。

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

三張表的連接查詢

案例：找出每一個員工的部門名稱以及工資等級

......

A

join

B

join

C

on

......

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

總結

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

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL多表查詢內連接外連接詳解(使用join、left?join、right?join和full?join)

目錄

1、多表查詢概覽

1.1、分類

1.2、外連接的分類

1.3、常用的SQL語法標準

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

2.1、初始化表

2.2、內連接

2.3、外連接案例

2.4、全連接案例

2.5、union和union all

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

2.7、內外連接面試基礎

2.8、SQL99多表查詢新特性

總結

相關文章

最新評論

大家感興趣的內容

最近更新的內容

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL多表查詢內連接外連接詳解(使用join、left?join、right?join和full?join)

目錄

1、多表查詢概覽

1.1、分類

1.2、外連接的分類

1.3、常用的SQL語法標準

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

2.1、初始化表

2.2、內連接

2.3、外連接案例

2.4、全連接案例

2.5、union和union all

2.6、實現(xiàn)MySQL全連接

2.7、內外連接面試基礎

2.8、SQL99多表查詢新特性

總結

相關文章

最新評論

大家感興趣的內容

最近更新的內容

常用在線小工具

MySQL多表查詢內連接外連接詳解(使用join、left?join、right?join和full?join)

1、多表查詢概覽

1.1、分類

1.2、外連接的分類

1.3、常用的SQL語法標準

2.1、初始化表

2.2、內連接

2.4、全連接案例

2.5、union和union all

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

2.8、SQL99多表查詢新特性