MySQL Study之--MySQL 表連接配接

一．Join文法概述

join 用于多表中字段之間的聯系，文法如下：

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON condition

table1:左表；table2:右表。

JOIN 按照功能大緻分為如下三類：

INNER JOIN（内連接配接,或等值連接配接）：取得兩個表中存在連接配接比對關系的記錄。

LEFT JOIN（左連接配接）：取得左表（table1）完全記錄，即是右表（table2）并無對應比對記錄。

RIGHT JOIN（右連接配接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無比對對應記錄。

注意：mysql不支援Full join,不過可以通過UNION 關鍵字來合并 LEFT JOIN 與 RIGHT JOIN來模拟FULL join.

案例分析：

1、案例環境

mysql> select * from emp;

mysql> select * from dept;

<code>| deptNO | DNAME | LOC |</code>

<code>| </code><code>10</code> <code>| ACCOUNTING | NEWYORK |</code>

<code>| </code><code>20</code> <code>| RESEARCH | DALLAS |</code>

<code>| </code><code>30</code> <code>| SALES | CHICAGO |</code>

<code>| </code><code>40</code> <code>| OPERATIONS | BOSTON |</code>

4 rows in set (0.00 sec)

inner join：（内連接配接,或等值連接配接）：取得兩個表中存在連接配接比對關系的記錄。

mysql> select e.empno,e.ename,e.sal,e.deptno,d.dname

-> from emp e

-> inner join dept d

-> where e.deptno=d.deptno;

-> on e.deptno=d.deptno;

隐式内連接配接：

-> from emp e,dept d

-> where e.deptno=d.deptno;

left join：取得左表（table1）完全記錄，即是右表（table2）并無對應比對記錄。

mysql> update emp set deptno=null where empno=7788;

Query OK, 1 row affected (0.07 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp where empno=7788;

采用等值連接配接：

mysql> select e.empno,e.ename,e.sal,e.deptno,d.dname

-> from emp e

-> inner join dept d on e.deptno=d.deptno;

-----對于等值連接配接，隻能看到條件比對的記錄！

-> left join dept d on e.deptno=d.deptno;

-----通過left join可以檢視到emp表中不符合條件的記錄！

right join：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無比對對應記錄。

mysql> select e.empno,e.ename,e.sal,d.deptno,d.dname

-> right join dept d on e.deptno=d.deptno;

------查詢到dept表中，不符合條件的記錄！！！

以下内容轉自：http://www.jb51.net/article/50427.htm

淺析Mysql Join文法以及性能優化

在講MySQL的Join文法前還是先回顧一下聯結的文法，呵呵，其實連我自己都忘得差不多了，那就大家一起溫習吧，這裡我有個比較簡便的記憶方法，内外聯結的差別是内聯結将去除所有不符合條件的記錄，而外聯結則保留其中部分。外左聯結與外右聯結的差別在于如果用A左聯結B則A中所有記錄都會保留在結果中，此時B中隻有符合聯結條件的記錄，而右聯結相反，這樣也就不會混淆

了。

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

接下來給出一個列子用于解釋下面幾種分類。如下兩個表(A,B)

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;

<code>| </code><code>1</code> <code>| Pirate | Rutabaga |</code>

<code>| </code><code>2</code> <code>| Monkey | Pirate |</code>

<code>| </code><code>3</code> <code>| Ninja | Darth Vader |</code>

<code>| </code><code>4</code> <code>| Spaghetti | Ninja |</code>

二.Inner join

内連接配接，也叫等值連接配接，inner join産生同時符合A和B的一組資料。

mysql> select * from A inner join B on A.name = B.name;

三.Left join

mysql> select * from A left join B on A.name = B.name;

#或者：select * from A left outer join B on A.name = B.name;

left join,（或left outer join:在Mysql中兩者等價，推薦使用left join.）左連接配接從左表(A)産生一套完整的記錄,與比對的記錄(右表(B)) .如果沒有比對,右側将包含null

如果想隻從左表(A)中産生一套記錄，但不包含右表(B)的記錄，可以通過設定where語句來執行，如下：

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;

同理，還可以模拟inner join. 如下：

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;

求差集：

根據上面的例子可以求差集，如下：

SELECT * FROM A LEFT JOIN B ON A.name = B.name

WHERE B.id IS NULL

union

SELECT * FROM A right JOIN B ON A.name = B.name

WHERE A.id IS NULL;

# 結果

四.Right join

mysql> select * from A right join B on A.name = B.name;

同left join。

五.Cross join

cross join：交叉連接配接，得到的結果是兩個表的乘積，即笛卡爾積

笛卡爾（Descartes）乘積又叫直積。假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以擴充到多個集合的情況。類似的例子有，如果A表示某學校學生的集合，B表示該學校所有課程的集合，則A與B的笛卡爾積表示所有可能的選課情況。

mysql> select * from A cross join B;

#再執行：mysql> select * from A inner join B; 試一試

#在執行mysql> select * from A cross join B on A.name = B.name; 試一試

實際上，在 MySQL 中（僅限于 MySQL） CROSS JOIN 與 INNER JOIN 的表現是一樣的，在不指定 ON 條件得到的結果都是笛卡爾積，反之取得兩個表完全比對的結果。 INNER JOIN 與 CROSS JOIN 可以省略 INNER 或 CROSS 關鍵字，是以下面的 SQL 效果是一樣的：

... FROM table1 INNER JOIN table2

... FROM table1 CROSS JOIN table2

... FROM table1 JOIN table2

六.Full join

mysql> select * from A left join B on B.name = A.name

-> union

-> select * from A right join B on B.name = A.name;

全連接配接産生的所有記錄（雙方比對記錄）在表A和表B。如果沒有比對,則對面将包含null。

七.性能優化

1.顯示(explicit) inner join VS 隐式(implicit) inner join

如：

select * from

table a inner join table b

on a.id = b.id;

select a.*, b.*

from table a, table b

where a.id = b.id;

我在資料庫中比較(10w資料)得之，它們用時幾乎相同，第一個是顯示的inner join，後一個是隐式的inner join。

2.left join/right join VS inner join

盡量用inner join.避免 LEFT JOIN 和 NULL.

在使用left join（或right join）時，應該清楚的知道以下幾點：

(1). on與 where的執行順序

ON 條件（“A LEFT JOIN B ON 條件表達式”中的ON）用來決定如何從 B 表中檢索資料行。如果 B 表中沒有任何一行資料比對 ON 的條件,将會額外生成一行所有列為 NULL 的資料,在比對階段 WHERE 子句的條件都不會被使用。僅在比對階段完成以後，WHERE 子句條件才會被使用。它将從比對階段産生的資料中檢索過濾。

是以我們要注意：在使用Left (right) join的時候，一定要在先給出盡可能多的比對滿足條件，減少Where的執行。如：

PASS

select * from A

inner join B on B.name = A.name

left join C on C.name = B.name

left join D on D.id = C.id

where C.status>1 and D.status=1;

left join C on C.name = B.name and C.status>1

left join D on D.id = C.id and D.status=1

從上面例子可以看出，盡可能滿足ON的條件，而少用Where的條件。從執行性能來看第二個顯然更加省時。

(2).注意ON 子句和 WHERE 子句的不同

如作者舉了一個列子：

mysql> SELECT * FROM product LEFT JOIN product_details

ON (product.id = product_details.id)

AND product_details.id=2;

+----+--------+------+--------+-------+

| 2 | 200 | 2 | 22 | 0 |

WHERE product_details.id=2;

+----+--------+----+--------+-------+

| 2 | 200 | 2 | 22 | 0 |

1 row in set (0.01 sec)

從上可知，第一條查詢使用 ON 條件決定了從 LEFT JOIN的 product_details表中檢索符合的所有資料行。第二條查詢做了簡單的LEFT JOIN，然後使用 WHERE 子句從 LEFT JOIN的資料中過濾掉不符合條件的資料行。

(3).盡量避免子查詢，而用join

往往性能這玩意兒，更多時候展現在資料量比較大的時候，此時，我們應該避免複雜的子查詢。如下：

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);

insert into t1(a1)

select b1 from t2

left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id

where t1.id is null;

本文轉自客居天涯 51CTO部落格，原文連結：http://blog.51cto.com/tiany/1716087，如需轉載請自行聯系原作者

MySQL Study之--MySQL 表連接配接

繼續閱讀

雲計算面試題——mysql/存儲引擎/備份

2021-08-13c++——類之操作符重載

SQL語言基礎：常用的資料查詢語句

swmm與lisflood-fp源碼如何一起編譯 CMake指令

Windows下VS開發環境環境安裝工程項目設定關于Debug和Release的提示

一文看懂字元串的加減乘除

C++ 第十五周報告1--《冒泡法排序》

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

C++實作簡單順序表

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

C經典書籍筆記——C陷阱與缺陷②(文法陷阱之優先級)一、錯誤案列二、優先級規律

mysql使用source指令導入.sql檔案

線性表之順序表的實作

C++判斷素數、求最大公約數代碼判斷一個數是否為素數求兩個數的最大公約數

SequoiaDB巨杉資料庫C++驅動概述