# SUBQUERY (truy vấn lồng)
## Giới thiệu
:::info
:bulb: **Định nghĩa**:
truy vấn lồng là câu truy vấn mà bên trong của nó chưa 1 câu truy vấn khác
:::
```sql
SELECT A
FROM X
WHERE ... (SELECT B FROM Y WHERE ...) ...
```
## Vị trí
Nằm đâu cũng được từ **SELECT**, **FROM** cho tới **WHERE** (thường nhất)
vd
nằm trong select
```sql
select ProductID, Name, (SELECT COUNT(*)
FROM Production.ProductReview PR
WHERE P.ProductID = PR.ProductID) AS review_amount
from Production.Product P
ORDER BY review_amount desc
```
nằm trong from
```sql
select PR.ProductID, P.Name, PR.Comments, PR.ReviewerName
from (SELECT ProductID, Comments, ReviewerName
FROM Production.ProductReview
) AS PR
left join Production.Product P on PR.ProductID = P.ProductID
```
nằm trong where
```sql
select *
from HumanResources.EmployeePayHistory
where rate > (select rate
from HumanResources.EmployeePayHistory
where BusinessEntityID = 250 and year(RateChangeDate) =2012
)
```
## Phân loại
> Ta có 2 loại truy vấn lồng
> 1. Truy vấn lồng phân cấp. Câu con **độc lập** câu cha
> 2. Truy vấn lồng tương quan. Câu con **phụ thuộc** câu cha
### Phân cấp
```sql
SELECT A
FROM X
WHERE…(SELECT B,C FROM Y ) …
```
2 câu query độc lập nhau
Trong mệnh đề phân cấp ta sẽ làm quen với các toán tử
> IN
> ALL
> ANY | SOME
### Tương quan
```sql
SELECT A
FROM X
WHERE…(SELECT B,C FROM Y WHERE B = X.A) …
```
ta thấy mệnh đề WHERE của câu con có liên quan đến câu cha
Trong mệnh đề tương quan ta sẽ làm quen với các toán tử
> EXISTS
> ALL
> ANY | SOME
### EXISTS
cú pháp
```sql=
EXISTS ( subquery )
```
:::info
:bulb: **Định nghĩa**:
Toán tử EXISTS trả về **True** nếu truy vấn con trả về một hoặc nhiều dòng dữ liệu.
:::
ví dụ
Lấy ra tên họ của những người xuất hiện trong bảng Employee có mức lương rate cao hơn 30
```sql=
select LastName, FirstName
from Person.Person P
where exists (select 1
from HumanResources.EmployeePayHistory EP
where P.BusinessEntityID = EP.BusinessEntityID
and EP.Rate > 30) ;
```
trái với EXIST ta có NOT EXIST
```sql=
select LastName, FirstName
from Person.Person P
where not exists (select 1
from HumanResources.EmployeePayHistory EP
where P.BusinessEntityID = EP.BusinessEntityID
and EP.Rate < 30) ;
```
> Ưu điểm của việc sử dụng toán tử SQL EXISTS và NOT EXISTS là việc thực thi truy vấn con bên trong có thể dừng lại miễn là tìm thấy một bản ghi phù hợp.
>Nếu truy vấn con yêu cầu quét một khối lượng lớn các bản ghi, việc dừng thực thi truy vấn con ngay sau khi một bản ghi được khớp có thể tăng tốc đáng kể thời gian phản hồi truy vấn tổng thể
### IN
```sql=
columns [ NOT ] IN
( subquery | expression [ ,...n ]
)
```
:::info
:bulb: **Định nghĩa**:
Toán tử IN trả về **True** nếu giá trị khớp với 1 hay nhiều giá trị trong kết quả trả về hay danh sách
:::
ví dụ
Lấy ra tên họ của những người xuất hiện trong bảng Employee có mức lương rate cao hơn 30
```sql=
select LastName, FirstName
from Person.Person P
where P.BusinessEntityID in (select EP.BusinessEntityID
from HumanResources.EmployeePayHistory EP
where EP.Rate > 30) ;
```
Phủ đinh của IN là NOT IN
:::info
:bulb: **Định nghĩa**:
Nếu truy vấn con hoặc biểu thức được so sánh với <columns> có NULL trong kết quả sử dụng IN hoặc NOT IN đều trả về UNKNOWN. Sử dụng các giá trị null cùng với IN hoặc NOT IN có thể tạo ra kết quả không mong muốn.
:::
vd
```sql=
select 'true' where 3 in (1, 2, 3, null)
select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null
select 'true' where 3 not in (1, 2, null)
select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null
```
```sql
select 'true' where 3 in (1, 2, 3, null)
select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null
3 = 1 or 3 = 2 or 3 = 3 or 3 = null
which is:
FALSE or FALSE or TRUE or UNKNOWN
which evaluates to
TRUE
```
```sql
select 'true' where 3 not in (1, 2, null)
select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null
3 <> 1 and 3 <> 2 and 3 <> null
which evaluates to:
TRUE and TRUE and UNKNOWN
which evaluates to:
UNKNOWN
```
### ALL
```sql=
scalar_expression ("<", ">", "<>", "!=", "=") ALL [subquery]
```
trả về kết quả đúng nếu scalar_expression lớn hơn tất cả hay khác tất cả ,... các kết quả cảu sub query
ví dụ tìm họ tên người có rate lương cao nhất
```sql=
select LastName, FirstName
from HumanResources.EmployeePayHistory ER inner join Person.Person P
on ER.BusinessEntityID = P.BusinessEntityID
where ER.Rate >= ALL (select Rate
from HumanResources.EmployeePayHistory
)
```
### ANY | SOME
```sql=
scalar_expression ("<", ">", "<>", "!=", "=") any|some [subquery]
```
trả về kết quả đúng nếu scalar_expression lớn hơn Một hay một vài hay khác Một hay một vài ,... các kết quả cảu sub query
# Một vài Ví dụ
1 Tìm ra SalesOrderID, CustomerID mua được nhiều sản phẩm nhất trong 1 đơn hàng
VỚI IN
```SQL
SELECT H.SalesOrderID, H.CustomerID
FROM Sales.SalesOrderHeader H
WHERE H.SalesOrderID in ( select top 1 with ties D.SalesOrderID
from Sales.SalesOrderDetail D
group by D.SalesOrderID
order by SUM(D.OrderQty) desc
)
```
VỚI EXITST
```sql=
SELECT H.SalesOrderID, H.CustomerID
FROM Sales.SalesOrderHeader H
WHERE exists ( select 1
from Sales.SalesOrderDetail D1
where D1.SalesOrderID = H.SalesOrderID
group by D1.SalesOrderID
having SUM(D1.OrderQty) >= ALL( SELECT SUM(D2.OrderQty)
FROM Sales.SalesOrderDetail D2
GROUP BY D2.SalesOrderID)
)
```
2. Tìm ra tất cả thông tin về người của những nhân viên Sales
```sql=
SELECT P.*
FROM Person.Person p
WHERE P.BusinessEntityID IN (SELECT S.BusinessEntityID
FROM Sales.SalesPerson S)
```
cách 2 có the giải bằng join
cách 3 có thể giải bằng EXISTS
```sql=
SELECT P.*
FROM Person.Person P
WHERE EXISTS (SELECT S.BusinessEntityID
FROM Sales.SalesPerson S
WHERE S.BusinessEntityID = P.BusinessEntityID)
```
# Bài tập
1.
Dựa vào bảng trên lấy ra BusinessEntityID,LoginID,JobTitle, VacationHours.
Với điều kiện VacationHours >= số giờ nghỉ phép trung bình [avg(VacationHours)] của toàn bảng.
2.
lấy tên của những nhân viên cũng đồng thời là khách hàng
3. 
giá bằng giá sản phẩm tên 'Touring End Caps'
4. Cùng quan sát NOT IN nhạy cảm thể nào với giá trị NULL.
bảng vendor là bảng chưa thông tin những Công Ty mà công ty Adventure work mua linh kiện. Product vendor là bảng mapping với bảng Vendor thông qua những sản phẩm mà công ty đó bán.
Bước 1 ta tạo ra 1 bảng MyVendor với câu lệnh sau copy chạy
```sql
CREATE TABLE Purchasing.MyVendors
(
BusinessEntityID int,
AccountNumber nvarchar(15),
Name nvarchar(50)
)
GO
-- chọn vài dòng dữ liệu từ bảng vendor bỏ vô MyVendors
INSERT INTO Purchasing.MyVendors
SELECT BusinessEntityID, AccountNumber, Name
FROM Purchasing.Vendor
WHERE BusinessEntityID IN (SELECT BusinessEntityID
FROM Purchasing.ProductVendor)
AND BusinessEntityID like '14%'
```
Dùng "NOT IN" để tìm ra "**Product.[Name]**", "**ProductVendor.BusinessEntityID**", "**ProductVendor.StandardPrice**", "**Product.ListPrice**". Với những **ProductVendor.BusinessEntityID** không xuất hiện trong bảng **MyVendors** vừa tạo
_ Sau khi chạy xong ra kết quả như bên dưới
ta tiếp tục thêm một câu lệnh thêm 1 dòng dữ liệu Null vào bang **MyVendors**
```sql=
INSERT INTO Purchasing.MyVendors
VALUES (NULL, '123', 'TEST_NULL')
```
Sau đó chạy lại câu query của bạn lucs nảy, và so sánh dữ liệu.
5. Tìm cách giải quyết kết quả sai câu 4.
_ gợi ý
> 1 đảm bảo câu subquery không chứa giá trị NULL
> 2 Hoặc đảm bảo bẳng MyVendors không thêm đc giá trị NULL
Sign in with Wallet
Connect another wallet