SQL的JOIN用法1

關於sql語句中的串連（join）關鍵字，是較為常用而又不太容易理解的關鍵字，下面這個例子給出了一個簡單的解釋，相信會對你有所啟示。

--建表table1,table2：
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
如表
-------------------------------------------------
table1  | table2  |
-------------------------------------------------
id  name |id  score |
1  lee |1  90 |
2  zhang |2  100 |
4  wang |3  70 |
-------------------------------------------------

以下均在查詢分析器中執行

一、外串連
1.概念：包括左向外聯結、右向外聯結或完整外部聯結

2.左串連：left join 或 left outer join
(1)左向外聯結的結果集包括 LEFT OUTER 子句中指定的左表的所有行，而不僅僅是聯結列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯的結果集行中右表的所有挑選清單列均為空白值(null)。
(2)sql語句
select * from table1 left join table2 on table1.id=table2.id
-------------結果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
4 wang NULL NULL
------------------------------
注釋：包含table1的所有子句，根據指定條件返回table2相應的欄位，不符合的以null顯示

3.右串連：right join 或 right outer join
(1)右向外聯結是左向外聯結的反向聯結。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。
(2)sql語句
select * from table1 right join table2 on table1.id=table2.id
-------------結果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
NULL NULL 3 70
------------------------------
注釋：包含table2的所有子句，根據指定條件返回table1相應的欄位，不符合的以null顯示

4.完整外部聯結:full join 或 full outer join 
(1)完整外部聯結返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的挑選清單列包含空值。如果表之間有匹配行，則整個結果集行包含基表的資料值。
(2)sql語句
select * from table1 full join table2 on table1.id=table2.id
-------------結果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
4 wang NULL NULL
NULL NULL 3 70
------------------------------
注釋：返回左右串連的和（見上左、右串連）

二、內串連
1.概念：內聯結是用比較子比較要聯結列的值的聯結

2.內串連：join 或 inner join 

3.sql語句
select * from table1 join table2 on table1.id=table2.id
-------------結果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
------------------------------
注釋：只返回合格table1和table2的列

4.等價（與下列執行效果相同）
A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * from table1 cross join table2 where table1.id=table2.id  (註：cross join後加條件只能用where,不能用on)

三、交叉串連(完全)

1.概念：沒有 WHERE 子句的交叉聯結將產生聯結所涉及的表的笛卡爾積。第一個表的行數乘以第二個表的行數等於笛卡爾積結果集的大小。（table1和table2交叉串連產生3*3=9條記錄）

2.交叉串連：cross join (不帶條件where...)

3.sql語句
select * from table1 cross join table2
-------------結果-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 1 90
4 wang 1 90
1 lee 2 100
2 zhang 2 100
4 wang 2 100
1 lee 3 70
2 zhang 3 70
4 wang 3 70
------------------------------
注釋：返回3*3=9條記錄，即笛卡爾積

4.等價（與下列執行效果相同）
A:select * from table1,table2

1.
a. 並集UNION
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

b. 交集JOIN
SELECT * FROM table1 AS a JOIN table2 b ON a.name=b.name

c. 差集NOT IN
SELECT * FROM table1 WHERE name NOT IN(SELECT name FROM table2)

d. 笛卡爾積
SELECT * FROM table1 CROSS JOIN table2
與
SELECT * FROM table1,table2相同

2. SQL中的UNION
UNION與UNION ALL的區別是，前者會去除重複的條目，後者會仍舊保留。

a. UNION
SQL Statement1
UNION
SQL Statement2

b. UNION ALL
SQL Statement1
UNION ALL
SQL Statement2

3. SQL中的各種JOIN
SQL中的串連可以分為內串連，外串連，以及交叉串連

(即是笛卡爾積) 

a. 交叉串連CROSS JOIN
如果不帶WHERE條件子句，它將會返回被串連的兩個表的笛卡爾積，返回結果的行數等於兩個表行數的乘積；

舉例
SELECT * FROM table1 CROSS JOIN table2
等同於
SELECT * FROM table1,table2

一般不建議使用該方法，因為如果有WHERE子句的話，往往會先產生兩個表行數乘積的行的資料表然後才根據WHERE條件從中選擇。 
因此，如果兩個需要求交際的表太大，將會非常非常慢，不建議使用。

b. 內串連INNER JOIN
如果僅僅使用
SELECT * FROM table1 INNER JOIN table2
沒有指定串連條件的話，和交叉串連的結果一樣。

但是通常情況下，使用INNER JOIN需要指定串連條件。
-- 等值串連(=號應用於串連條件, 不會去除重複的列)
SELECT * FROM table1 AS a INNER JOIN table2 AS b on a.column=b.column
-- 不等串連(>,>=,<,<=,!>,!<,<>)
例如
SELECT * FROM table1 AS a INNER JOIN table2 AS b on a.column<>b.column
-- 自然串連(會去除重複的列)

c. 外串連OUTER JOIN
首先內串連和外串連的不同之處： 
內串連如果沒有指定串連條件的話，和笛卡爾積的交叉串連結果一樣，但是不同於笛卡爾積的地方是，沒有笛卡爾積那麼複雜要先產生行數乘積的資料表，內串連的效率要高於笛卡爾積的交叉串連。

指定條件的內串連，僅僅返回符合串連條件的條目。
外串連則不同，返回的結果不僅包含符合串連條件的行，而且包括左表(左外串連時), 右表(右串連時)或者兩邊串連(全外串連時)的所有資料行。

1)左外串連LEFT [OUTER] JOIN 
顯示合格資料行，同時顯示左邊資料表不合格資料行，右邊沒有對應的條目顯示NULL
例如
SELECT * FROM table1 AS a LEFT [OUTER] JOIN ON a.column=b.column
2)右外串連RIGHT [OUTER] JOIN
顯示合格資料行，同時顯示右邊資料表不合格資料行，左邊沒有對應的條目顯示NULL
例如
SELECT * FROM table1 AS a RIGHT [OUTER] JOIN ON a.column=b.column
3)全外串連
顯示合格資料行，同時顯示左右不合格資料行，相應的左右兩邊顯示NULL