連表查詢
連接查詢包括合并、內(nèi)連接下面、外連接和交叉連接复颈，如果涉及多表查詢，了解這些連接的特點(diǎn)很重要沥割。
只有真正了解它們之間的區(qū)別耗啦，才能正確使用。
1机杜、Union
UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語(yǔ)句的結(jié)果集帜讲。
UNION 運(yùn)算符通過(guò)組合其他兩個(gè)結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復(fù)行而派生出一個(gè)結(jié)果表。
當(dāng) ALL 隨 UNION 一起使用時(shí)（即 UNION ALL）椒拗，不消除重復(fù)行似将。兩種情況下，派生表的每一行不是來(lái)自 TABLE1 就是來(lái)自 TABLE2蚀苛。
注意：使用UNION時(shí)在验，兩張表查詢的結(jié)果有相同數(shù)量的列、列類型相似堵未。
1）基本UNION查詢腋舌，查詢學(xué)校教師、學(xué)生的總的信息表渗蟹，包括ID和姓名
SELECT ID,Name FROM Students
UNION
SELECT ID,Name FROM Teachers
2)帶條件的UNION查詢块饺，也可以查詢同一張表，查詢年齡為18拙徽，23歲的學(xué)生信息
SELECT ID,Name FROM Student WHERE Age=18
UNION
SELECT ID,Name FROM Student WHERE Age=23
當(dāng)然刨沦，這可以使用IN或者OR很容易實(shí)現(xiàn)，這里只是點(diǎn)到膘怕，以后遇到復(fù)雜查詢想诅，相信你會(huì)用到。
3）查詢教師學(xué)生全部姓名
因?yàn)閁NION只會(huì)選擇不同的值，如果學(xué)生中和教師中有重名的情況来破，這就需要UNION ALL
SELECT Name FROM Students
UNION ALL
SELECT Name FROM Teachers
2篮灼、INNER JOIN（內(nèi)連接）
INNER JOIN（內(nèi)連接），也成為自然連接
作用：根據(jù)兩個(gè)或多個(gè)表中的列之間的關(guān)系徘禁，從這些表中查詢數(shù)據(jù)诅诱。
注意: 內(nèi)連接是從結(jié)果中刪除其他被連接表中沒(méi)有匹配行的所有行，所以內(nèi)連接可能會(huì)丟失信息送朱。
重點(diǎn)：內(nèi)連接娘荡，只查匹配行。
語(yǔ)法：(INNER可省略)
SELECT fieldlist FROM table1 [INNER] join table2 ON table1.column=table2.column
實(shí)例：查詢學(xué)生信息驶沼，包括ID,姓名炮沐、專業(yè)名稱
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students INNER JOIN MajorsON Students.MajorID = Majors.ID
結(jié)果：
根據(jù)結(jié)果可以清晰看到，確實(shí)只有匹配的行回怜。學(xué)生Lucy的信息丟失了大年。
但是，inner join也會(huì)產(chǎn)生重復(fù)數(shù)據(jù)玉雾。如果將Majors表的主鍵約束去掉翔试，可以插入重復(fù)的ID，如：
DELETE FROM Majors
INSERT INTO Majors(ID,Name) VALUES(10,'English')
INSERT INTO Majors(ID,Name) VALUES(10,'Computer')
繼續(xù)執(zhí)行上面的關(guān)聯(lián)語(yǔ)句复旬，結(jié)果為：
RIGHT JOIN 結(jié)果與INNER JOIN一樣垦缅。
后續(xù)我們會(huì)深入研究JOIN的具體原理。
3赢底、外連接
與內(nèi)連接相比失都，即使沒(méi)有匹配行，也會(huì)返回一個(gè)表的全集幸冻。
外連接分為三種：左外連接粹庞，右外連接，全外連接洽损。對(duì)應(yīng)SQL：LEFT/RIGHT/FULL OUTER JOIN庞溜。通常我們省略outer 這個(gè)關(guān)鍵字。寫(xiě)成：LEFT/RIGHT/FULL JOIN碑定。
重點(diǎn)：至少有一方保留全集流码，沒(méi)有匹配行用NULL代替。
1）LEFT OUTER JOIN延刘，簡(jiǎn)稱LEFT JOIN漫试，左外連接（左連接）
結(jié)果集保留左表的所有行，但只包含第二個(gè)表與第一表匹配的行碘赖。第二個(gè)表相應(yīng)的空行被放入NULL值驾荣。
依然沿用內(nèi)鏈接的例子
（1）使用左連接查詢學(xué)生的信息外构，其中包括學(xué)生ID，學(xué)生姓名和專業(yè)名稱播掷。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN MajorsON Students.MajorID = Majors.ID
** 結(jié)論：**
通過(guò)結(jié)果审编，我們可以看到左連接包含了第一張表的所有信息，在第二張表中如果沒(méi)有匹配項(xiàng)歧匈，則用NULL代替垒酬。
2）RIGHT JOIN(right outer join)右外連接(右連接)
右外連接保留了第二個(gè)表的所有行，但只包含第一個(gè)表與第二個(gè)表匹配的行件炉。第一個(gè)表相應(yīng)空行被入NULL值勘究。
右連接與左連接思想類似。只是第二張保留全集妻率，如果第一張表中沒(méi)有匹配項(xiàng)乱顾，用NULL代替
依然沿用內(nèi)鏈接的例子，只是改為右連接
（1）使用右連接查詢學(xué)生的信息宫静，其中包括學(xué)生ID，學(xué)生姓名和專業(yè)名稱券时。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students RIGHT JOIN MajorsON Students.MajorID = Majors.ID
通過(guò)結(jié)果可以看到孤里，包含了第二張表Majors的全集愉择，Computer在Students表中沒(méi)有匹配項(xiàng)免猾，就用NULL代替。
3）FULL JOIN (FULL OUTER JOIN貌夕，全外連接）
全外連接炸枣，簡(jiǎn)稱：全連接虏等。會(huì)把兩個(gè)表所有的行都顯示在結(jié)果表中
1)使用全連接查詢學(xué)生的信息，其中包括學(xué)生ID适肠，學(xué)生姓名和專業(yè)名稱霍衫。
SELECT Students.ID,Students.Name,Majors.Name AS MajorNameFROM Students FULL JOIN MajorsON Students.MajorID = Majors.ID
包含了兩張表的所有記錄，沒(méi)有記錄丟失侯养，沒(méi)有匹配的行用NULL代替敦跌。
4、CROSS JOIN（交叉連接）
交叉連接逛揩。交叉連接返回左表中的所有行柠傍，左表中的每一行與右表中的所有行組合。交叉連接也稱作笛卡爾積辩稽。
簡(jiǎn)單查詢兩張表組合惧笛，這是求笛卡兒積，效率最低逞泄。
笛卡兒積：笛卡爾乘積患整，也叫直積静檬。假設(shè)集合A={a,b}，集合B={0,1,2}并级，則兩個(gè)集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}拂檩。可以擴(kuò)展到多個(gè)集合的情況嘲碧。類似的例子有稻励，如果A表示某學(xué)校學(xué)生的集合，B表示該學(xué)校所有課程的集合愈涩，則A與B的笛卡爾積表示所有可能的選課情況望抽。
1）交叉連接查詢學(xué)生的信息，其中包括學(xué)生ID履婉，學(xué)生姓名和專業(yè)名稱煤篙。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
2）查詢多表，其實(shí)也是笛卡兒積毁腿，與CROSS JOIN等價(jià)辑奈，以下查詢同上述結(jié)果一樣。
這個(gè)可能很常見(jiàn)已烤，但是大家一定要注意了鸠窗，這樣就查詢了兩張表中所有組合的全集。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students,Majors
3）加了查詢條件
注意:在使用CROSS JOIN關(guān)鍵字交叉連接表時(shí)胯究，因?yàn)樯傻氖莾蓚€(gè)表的笛卡爾積稍计，因而不能使用ON關(guān)鍵字，只能在WHERE子句中定義搜索條件裕循。
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
WHERE Students.MajorID = Majors.ID
查詢結(jié)果與INNER JOIN一樣臣嚣，但是其效率就慢很多了。
5.全部示例腳本
CREATE DATABASE TestDB
USE TestDB
------------------------------------------
--創(chuàng)建相關(guān)表
IF OBJECT_ID('Students','U') IS NOT NULL
DROP TABLE Students
--學(xué)生信息表
CREATE TABLE Students(
ID int primary key not null,
Name nvarchar(50),
Age int,
City nvarchar(50),
MajorID int
)
--專業(yè)信息表
IF OBJECT_ID('Majors','U') IS NOT NULL
DROP TABLE Majors
CREATE TABLE Majors(
ID int primary key not null,
Name nvarchar(50)
)
--教師信息表
IF OBJECT_ID('Teachers','U') IS NOT NULL
DROP TABLE Teachers
CREATE TABLE Teachers(
ID int primary key not null,
Name nvarchar(20) not null
)
--預(yù)置數(shù)據(jù)
DELETE FROM Students
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(101,'Tom',20,'BeiJing',10)
INSERT INTO Students(ID,Name,Age,City,MajorID) VALUES(102,'Lucy',18,'ShangHai',11)
DELETE FROM Majors
INSERT INTO Majors(ID,Name) VALUES(10,'English')
INSERT INTO Majors(ID,Name) VALUES(12,'Computer')
DELETE FROM Teachers
INSERT INTO Teachers(ID,Name) VALUES(101,'Mrs Lee')
INSERT INTO Teachers(ID,Name) VALUES(102,'Lucy')
SELECT ID,Name FROM Students
UNION
SELECT ID,Name FROM Teachers
SELECT ID,Name FROM Students
UNION ALL
SELECT ID,Name FROM Teachers
--內(nèi)連接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students INNER JOIN Majors
ON Students.MajorID = Majors.ID
--左連接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID
--右連接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID
--全連接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students FULL JOIN Majors
ON Students.MajorID = Majors.ID
--交叉連接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
--交叉連接
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students CROSS JOIN Majors
WHERE Students.MajorID = Majors.ID
--一次查詢多表
SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students,Majors
@ 晴-2017-04-21 18:07:25
