SQL join，sqljoin

SQL join，sqljoin

============================================================================= 

1.理論知識點

             SQL join

        SQL join用於根據兩個或多個表中的列之間的關係，從這些表中查詢資料。有時為了得到完整的結果，我們需要從兩個或更多的表中擷取結果。我們就需要執行 join。資料庫中的表可通過鍵將彼此聯絡起來。主鍵（Primary Key）是一個列，在這個列中的每一行的值都是唯一的。在表中，每個主鍵的值都是唯一的。這樣做的目的是在不重複每個表中的所有資料的情況下，把表間的資料交叉捆綁在一起。

        串連查詢是關聯式資料庫中最主要的查詢，主要包括內串連、外串連和交叉串連等。通過串連運算子可以實現多個表查詢。串連是關聯式資料庫模型的主要特點，也是它區別於其它類型資料庫管理系統的一個標誌。 在關聯式資料庫管理系統中，表建立時各資料之間的關係不必確定，常把一個實體的所有資訊存放在一個表中。當檢索資料時，通過串連操作查詢出存放在多個表中的不同實體的資訊。串連操作給使用者帶來很大的靈活性，他們可以在任何時候增加新的資料類型。為不同實體建立新的表，然後通過串連進行查詢。
不同的 SQL JOIN

JOIN 類型，以及它們之間的差異:

• [ INNER] JOIN:內串連,如果表中有至少一個匹配，則返回行
  
• LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行
• RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行
• FULL JOIN: 只要其中一個表中存在匹配，就返回行

交叉串連交叉串連即笛卡兒乘積，是指兩個關係中所有元組的任意組合。一般情況下，交叉查詢是沒有實際意義的。例如：如果希望得到學生表和選課表兩個關係模式的乘積，查詢語句為SELECT*FROM學生表CROSS JOIN選課表
內串連 內串連是一種最常用的連線類型。內串連查詢實際上是一種任意條件的查詢。使用內串連時，如果兩個表的相關欄位滿足串連條件，就從這兩個表中提取資料並組合成新的記錄，也就是在內串連查詢中，只有滿足條件的元組才能出現在結果關係中。例如：要查詢每個已經選課的學生的情況，查詢語句為SELECT*FROM學生表INNER JOIN選課表ON學生表.學號=選課表.學號根據比較方式分為： 1）等值串連：在串連條件中使用等號(=)運算子比較被串連列的列值，其查詢結果中列出被串連表中的所有列，包括其中的重複列。 2）不等串連：在串連條件使用除等於運算子以外的其它比較子比較被串連的列的列值。這些運算子包括>、>=、<=、<、!>、!<和<>。 3）自然串連：在串連條件中使用等於(=)運算子比較被串連列的列值，但它使用挑選清單指出查詢結果集合中所包括的列，並刪除串連表中的重複列。
自串連如果在一個串連查詢中，涉及到的兩個表都是同一個表，這種查詢就稱為自串連查詢。同一張表在FROM字句中多次出現，為了區別該表的每一次出現，需要為表定義一個別名。自串連是一種特殊的內串連，它是指相互串連的表在物理上為同一張表，但可以在邏輯上分為兩張表。例如：要求檢索出學號為20210的學生的同班同學的資訊，查詢語句為SELECT學生表.*FROM學生表JOIN學生表AS學生表1ON學生表.班級=學生表1.班級WHERE學生表1.學號='20210'
外串連內串連的查詢結果都是滿足串連條件的元組。但有時我們也希望輸出那些不滿足串連條件的元組資訊。比如，我們想知道每個學生的選課情況，包括已經選課的學生（這部分學生的學號在學生表中有，在選課表中也有，是滿足串連條件的），也包括沒有選課的學生（這部分學生的學號在學生表中有，但在選課表中沒有，不滿足串連條件），這時就需要使用外串連。外串連是只限制一張表中的資料必須滿足串連條件，而另一張表中的資料可以不滿足串連條件的串連方式。3種外串連： 1)左外串連（LEFTOUTER JOIN）如果在串連查詢中，串連管子左端的表中所有的元組都列出來，並且能在右端的表中找到匹配的元組，那麼串連成功。如果在右端的表中，沒能找到匹配的元組，那麼對應的元組是空值（NULL）。這時，查詢語句使用關鍵字LEFT OUTERJOIN，也就是說，左外串連的含義是限制串連關鍵字右端的表中的資料必須滿足串連條件，而不關左端的表中的資料是否滿足串連條件，均輸出左端表中的內容。例如：要查詢所有學生的選課情況，包括已經選課的和還沒有選課的學生，查詢語句為SELECT學生表.學號，姓名，班級，課程號，成績FROM學生表LEFT OUTER JOIN選課表ON學生表.學號=選課表.學號左外串連查詢中左端表中的所有元組的資訊都得到了保留。 2）右外串連（RIGHTOUTERJOIN）右外串連與左外串連類似，只是右端表中的所有元組都列出，限制左端表的資料必須滿足串連條件，而不管右端表中的資料是否滿足串連條件，均輸出表中的內容。例如：同上例內容，查詢語句為SELECT學生表.學號，姓名，班級，課程號，成績FROM學生表RIGHTOUTERJOIN選課表ON學生表.學號=選課表.學號右外串連查詢中右端表中的所有元組的資訊都得到了保留。 3）全外串連（FULL OUTER JOIN）全外串連查詢的特點是左、右兩端表中的元組都輸出，如果沒能找到匹配的元組，就使用NULL來代替。例如：同左外串連例子內容，查詢語句為SELECT學生表.學號，姓名，班級，課程號，成績FROM學生表FULL OUTER JOIN選課表ON學生表.學號=選課表.學號全外串連查詢中所有表中的元組資訊都得到了保留。
===================================================================
2.執行個體資料庫的搭建

"Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Id_P" 列是 Persons 表中的的主鍵。

 "Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

"Id_O" 列是 Orders 表中的的主鍵，同時，"Orders" 表中的 "Id_P" 列用於引用 "Persons" 表中的人，而無需使用他們的確切姓名。"Id_P" 列把上面的兩個表聯絡了起來。

建表語句：

CREATE TABLE Persons(Id_P int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255));

ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P);

create table Orders(    Id_O int,    OrderNo varchar(255),    Id_P int);

ALTER TABLE Orders ADD CONSTRAINT pk_OrderID PRIMARY KEY (Id_O);

#添加外鍵約束

ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (Id_P)REFERENCES Persons(Id_P);
如需撤銷 FOREIGN KEY 約束，請使用下面的 SQL：
MySQL:
ALTER TABLE OrdersDROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders
插入資料：
insert into Persons values  (1,'Adams','John','Oxford Street','London' ); 

INSERT INTO Persons VALUES (3,'Bush', 'George', 'Fifth Avenue', 'New York');
INSERT INTO Persons VALUES (2,'Gates', 'Bill', 'Changan Street', 'Beijing');

insert into orders values(1,'20150730100',2);

insert into orders values(2,'20150730100',3);
insert into orders values(3,'20150730100',2);
insert into orders values(4,'20150730100',3);

insert into orders values(5,'20150730100',3);

insert into orders values(6,'20150730100',1);

===================================================================

3.SQL join執行個體

我們可以通過引用兩個表的方式，從兩個表中擷取資料：誰訂購了產品，並且他們訂購了什麼產品？

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM Persons, OrdersWHERE Persons.Id_P = Orders.Id_P 

也可以使用關鍵詞 JOIN 來從兩個表中擷取資料。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsINNER JOIN OrdersON Persons.Id_P = Orders.Id_PORDER BY Persons.LastName

結果集：

LastName	FirstName	OrderNo
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678

--------------------------------------------------------------------------------------------------------------------------------------------------------

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。