SQL 多個表之間聯合查詢

非常少用join，這次學學，并備忘兩篇文章！

連接配接查詢

通過連接配接運算符能夠實作多個表查詢。連接配接是關系資料庫模型的主要特點，也是它差别于其他類型資料庫管理系統的一個标志。

在關系資料庫管理系統中，表建立時各資料之間的關系不必确定，常把一個實體的全部資訊存放在一個表中。當檢索資料時，通過連接配接操作查詢出存放在多個表中的不同實體的資訊。連接配接操作給使用者帶來非常大的靈活性，他們能夠在不論什麼時候添加新的資料類型。為不同實體建立新的表，爾後通過連接配接進行查詢。

連接配接能夠在SELECT 語句的FROM子句或WHERE子句中建立，似是而非在FROM子句中指出連接配接時有助于将連接配接操作與WHERE子句中的搜尋條件區分開來。是以，在Transact-SQL中推薦使用這個方法。

SQL-92标準所定義的FROM子句的連接配接文法格式為：

FROM join_table join_type join_table

[ON (join_condition)]

當中join_table指出參與連接配接操作的表名，連接配接能夠對同一個表操作，也能夠對多表操作，對同一個表操作的連接配接又稱做自連接配接。

join_type 指出連接配接類型，可分為三種：内連接配接、外連接配接和交叉連接配接。内連接配接(INNER JOIN)使用比較運算符進行表間某(些)列資料的比較操作，并列出這些表中與連接配接條件相比對的資料行。依據所使用的比較方式不同，内連接配接又分為等值連接配接、自然連接配接和不等連接配接三種。

外連接配接分為左外連接配接(LEFT OUTER JOIN或LEFT JOIN)、右外連接配接(RIGHT OUTER JOIN或RIGHT JOIN)和全外連接配接(FULL OUTER JOIN或FULL JOIN)三種。與内連接配接不同的是，外連接配接不僅僅列出與連接配接條件相比對的行，而是列出左表(左外連接配接時)、右表(右外連接配接時)或兩個表(全外連接配接時)中全部符合搜尋條件的資料行。

交叉連接配接(CROSS JOIN)沒有WHERE 子句，它傳回連接配接表中全部資料行的笛卡爾積，其結果集合中的資料行數等于第一個表中符合查詢條件的資料行數乘以第二個表中符合查詢條件的資料行數。

連接配接操作中的ON (join_condition) 子句指出連接配接條件，它由被連接配接表中的列和比較運算符、邏輯運算符等構成。

不管哪種連接配接都不能對text、ntext和image資料類型列進行直接連接配接，但能夠對這三種列進行間接連接配接。比如：

SELECT p1.pub_id,p2.pub_id,p1.pr_info

FROM pub_info AS p1 INNER JOIN pub_info AS p2

ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)

(一)内連接配接

内連接配接查詢操作列出與連接配接條件比對的資料行，它使用比較運算符比較被連接配接列的列值。内連接配接分三種：

1、等值連接配接：在連接配接條件中使用等于号(=)運算符比較被連接配接列的列值，其查詢結果中列出被連接配接表中的全部列，包含當中的反複列。

2、不等連接配接： 在連接配接條件使用除等于運算符以外的其他比較運算符比較被連接配接的列的列值。這些運算符包含>、>=、<=、<、!>、!<和<>。

3、自然連接配接：在連接配接條件中使用等于(=)運算符比較被連接配接列的列值，但它使用選擇清單指出查詢結果集合中所包含的列，并删除連接配接表中的反複列。

例，以下使用等值連接配接列出authors和publishers表中位于同一城市的作者和出版社：

SELECT *

FROM authors AS a INNER JOIN publishers AS p

ON a.city=p.city

又如使用自然連接配接，在選擇清單中删除authors 和publishers 表中反複列(city和state)：

SELECT a.*,p.pub_id,p.pub_name,p.country

(二)外連接配接

内連接配接時，傳回查詢結果集合中的僅是符合查詢條件( WHERE 搜尋條件或 HAVING 條件)和連接配接條件的行。而採用外連接配接時，它傳回到查詢結果集合中的不僅包含符合連接配接條件的行，并且還包含左表(左外連接配接時)、右表(右外連接配接時)或兩個邊接表(全外連接配接)中的全部資料行。

如以下使用左外連接配接将論壇内容和作者資訊連接配接起來：

SELECT a.,b. FROM luntan LEFT JOIN usertable as b

ON a.username=b.username

以下使用全外連接配接将city表中的全部作者以及user表中的全部作者，以及他們所在的城市：

SELECT a.,b.

FROM city as a FULL OUTER JOIN user as b

(三)交叉連接配接

交叉連接配接不帶WHERE 子句，它傳回被連接配接的兩個表全部資料行的笛卡爾積，傳回到結果集合中的資料行數等于第一個表中符合查詢條件的資料行數乘以第二個表中符合查詢條件的資料行數。

例，titles表中有6類圖書，而publishers表中有8家出版社，則下列交叉連接配接檢索到的記錄數将等

于6*8=48行。

SELECT type,pub_name

FROM titles CROSS JOIN publishers

ORDER BY type

12 樓aw511（點點星燈）回複于 2006-08-22 09:13:42 得分 0 --查詢分析器中運作：

--建表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

idname|idscore|

1lee|190|

2zhang|2100|

以下均在查詢分析器中運作

一、外連接配接

1.概念：包含左向外聯接、右向外聯接或完整外部聯接

2.左連接配接：left join 或 left outer join

(1)左向外聯接的結果集包含 LEFT OUTER 子句中指定的左表的全部行，而不僅僅是聯接列所比對的行。假設左表的某行在右表中沒有比對行，則在相關聯的結果集行中右表的全部選擇清單列均為空值(null)。

(2)sql語句

select * from table1 left join table2 on table1.id=table2.id

-------------結果-------------

1lee190

2zhang2100

凝視：包含table1的全部子句，依據指定條件傳回table2對應的字段，不符合的以null顯示

3.右連接配接：right join 或 right outer join

(1)右向外聯接是左向外聯接的反向聯接。将傳回右表的全部行。假設右表的某行在左表中沒有比對行，則将為左表傳回空值。

select * from table1 right join table2 on table1.id=table2.id

凝視：包含table2的全部子句，依據指定條件傳回table1對應的字段，不符合的以null顯示

4.完整外部聯接:full join 或 full outer join

(1)完整外部聯接傳回左表和右表中的全部行。當某行在還有一個表中沒有比對行時，則還有一個表的選擇清單列包含空值。假設表之間有比對行，則整個結果集行包含基表的資料值。

select * from table1 full join table2 on table1.id=table2.id

4wangNULLNULL

凝視：傳回左右連接配接的和（見上左、右連接配接）

二、内連接配接

1.概念：内聯接是用比較運算符比較要聯接列的值的聯接

2.内連接配接：join 或 inner join

3.sql語句

select * from table1 join table2 on table1.id=table2.id

凝視：僅僅傳回符合條件的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...)

select * from table1 cross join table2

2zhang190

4wang190

1lee2100

4wang2100

1lee370

2zhang370

凝視：傳回3*3=9條記錄，即笛卡爾積

A:select * from table1,table2