mysql兩表聯合查詢

兩個表需要有共同的字段用來做對應關系，假定表a 的ab字段和表b 的bc字段意義是一樣的：

創新互聯建站于2013年開始，先為慈利等服務建站，慈利等地企業，進行企業商務咨詢服務。為慈利企業網站制作PC+手機+微官網三網同步一站式服務解決您的所有建站問題。

新建兩張表：

表a：ab ac ad

1 1 2

2 3 4

3 5 6

表b: bc bd be

1 7 8

3 9 10

4 11 12

（此時這樣建表只是為了演示連接SQL語句，當然實際開發中我們不會這樣建表，實際開發中這兩個表會有自己不同的主鍵。）

一、外連接

外連接可分為：左連接、右連接、完全外連接。

1、左連接 left join 或 left outer join

SQL語句：select 表a.ab,表a.ac,表a.ad,表b.bc,表b.bd,表b.be from 表a left join 表b on 表a.ab=表b.bd執行結果以ab行為準：

表：ab ac ad bc bd be

1 1 2 1 7 8

2 3 4 0 0 0

3 5 6 3 9 10

其中b表bc第二行的值在ab中沒有對應的所以左連無法找出來，同理右連會變成這樣：

SQL語句：select 表a.ab,表a.ac,表a.ad,表b.bc,表b.bd,表b.be from 表a left join 表b on 表a.ab=表b.bd執行結果以bd行為準：

表：ab ac ad bc bd be

1 1 2 1 7 8

3 5 6 3 9 10

0 0 0 4 11 12

左外連接包含left join左表所有行，如果左表中某行在右表沒有匹配，則結果中對應行右表的部分全部為0.

注：此時我們不能說結果的行數等于左表數據的行數。當然此處查詢結果的行數等于左表數據的行數，因為左右兩表此時為一對一關系。

右外連接包含right join右表所有行，如果左表中某行在右表沒有匹配，則結果中對應左表的部分全部為0。

注：同樣此時我們不能說結果的行數等于右表的行數。當然此處查詢結果的行數等于左表數據的行數，因為左右兩表此時為一對一關系。

3、完全外連接 full join 或 full outer join

SQL語句：select 表a.ab,表a.ac,表a.ad,表b.bc,表b.bd,表b.be from 表a full join 表b on 表a.ab=表b.bd執行結果：

表：ab ac ad bc bd be

1 1 2 1 7 8

2 3 4 0 0 0

3 5 6 3 9 10

0 0 0 4 11 12

完全外連接包含full join左右兩表中所有的行，如果右表中某行在左表中沒有匹配，則結果中對應行右表的部分全部為0，如果左表中某行在右表中沒有匹配，則結果中對應行左表的部分全部為0。

二、內連接 join 或 inner join

SQL語句：select 表a.ab,表a.ac,表a.ad,表b.bc,表b.bd,表b.be from 表a inner join 表b on 表a.ab=表b.bdinner join 是比較運算符，只返回符合條件的行。

表：ab ac ad bc bd be

1 1 2 1 7 8

3 5 6 3 9 10

三、交叉連接 cross join

1.概念：沒有 WHERE 子句的交叉聯接將產生連接所涉及的表的笛卡爾積。第一個表的行數乘以第二個表的行數等于笛卡爾積結果集的大小。

表a: ab

星期一

星期二

星期三

表b: cd

張三

李四

王五

SELECT a.ab,b.cd FROM 表a CROSS JOIN 表b

ab cd

星期一 張三

星期一 李四

星期一 王五

星期二 張三

星期二 李四

星期二 王五

星期三 張三

星期三 李四

星期三 王五

mysql內連接 比默認笛卡爾積 效率高在哪兒？

1. 多表連接類型

1. 笛卡爾積(交叉連接) 在MySQL中可以為CROSS JOIN或者省略CROSS即JOIN，或者使用',' 如：

SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2

由于其返回的結果為被連接的兩個數據表的乘積，因此當有WHERE, ON或USING條件的時候一般不建議使用，因為當數據表項目太多的時候，會非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

2. 內連接INNER JOIN 在MySQL中把INNER JOIN叫做等值連接，即需要指定等值連接條件在MySQL中CROSS和INNER JOIN被劃分在一起。 join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. MySQL中的外連接，分為左外連接和右連接，即除了返回符合連接條件的結果之外，還要返回左表(左連接)或者右表(右連接)中不符合連接條件的結果，相對應的使用NULL對應。

例子：

user表:

id | name

———

1 | libk

2 | zyfon

3 | daodao

user_action表:

user_id | action

—————

1 | jump

1 | kick

1 | jump

2 | run

4 | swim

sql:

select id, name, action from user as u left join user_action a on u.id = a.user_id

result:

id | name | action

——————————–

1 | libk | jump ①

1 | libk | kick ②

1 | libk | jump ③

2 | zyfon | run ④

3 | daodao | null ⑤

分析：

注意到user_action中還有一個user_id=4, action=swim的紀錄，但是沒有在結果中出現，

而user表中的id=3, name=daodao的用戶在user_action中沒有相應的紀錄，但是卻出現在了結果集中

因為現在是left join，所有的工作以left為準.

結果1，2，3，4都是既在左表又在右表的紀錄，5是只在左表，不在右表的紀錄

工作原理：

從左表讀出一條，選出所有與on匹配的右表紀錄(n條)進行連接，形成n條紀錄(包括重復的行，如：結果1和結果3)，如果右邊沒有與on條件匹配的表，那連接的字段都是null.然后繼續讀下一條。

引申：

我們可以用右表沒有on匹配則顯示null的規律, 來找出所有在左表，不在右表的紀錄， 注意用來判斷的那列必須聲明為not null的。

如：

sql:

select id, name, action from user as u left join user_action a on u.id = a.user_id where a.user_id is NULL

注意:

1.列值為null應該用is null 而不能用=NULL

2.這里a.user_id 列必須聲明為 NOT NULL 的.

上面sql的result:

id | name | action

————————–

3 | daodao | NULL

——————————————————————————–

一般用法：

a. LEFT [OUTER] JOIN：

除了返回符合連接條件的結果之外，還需要顯示左表中不符合連接條件的數據列，相對應使用NULL對應

復制代碼 代碼如下:SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column

b. RIGHT [OUTER] JOIN：

RIGHT與LEFT JOIN相似不同的僅僅是除了顯示符合連接條件的結果之外，還需要顯示右表中不符合連接條件的數據列，相應使用NULL對應

復制代碼 代碼如下:SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column

Tips:

1. on a.c1 = b.c1 等同于 using(c1)

2. INNER JOIN 和 , (逗號) 在語義上是等同的

3. 當 MySQL 在從一個表中檢索信息時，你可以提示它選擇了哪一個索引。

如果 EXPLAIN 顯示 MySQL 使用了可能的索引列表中錯誤的索引，這個特性將是很有用的。

通過指定 USE INDEX (key_list)，你可以告訴 MySQL 使用可能的索引中最合適的一個索引在表中查找記錄行。

可選的二選一句法 IGNORE INDEX (key_list) 可被用于告訴 MySQL 不使用特定的索引。如：

mysql SELECT * FROM table1 USE INDEX (key1,key2) - WHERE key1=1 AND key2=2 AND key3=3; mysql SELECT * FROM table1 IGNORE INDEX (key3) - WHERE key1=1 AND key2=2 AND key3=3;

2. 表連接的約束條件

添加顯示條件WHERE, ON, USING

1. WHERE子句

mysql

復制代碼 代碼如下:SELECT * FROM table1,table2 WHERE table1.id=table2.id;

2. ON

mysql

復制代碼 代碼如下:SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id

LEFT JOIN table3 ON table2.id=table3.id;

3. USING子句，如果連接的兩個表連接條件的兩個列具有相同的名字的話可以使用USING

例如：

SELECT FROM LEFT JOIN USING ()

連接多于兩個表的情況舉例：

mysql

SELECT artists.Artist, cds.title, genres.genre FROM cds LEFT JOIN genres N cds.genreID = genres.genreID LEFT JOIN artists ON cds.artistID = artists.artistID;

或者 mysql

SELECT artists.Artist, cds.title, genres.genre FROM cds LEFT JOIN genres ON cds.genreID = genres.genreID LEFT JOIN artists - ON cds.artistID = artists.artistID WHERE (genres.genre = 'Pop');

--------------------------------------------

另外需要注意的地方 在MySQL中涉及到多表查詢的時候，需要根據查詢的情況，想好使用哪種連接方式效率更高。

1. 交叉連接(笛卡爾積)或者內連接 [INNER | CROSS] JOIN

2. 左外連接LEFT [OUTER] JOIN或者右外連接RIGHT [OUTER] JOIN 注意指定連接條件WHERE, ON，USING.

3. MySQL如何優化LEFT JOIN和RIGHT JOIN

在MySQL中，A LEFT JOIN B join_condition執行過程如下：

1)· 根據表A和A依賴的所有表設置表B。

2)· 根據LEFT JOIN條件中使用的所有表(除了B)設置表A。

3)· LEFT JOIN條件用于確定如何從表B搜索行。(換句話說，不使用WHERE子句中的任何條件）。

4)· 可以對所有標準聯接進行優化，只是只有從它所依賴的所有表讀取的表例外。如果出現循環依賴關系，MySQL提示出現一個錯誤。

5)· 進行所有標準WHERE優化。

6)· 如果A中有一行匹配WHERE子句，但B中沒有一行匹配ON條件，則生成另一個B行，其中所有列設置為NULL。

7)· 如果使用LEFT JOIN找出在某些表中不存在的行，并且進行了下面的測試：WHERE部分的col_name IS NULL，其中col_name是一個聲明為 NOT NULL的列，MySQL找到匹配LEFT JOIN條件的一個行后停止(為具體的關鍵字組合)搜索其它行。

RIGHT JOIN的執行類似LEFT JOIN，只是表的角色反過來。

聯接優化器計算表應聯接的順序。LEFT JOIN和STRAIGHT_JOIN強制的表讀順序可以幫助聯接優化器更快地工作，因為檢查的表交換更少。請注意這說明如果執行下面類型的查詢，MySQL進行全掃描b，因為LEFT JOIN強制它在d之前讀取：

SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;

在這種情況下修復時用a的相反順序，b列于FROM子句中：

SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;

MySQL可以進行下面的LEFT JOIN優化：如果對于產生的NULL行，WHERE條件總為假，LEFT JOIN變為普通聯接。

例如，在下面的查詢中如果t2.column1為NULL，WHERE 子句將為false：

復制代碼 代碼如下:SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

因此，可以安全地將查詢轉換為普通聯接：

復制代碼 代碼如下:SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

這樣可以更快，因為如果可以使查詢更佳，MySQL可以在表t1之前使用表t2。為了強制使用表順序，使用STRAIGHT_JOIN。

MySQL中的交叉連接、內連接、外連接

學生student表：

課程course表：

交叉連接：

結果為兩張表的笛卡爾積：

內連接（inner join ，inner 可以省略）

顯示內連接和隱示內連接獲得的查詢結果是一樣的，都是A表和B表的交集（例：A.id = B.id），但是只能查到有關系的信息，如果A表的一條數據的與B表關聯的字段沒有對應的信息（如：A.id = null），即：這條數據在B表中沒有對應的信息，則無法獲得。

外連接（outer join，outer可以省略）

左外連接獲得的查詢結果是左邊的表A的全部信息和A，B兩張表的交集，左邊A表的全部包含A表中在B表中沒有對應關系的信息。

右外連接獲得的查詢結果是右邊的表B的全部信息和A，B兩張表的交集，右邊B表的全部包含B表中在A表中沒有對應關系的信息。

MySQL連接5種方式

SQL中的 join 可以根據相應條件把指定的表給結合起來并將數據返回。

內連接是基于連接謂詞將倆張表（如A和B）的列組合到一起產生新的結果表，在表中存在至少一個匹配時，INNER JOIN 關鍵字返回行

左外連接Left join關鍵字會從左表那里返回所有的行，即使是在右表中沒有匹配到的行?

右外連接關鍵字Right join會從右表那里返回所有的行，即使是在左表中沒有匹配到的行?

全連接的關鍵字Full join，只要其中某個表中存在匹配，Full join 就會返回行

交叉連接一般使用的比較少，交叉連接又稱笛卡爾連接或者叉乘連接，如果，A和B是倆個集合，他們的交叉連接就是A*B?

文章題目：mysql交叉怎么連接 mysql交叉連接與內連接的聯系
分享路徑：http://vcdvsql.cn/article6/dopicog.html

成都網站建設公司_創新互聯，為您提供網站維護、網站內鏈、電子商務、網頁設計公司、自適應網站、微信小程序

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯