SQL Join

記一下SQL語法 由網路上找來的資料
JOIN------------------------------------------------
先分兩大類Inner和Outter
Inner 只顯示匹配的行.
Outter 不論是否匹配,都顯示行 (所以可能會有null的東西)
------------------------------------------------
實際上有6種
Inner Join 類似好幾個where的用法 return all rows from both tables where there is a match
Natural Join 自然連接有 NATURAL JOIN、NATURAL LEFT JOIN、NATURAL RIGHT JOIN，兩個表格在進行 JOIN 時，加上 NATURAL 這個關鍵字之後，兩資料表之間同名的欄位會被自動結合在一起。
Left Outer Join 左邊顯示左表的東西,右邊顯示右表批配的,沒有顯示null
Right Outer Join 以上類推
Full Outer Join 上面兩個合體
Cross Join 乘積
------------------------------------------------
以上資料來自 http://www.wretch.cc/blog/sky4s/2250385
------------------------------------------------
接下來就是例子了 (出處http://webdesign.piipo.com/sql/sql_inner_join)
.................................................................
Innter Join語法 :
SELECT table_column1, table_column2···
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
or
SELECT table_column1, table_column2···
FROM table_name1
INNER JOIN table_name2
USING (column_name);
查詢實例
customers : C_Id Name City Address Phone
orders : O_Id Order_No C_Id
SELECT customers.Name, orders.Order_No
FROM customers
INNER JOIN orders
ON customers.C_Id=orders.C_Id;
此為查詢所有客戶定單的語法
..................................................................
Left Outter Join/Right Outter Join語法
SELECT table_column1, table_column2···FROM table_name1 LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
查詢實例
SELECT customers.Name, orders.Order_No FROM customers LEFT JOIN
ordersON customers.C_Id=orders.C_Id;
左表都會顯示,右表沒match到的部份會留null (Left)
右表都會顯示,左表沒match到的部份會留null (Right)
.....................................................................
Full Outter Join語法
SELECT table_column1, table_column2···
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
查詢實例
SELECT customers.Name, orders.Order_No FROM customers FULL JOIN
ordersON customers.C_Id=orders.C_Id;
左右表不管合不合都列,不合留null,算整合Left & Right的結果把
........................................................................
Left /Right/Full 三種用法近似,只差結果留null的方向
........................................................................
CROSS JOIN 語法
SELECT table_column1, table_column2···FROM table_name1 CROSS JOIN table_name2;
查詢實例
SELECT customers.Name, orders.Order_No FROM customers CROSS JOIN orders;
互相一一對應,產生乘積的結果
.........................................................................
NATURAL JOIN 語法
SELECT table_column1, table_column2···FROM table_name1
NATURAL JOIN table_name2;
查詢實例
SELECT customers.Name, orders.Order_No FROM customers
NATURAL JOIN orders;
欄位有批配的結果才列出

好了先寫到這邊,有機會在補充