資料庫多表查詢總結（搬運）

標籤：rom   int   重複資料   ali   關聯   pad   tab   效率   div   

1、Union

UNION 操作符用於合并兩個或多個 SELECT 語句的結果集。

UNION 運算子通過組合其他兩個結果表（例如 TABLE1 和 TABLE2）並消去表中任何重複行而派生出一個結果表。

當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重複行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。

注意：使用UNION時，兩張表查詢的結果有相同數量的列、列類型相似。

學生表資訊（Students）：

ID	Name	Age	City	MajorID
101	Tom	20	BeiJing	10
102	Lucy	18	ShangHai	11

 

 

 

教師表資訊（Teachers）：

ID	Name
101	Mrs Lee
102	Lucy

 

 

 

預置指令碼：

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)INSERT INTO Teachers(ID,Name) VALUES(101,‘Mrs Lee‘)INSERT INTO Teachers(ID,Name) VALUES(102,‘Lucy‘)

 

1）基本UNION查詢，查詢學校教師、學生的總的資訊表，包括ID和姓名

SELECT ID,Name FROM StudentsUNIONSELECT ID,Name FROM Teachers

查詢結果：

ID	Name
101	Mrs Lee
101	Tom
102	Lucy

 

 

 

 

2)帶條件的UNION查詢，也可以查詢同一張表，查詢年齡為18，23歲的學生資訊

SELECT ID,Name FROM Student WHERE Age=18UNIONSELECT ID,Name FROM Student WHERE Age=23

當然，這可以使用IN或者OR很容易實現，這裡只是點到，以後遇到複雜查詢，相信你會用到。

3）查詢教師學生全部姓名

 因為UNION只會選擇不同的值，如果學生中和教師中有重名的情況，這就需要UNION ALL

SELECT Name FROM StudentsUNION ALLSELECT Name FROM Teachers

查詢結果：

ID	Name
101	Tom
102	Lucy
101	Mrs Lee
102	Lucy

 

 

 

  

 2、INNER JOIN（內串連）

INNER JOIN（內串連），也成為自然串連

作用：根據兩個或多個表中的列之間的關係，從這些表中查詢資料。

注意: 內串連是從結果中刪除其他被串連表中沒有匹配行的所有行，所以內串連可能會丟失資訊。

重點：內串連，只查匹配行。

文法：(INNER可省略)

SELECT fieldlistFROM table1 [INNER] join table2ON table1.column=table2.column

學生表資訊（Students）：

ID	Name	Age	City	MajorID
101	Tom	20	BeiJing	10
102	Lucy	18	ShangHai	11

 

 

 

專業資訊表（Majors）： 

ID	Name
10	English
12	Computer

 

 

 

 預置指令碼：

DELETE FROM StudentsINSERT 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 MajorsINSERT INTO Majors(ID,Name) VALUES(10,‘English‘)INSERT INTO Majors(ID,Name) VALUES(12,‘Computer‘)

 

執行個體：查詢學生資訊，包括ID,姓名、專業名稱

SELECT Students.ID,Students.Name,Majors.Name AS MajorNameFROM Students INNER JOIN MajorsON Students.MajorID = Majors.ID

 查詢結果：

ID	Name	MajorName
101	Tom	English

 

 

根據結果可以清晰看到，確實只有匹配的行。學生Lucy的資訊丟失了。

 

但是，inner join也會產生重複資料。如果將Majors表的主鍵約束去掉，可以插入重複的ID，如：

DELETE FROM MajorsINSERT INTO Majors(ID,Name) VALUES(10,‘English‘)INSERT INTO Majors(ID,Name) VALUES(10,‘Computer‘)

繼續執行上面的關聯語句，結果為：

ID	Name	MajorName
101	Tom	English
101	Tom	Computer

 

 

 

如果是LEFT JOIN也會有重複記錄，其結果為：

ID	Name	MajorName
101	Tom	English
101	Tom	Computer
102	Lucy	NULL

 

 

 

RIGHT JOIN 結果與INNER JOIN一樣。

後續我們會深入研究JOIN的具體原理。

3、外串連

與內串連相比，即使沒有匹配行，也會返回一個表的全集。

外串連分為三種：左外串連，右外串連，全外串連。對應SQL：LEFT/RIGHT/FULL OUTER JOIN。通常我們省略outer 這個關鍵字。寫成：LEFT/RIGHT/FULL JOIN。

重點：至少有一方保留全集，沒有匹配行用NULL代替。

1）LEFT OUTER JOIN，簡稱LEFT JOIN，左外串連（左串連）

結果集保留左表的所有行，但只包含第二個表與第一表匹配的行。第二個表相應的空行被放入NULL值。

依然沿用內連結的例子

（1）使用左串連查詢學生的資訊，其中包括學生ID，學生姓名和專業名稱。

SELECT Students.ID,Students.Name,Majors.Name AS MajorNameFROM Students LEFT JOIN MajorsON Students.MajorID = Majors.ID

 

結果：

ID	Name	MajorName
101	Tom	English
102	Lucy	NULL

 

 

 

 

 

 結論：

通過結果，我們可以看到左串連包含了第一張表的所有資訊，在第二張表中如果沒有匹配項，則用NULL代替。 

 

2）RIGHT JOIN(right outer join)右外串連(右串連)

右外串連保留了第二個表的所有行，但只包含第一個表與第二個表匹配的行。第一個表相應空行被入NULL值。

右串連與左串連思想類似。只是第二張保留全集，如果第一張表中沒有匹配項，用NULL代替

依然沿用內連結的例子，只是改為右串連

（1）使用右串連查詢學生的資訊，其中包括學生ID，學生姓名和專業名稱。

SELECT Students.ID,Students.Name,Majors.Name AS MajorNameFROM Students RIGHT JOIN MajorsON Students.MajorID = Majors.ID

查詢結果：

ID	Name	MajorName
101	Tom	English
NULL	NULL	Computer

 

 

 

 

 

 

通過結果可以看到，包含了第二張表Majors的全集，Computer在Students表中沒有匹配項，就用NULL代替。 

3）FULL JOIN (FULL OUTER JOIN，全外串連）

全外串連，簡稱：全串連。會把兩個表所有的行都顯示在結果表中

1)使用全串連查詢學生的資訊，其中包括學生ID，學生姓名和專業名稱。

SELECT Students.ID,Students.Name,Majors.Name AS MajorNameFROM Students FULL JOIN MajorsON Students.MajorID = Majors.ID

查詢結果： 

ID	Name	MajorName
101	Tom	English
102	Lucy	NULL
NULL	NULL	Computer

 

 

 

 

 

 

 

 

包含了兩張表的所有記錄，沒有記錄丟失，沒有匹配的行用NULL代替。

4、CROSS JOIN（交叉串連）

交叉串連。交叉串連返回左表中的所有行，左表中的每一行與右表中的所有行組合。交叉串連也稱作笛卡爾積。 

簡單查詢兩張表組合，這是求笛卡兒積，效率最低。

笛卡兒積：笛卡爾乘積，也叫直積。假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以擴充到多個集合的情況。類似的例子有，如果A表示某學校學生的集合，B表示該學校所有課程的集合，則A與B的笛卡爾積表示所有可能的選課情況。

1）交叉串連查詢學生的資訊，其中包括學生ID，學生姓名和專業名稱。

SELECT Students.ID,Students.Name,Majors.Name AS MajorNameFROM Students CROSS JOIN Majors

查詢結果：

ID	Name	MajorName
101	Tom	English
102	Lucy	English
101	Tom	Computer
102	Lucy	Computer

 

 

 

 

 

 

 

 

 

 

2）查詢多表，其實也是笛卡兒積，與CROSS JOIN等價，以下查詢同上述結果一樣。

這個可能很常見，但是大家一定要注意了，這樣就查詢了兩張表中所有組合的全集。

SELECT Students.ID,Students.Name,Majors.Name AS MajorNameFROM Students,Majors

3）加了查詢條件

注意:在使用CROSS JOIN關鍵字交叉串連表時，因為產生的是兩個表的笛卡爾積，因而不能使用ON關鍵字，只能在WHERE子句中定義搜尋條件。

SELECT Students.ID,Students.Name,Majors.Name AS MajorNameFROM Students CROSS JOIN MajorsWHERE Students.MajorID = Majors.ID

查詢結果：

ID	Name	MajorName
101	Tom	English

 

 

 

 

查詢結果與INNER JOIN一樣，但是其效率就慢很多了。

 

資料庫多表查詢總結（搬運）