JOIN IN T-SQL

• inner join
• full join
• left join
• right join
• cross join

Data mẫu để minh họa

Production

product_id	name	price
p1	cpu	100
p2	ram	20
p3	fan	10
p4	mainboard	80

Transaction

transaction_id	product_id	amount
t1	p1	1
t2	p2	2
t3	p3	3
t4	p1	4
t5	p5	5

Inner join syntax

SELECT column_name(s)
FROM <table1> tb1 INNER JOIN <table2> tb2 
                  on tb1.<columns1> = tb2.<columns2> 

:bulb: Hint: Dùng để chọn ra những dòng có điểm chung dựa vào điều kiện join của 2 bảng

ví dụ

SELECT *
FROM Transaction T inner join Production P
                 on T.product_id = P.product_id 

Thông thường trong Sql server người ta chỉ viết JOIN là INNER JOIN

Left join syntax

SELECT column_name(s)
FROM <table1> tb1 LEFT JOIN <table2> tb2 
                  on tb1.<columns1> = tb2.<columns2> 

:bulb: Hint: dùng để lấy thêm thông tin từ bảng 2 bổ sung thêm cho bảng 1. Số lượng record của bảng 1 sẽ được giữ nguyên hoặc nhiều hơn.

ví dụ

SELECT *
FROM Transaction T left join Production P
                 on T.product_id = P.product_id 

---

Right join syntax

SELECT column_name(s)
FROM <table1> tb1 RIGHT JOIN <table2> tb2 
                  on tb1.<columns1> = tb2.<columns2> 

:bulb: Hint: Giống như left join nhưng lấy bảng bên phải làm chuẩn. Số lượng record của bảng 2 sẽ được giữ nguyên hoặc nhiều hơn.

ví dụ

SELECT *
FROM Transaction T right join Production P
                 on T.product_id = P.product_id 

---

full join syntax

SELECT <columns>
from <table1> tb1 FULL JOIN <table2> tb2 
                  on tb1.<columns1> = tb2.<columns2> 

:bulb: Hint: Lấy hết thông tin ở cả 2 bảng nếu như dòng nào không thỏa điều kiện join thì có giá trị NULL

ví dụ

SELECT *
FROM Transaction T full join Production P
                 on T.product_id = P.product_id 

CROSS join syntax

SELECT column_name(s)
FROM <table1> tb1 CROSS JOIN <table2> tb2 
                  

:bulb: Hint: LẤY 2 TABLE NHÂN LẠI VỚI NHAU

ví dụ

SELECT *
FROM Transaction T cross join Production P
                

MỘT SỐ TIP

1

:bulb: Chú ý:
Việc quan trong khi join 2 table là xác định "Điều kiện join". Nếu mối quan hệ 1-n thì phải chọn cột PK join với cột FK. Nếu không dữ liệu sẽ bị duplicate

2 multi Join

Join nhiều bảng lại với nhau, ta lấy ví dụ có database

ta cần thông tin customer_name , product_name, amount, price

SELECT C.customer_name 
​       ,P.name as product_name
​       ,T.amount
​       p.price
FROM Transaction T 
​    inner join Customer C 
​       on T.customer_id = C.customer_id
​    left join Production P 
​       on T.product_id = P.product_id 

Kết quả

ta cùng đi phân tích tại sao kết quả được như vậy. Trong câu query trên ta thấy có 2 lệnh join

1. là Inner Join
2. là Left Join

SQL server sẽ chạy lệnh đầu tiên

​Transaction T inner join Customer C on T.customer_id = C.customer_id

ra kết quả

sau đó đem kết quả vừa ra đem join tiếp với câu lệnh

left join Production P on T.product_id = P.product_id

ta được kết quả

Sau đó SQL server chọn ra các cột ở mệnh đề Select

:bulb: Chú ý:
Nếu ở mệnh đề join 2 mà ta dùng INNER JOIN thì kết quả cuối cùng sẽ bị mất đi 1 dòng khi đấy nếu ta tính tổng cột amount hay price sẽ không còn đúng. Nên cân nhắc trước khi lựa phép JOIN

UNION, EXCEPT, INTERSECT, SUBQUERY

:bulb: NOTE:
Nếu ở mệnh đề JOIN là ta ghép bảng theo chiều ngang
Thì UNION,EXCEPT INTERSECT là các mệnh để cho phép ta ghép bảng theo chiều dọc

UNION

UNION là phép ́hội (có thể hiểu như phép cộng) có 2 loại

1. UNION : sẽ loại đi các dòng trùng lặp
2. UNION ALL : lấy tất cả các dòng giữa 2 bảng

SELECT tb1.column_name1, tb1.column_name2,...
FROM <table1> tb1
UNION (UNION ALL)
SELECT tb2.column_name1, tb2.column_name2,...
FROM <table2> tb2                 

Ví dụ

chạy thử

	select TransactionID AS [transaction_1]
		   , ProductID as [product_1] 
	from Production.TransactionHistory
UNION  
	select  TransactionID AS [transaction_2]
		   , ProductID as [product_2] 
	from Production.TransactionHistoryArchive                 

:bulb: chú ý:
Khi Union số lượng cột và thứ tự các cột giữa 2 bảng phải giống nhau
Tên của bảng kết quả sẽ lấy theo bảng đầu tiên

EXCEPT

EXCEPT là phép ́trừ

SELECT tb1.column_name1, tb1.column_name2,...
FROM <table1> tb1
EXCEPT
SELECT tb2.column_name1, tb2.column_name2,...
FROM <table2> tb2                 

Ví dụ

lấy những dòng có trong table1 mà không có trong table2

chạy thử

SELECT ProductID   
FROM Production.Product  
EXCEPT  
SELECT ProductID
FROM Production.WorkOrder ;  
                

:bulb: chú ý:
EXCEPT giống Union về số lượng cột và thứ tự các cột giữa 2 bảng phải giống nhau
Tên của bảng kết quả sẽ lấy theo bảng đầu tiên

INERSECT

INERSECT là phép giao

SELECT tb1.column_name1, tb1.column_name2,...
FROM <table1> tb1
INERSECT
SELECT tb2.column_name1, tb2.column_name2,...
FROM <table2> tb2                 

Ví dụ

chạy thử

SELECT ProductID   
FROM Production.Product  
INTERSECT  
SELECT ProductID   
FROM Production.WorkOrder ;  
                

:bulb: chú ý:
INERSECT giống Union về số lượng cột và thứ tự các cột giữa 2 bảng phải giống nhau
Tên của bảng kết quả sẽ lấy theo bảng đầu tiên

Bài tập

join

Data: Sử dụng bộ dữ liệu Adventure Work 2017 OLTP
digaram
Dictionary

1. Dựa vào 2 bảng Sales.Customer và Sales.SalesOrderHeader. Hãy lấy ra Customer_ID và Tổng số lượng đơn hàng của họ sắp xếp kết quả theo số lượng đơn hàng giảm dần.

gợi ý
Sales.Customer(PersonID) và Person.person(BusinessEntityID) quan hệ với nhau

---

2. 

Dựa vào diagram này hãy cho biết số lượng 20 SalesOrderID có số tiền giảm giá nhiều nhất, lấy ra thông tin SalesOderID, Số lượng sản phẩm được giảm giá, Số tiền được giảm

---

3. 
   Bảng ProductListPriceHistory là bảng lưu thông tin lịch sử các lần thay đổi giá bán của sản phẩm.
   VD sản phẩm A qua 3 lần thay đổi giá sẽ được lưu như sau:

ProductID	StartDate	EndDate	ListPrice
A	1.4.2022	31.5.2022	100
A	1.6.2022	31.8.2022	80
A	1.9.2022	NULL	60

Giá mới nhất sẽ có EndDate = NULL. Và cột ListPrice ở bảng Product luôn bằng giá mới nhất của sản phẩm

Dựa vào 2 bảng này lấy ra 10 tên sản phẩm có sự chênh lệch giá nhiều nhất từ quá khứ. Query ra 2 cột tên sản phẩm và số tiền giảm giá

---

4. Dùng lại 2 table câu 3 hãy cho biết top 10 sản phẩm có SỐ LẦN thay đổi giá nhiều nhất

---

5. Lấy ra danh sách customer và product chưa bao giờ mua hàng hay bán.

Gợi ý: Sẽ có các khách hàng không bao giờ mua hàng cũng như có những sản phẩm chưa giờ bán được.
Ta query ra 2 cột Customer_Id va Product_id. Nếu cột Customer_Id có giá trị thì Product_id NUll và ngược lại.
dựa vào 4 bảng:

[Sales.SalesOrderHeader]
[Sales.SalesOrderDetail]
[Production.Product]
[Sales.Customer]

---

Lấy ra danh sách các nhân viên (employee) của công ty, bao gồm:
BusinessEntityID, Title, FirstName, MiddleName, LastName, EmailAddress.

---

Lấy ra các đầu mối liên lạc của các đối tác của công ty (vendor contact), bao gồm:
BusinessEntityID, Title, FirstName, MiddleName, LastName, Position (cột Name ở bảng Person.ContactType), PhoneNumber, Name (phone number type),

union

1. 

Ta có 2 bảng

[Production].[TransactionHistoryArchive] chứa thông tin các giao dịch từ năm 16/4/2011 đến 30/07/2013

[Production].[TransactionHistory] chứa thông tin các giao dịch từ năm 31/07/2013 đến 03/08/2014

Hãy viết câu query lấy đầy đủ các cột của cả 2 bảng. Gộp 2 bảng này lại thành 1 bảng lưu Transaction từ 16/4/2011 đến 03/08/2014.

2. 
   Ta có 2 bảng

[Production].[ProductListPriceHistory] chứa thông tin lịch sử thay đổi của giá bán từng sản phẩm

[Production].[ProductCostHistory] chứa thông tin lịch sử chi phí nhập hàng của sản phẩm

Hãy viết câu query lấy đầy đủ các cột của 2 bảng. Gộp 2 bảng thành 1 bảng và tạo thêm 1 cột Type chứa 2 giá trị 'cost' hoặc 'price' để phân biệt giữa dòng nào là chi phí sản xuất, và giá thành bán ra.

Gợi ý cú pháp thêm 1 cột: