SUBQUERY (truy vấn lồng)

Giới thiệu

: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

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

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

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

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

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

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

EXISTS ( subquery )

: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

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

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

columns [ NOT ] IN   
    ( subquery | expression [ ,...n ]  
    )   

: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

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

: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

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

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

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

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

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

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

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

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

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

SELECT P.*
FROM Person.Person P
WHERE EXISTS (SELECT  S.BusinessEntityID
              FROM Sales.SalesPerson S
              WHERE S.BusinessEntityID = P.BusinessEntityID)

Bài tập

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

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

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