# 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
```sql=
SELECT column_name(s)
FROM <table1> tb1 INNER JOIN <table2> tb2
on tb1.<columns1> = tb2.<columns2>
```
:::info
: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ụ
```sql=
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
```sql=
SELECT column_name(s)
FROM <table1> tb1 LEFT JOIN <table2> tb2
on tb1.<columns1> = tb2.<columns2>
```
:::info
: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ụ
```sql=
SELECT *
FROM Transaction T left join Production P
on T.product_id = P.product_id
```
****
## Right join syntax
```sql=
SELECT column_name(s)
FROM <table1> tb1 RIGHT JOIN <table2> tb2
on tb1.<columns1> = tb2.<columns2>
```
:::info
: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ụ
```sql=
SELECT *
FROM Transaction T right join Production P
on T.product_id = P.product_id
```
****
## full join syntax
```sql=
SELECT <columns>
from <table1> tb1 FULL JOIN <table2> tb2
on tb1.<columns1> = tb2.<columns2>
```
:::info
: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ụ
```sql=
SELECT *
FROM Transaction T full join Production P
on T.product_id = P.product_id
```
## CROSS join syntax
```sql=
SELECT column_name(s)
FROM <table1> tb1 CROSS JOIN <table2> tb2
```
:::info
:bulb: **Hint**: LẤY 2 TABLE NHÂN LẠI VỚI NHAU
:::
ví dụ
```sql=
SELECT *
FROM Transaction T cross join Production P
```
## MỘT SỐ TIP
### 1
:::info
: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**
```sql=
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
```sql=
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
```sql=
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**
:::info
: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
:::info
: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
```sql=
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ử
```sql=
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
```
:::info
: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ừ
```sql=
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ử
```sql=
SELECT ProductID
FROM Production.Product
EXCEPT
SELECT ProductID
FROM Production.WorkOrder ;
```
:::info
: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
```sql=
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ử
```sql=
SELECT ProductID
FROM Production.Product
INTERSECT
SELECT ProductID
FROM Production.WorkOrder ;
```
:::info
: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](https://i0.wp.com/improveandrepeat.com/wp-content/uploads/2018/12/AdvWorksOLTPSchemaVisio.png?ssl=1)
[Dictionary](https://www.sqldatadictionary.com/AdventureWorks2014/)
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.
::: success
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.
::: success
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]
:::
---------------------------------------------------------
6.
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.
---------------------------------------------------------
7.
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
<li> <b>[Production].[TransactionHistoryArchive]</b> chứa thông tin các giao dịch từ năm 16/4/2011 đến 30/07/2013 </li>
<li> <b>[Production].[TransactionHistory]</b> chứa thông tin các giao dịch từ năm 31/07/2013 đến 03/08/2014 </li>
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
<li> <b>[Production].[ProductListPriceHistory]</b> chứa thông tin lịch sử thay đổi của giá bán từng sản phẩm </li>
<li> <b>[Production].[ProductCostHistory]</b> chứa thông tin lịch sử chi phí nhập hàng của sản phẩm </li>
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 <b>Type</b> chứa 2 giá trị <i>'cost'</i> hoặc <i>'price'</i> để 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: 
Sign in with Wallet
Connect another wallet