資料庫中left join,right join,inner join的差異 ...
資料庫中left join,right join,inner join的差異
具體詳細說明
總的來說：
- JOIN: 有匹配的就返回。
- LEFT JOIN: 右表中沒有，沒關係，我左表的全部返回。
- RIGHT JOIN: 左表中沒有就空著，右表的全部返回。
- FULL JOIN: 倆表中有一個就返回。
常用的來說，inner join，left join，其中inner join 經常省略了。
1、內連接(等值連接)
將兩個表中存在連結關係的欄位符合連接條件的記錄形成記錄集。只返回兩個表中聯結欄位相等的行
Select A.name,B.name from A inner join B on A.id=B.id和
Select A.name,B.name from A,B where A.id=B.id結果是一樣的（內連接的inner關鍵字可省略）；
在表中存在至少一個匹配時，INNER JOIN 關鍵字返回行。
INNER JOIN 關鍵字語法
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
2、外連接：分為左外連接和右外連接
left join左連接A、B表結果包括A的全部記錄和符合條件的B的記錄。返回包括左表中的所有記錄和右表中聯結欄位相等的記錄。
Select A.name,B.name from A Left Join B on A.id=B.id
其中LEFT JOIN 關鍵字會從左表 (table_name1) 那裡返回所有的行，即使在右表 (table_name2) 中沒有匹配的行。
LEFT JOIN 關鍵字語法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
連接通常可以在select語句的from子句或where子句中建立，其語法格式為：
select colunm_name1,colunm_name2
from table_name1
left join table_name2
on table_name1.colunmname=table_name2.colunmname
其中join_table指出參與連接操作的表名，連接可以對同一個表操作，也可以對多表操作，對同一個表操作的連接稱為自連接， join_type 為連接類型，可以是left join 或者right join 或者inner join 。
left join與where共同使用
select a.*,b.*
from table1 a
left join table2 b on b.X=a.X
where XXX
如上：一旦使用了left join，沒有where條件時，左表table1會顯示全部內容。使用了where，只有滿足where條件的記錄才會顯示（左表顯示部分或者全部不顯示）
so。。。。
left join的困惑：一旦加上where條件，則顯示的結果等於inner join
原因分析：
資料庫在通過連接兩張或多張表來返回記錄時，都會生成一張中間的臨時表，然後再將這張臨時表返回給用戶；
where條件是在臨時表生成好後，再對臨時表進行過濾的條件；
因此：where 條件加上，已經沒有left join的含義（必須返回左邊表的記錄）了，條件不為真的就全部過濾掉。
解決方案：
1、where過濾結果作為子查詢，和主表left，如下：
select a.*,tmp.*from table1 aleft join(
select a.*,b.*
from table1 a
left join table2 b on b.X=a.X
where XXX
)tmp
很明顯，子查詢語句無論 left join、inner join都沒啥區別了
2、查詢條件放在on後面
select a.*,b.*
from table1 a
left join table2 b
on b.X=a.X and XXX
註意：where XXX去掉，改為鏈接條件on後面的 and XXX
分析：on條件是在生成臨時表時使用的條件，它不管on中的條件是否為真，都會返回左邊表中的記錄。
結論：過濾條件放在：
where後面：是先連接然生成臨時查詢結果，然後再篩選。
on後面：先根據條件過濾篩選，再連 生成臨時查詢結果。
對於left join，不管on後面跟什麼條件，左表的數據全部查出來，因此要想過濾需把條件放到where後面
對於inner join，滿足on後面的條件表的數據才能查出，可以起到過濾作用。也可以把條件放到where後面。
on、where、having的區別
on、where、having這三個都可以加條件的子句中，on是最先執行，where次之，having最後。有時候如果這先後順序不影響中間結果的話，那最終結果是相同的。
但因為on是先把不符合條件的記錄過濾後才進行統計，它就可以減少中間運算要處理的數據，按理說應該速度是最快的。
在兩個表聯接時才用on的，所以在一個表的時候，就剩下where跟having比較了。在這單表查詢統計的情況下，如果要過濾的條件沒有涉及到要計算欄位，那它們的結果是一樣的。
如果要涉及到計算的欄位，就表示在沒計算之前，這個欄位的值是不確定的，根據上篇寫的工作流程，where的作用時間是在計算之前就完成的，而having就是在計算後才起作用的，所以在這種情況下，兩者的結果會不同。
在多表聯接查詢時，on比where更早起作用。系統首先根據各個表之間的聯接條件，把多個表合成一個臨時表後，再由where進行過濾，然後再計算，計算完後再由having進行過濾。
由此可見，要想過濾條件起到正確的作用，首先要明白這個條件應該在什麼時候起作用，然後再決定放在那裡
標準的模板：
select * from (
select 表A.*,表B.*
from 表A left join 表B on 表A.x=表B.x )
where 表A.y<>表B.y or 表B.x is null;
left join是以A表的記錄為基礎的,A可以看成左表,B可以看成右表,left join是以左表為準的。
換句話說,左表(A)的記錄將會全部表示出來,而右表(B)只會顯示符合搜索條件的記錄(例子中為: A.aID = B.bID)。B表記錄不足的地方均為NULL。
right join右連接A、B表的結果和左連接B、A的結果是一樣的，返回包括右表中的所有記錄和左表中聯結欄位相等的記錄。也就是說：
Select A.name,B.name from B Right Join A on B.id-A.id執行後的結果是一樣的。
RIGHT JOIN 關鍵字會右表 (table_name2) 那裡返回所有的行，即使在左表 (table_name1) 中沒有匹配的行。
RIGHT JOIN 關鍵字語法
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
一個誤區：
想查詢B表中的所有type為1的數據，A表中查出一些欄位加入到B結果中。
比較下下麵兩句SQL
1.select * from A right join B on B.type=1 where A.id=B.aid;
2.select * from B left join A on A.id=B.aid where B.type=1 ;
我原先是按第一句寫的，結果出來時，我感到很奇怪。為什麼right join，不能把右邊表裡type為1的數據全部獲取出來。
第一句，先執行on部分，查出了所有B的數據，然後和A進行右連接，最後根據條件A.id=B.aid，篩選數據，這樣如果不滿足A.id=B.aid的數據，將會從結果中去除，包括B的數據！
第二句，先執行on部分，查出A中所有滿足A.id=B.aid的數據，再進行左連接，最後根據type=1做篩選。
可見，第二句sql才是正確的方法。
3，全連接Full join
只要其中某個表存在匹配，FULL JOIN 關鍵字就會返回行。
FULL JOIN 關鍵字語法
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
下麵列出了這幾個可以使用的 JOIN 類型，以及它們之間的差異。
- JOIN: 如果表中有至少一個匹配，則返回行
- LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行
- RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行
- FULL JOIN: 只要其中一個表中存在匹配，就返回行
多餘：
三表聯結查詢
select username,psw,gname,tel from (t1 left join t2 on t1.t1_id=t2.t1_id) left join t3 on t1.t1_id=t3.t1_id
三個表信息：
tems：商品表，item_visit_stats：商品訪問表，item_trade_stats：商品銷售表
SELECT i.num_iid, i.title, i.price, SUM(iv.user_visits) AS uv,it.buyer_num,it.item_num,it.item_num*i.price AS turnover
FROM (
items AS i RIGHT JOIN item_visit_stats AS iv ON i.num_iid=iv.num_iid)
LEFT JOIN (
SELECT num_iid,SUM(buyer_num) AS buyer_num,SUM(item_num) AS item_num FROM item_trade_stats
WHERE seller_nick="XXXX" AND business_day BETWEEN '2017-08-14' AND '2017-08-15' GROUP BY num_iid)
AS it ON it.num_iid=iv.num_iid
WHERE i.nick="XXXX" AND iv.business_day BETWEEN '2017-08-14' AND '2017-08-15'
GROUP BY i.num_iid ORDER BY uv DESC
