Subqueries (truy vấn con)

Bạn có thể sử dụng câu lệnh SELECT hoặc một truy vấn để trả về các bản ghi sẽ được sử dụng làm tiêu chí cho một câu lệnh hoặc truy vấn SELECT khác. Truy vấn bên ngoài được gọi là truy vấn mẹ và truy vấn bên trong được gọi là truy vấn con. Mục đích của truy vấn con là trả về kết quả cho truy vấn bên ngoài. Nói cách khác, câu lệnh truy vấn bên trong phải trả về cột hoặc các cột được sử dụng trong tiêu chí của câu lệnh truy vấn bên ngoài.

Dạng truy vấn con đơn giản nhất là dạng truy vấn chỉ trả về một cột. Truy vấn mẹ có thể sử dụng kết quả của truy vấn con này bằng dấu “=”. Cú pháp:

SELECT <ColumnName> FROM <table>
WHERE <ColumnName> = (SELECT <ColumnName> FROM <Table> WHERE <ColumnName> = <Condition>)

Trong một truy vấn con, câu lệnh SELECT trong cùng được thực thi đầu tiên và kết quả của nó được chuyển làm tiêu chí cho câu lệnh SELECT bên ngoài.

Hãy xem xét một tình huống cần xác định ngày đến hạn và ngày giao hàng của các đơn đặt hàng gần đây nhất.

Ví dụ:

SELECT DueDate,ShipDate FROM Sales.SalesOrderHeader WHERE Sales.SalesOrderHeader.OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader)

Ở đây, một truy vấn con đã được sử dụng để đạt được kết quả đầu ra mong muốn. Truy vấn bên trong hoặc truy vấn con truy xuất ngày đặt hàng gần đây nhất. Sau đó, kết quả này được chuyển đến truy vấn bên ngoài, hiển thị ngày đến hạn và ngày giao hàng cho tất cả các đơn đặt hàng, các đơn đặt hàng đã được thực hiện vào ngày cụ thể đó.

Dựa trên kết quả được trả về bởi truy vấn bên trong, một truy vấn con có thể được phân loại là truy vấn con vô hướng (scalar) hoặc truy vấn đa giá trị (multi-valued).
Có những mô tả như sau:

• Truy vấn con scalar trả về một giá trị duy nhất. Ở đây, truy vấn bên ngoài phải được viết để xử lý một kết quả duy nhất.
• Truy vấn con đa giá trị (multi-valued) trả về một kết quả tương tự như bảng một cột. Ở đây, truy vấn bên ngoài phải được viết để xử lý nhiều kết quả có thể có.

Làm việc với truy vấn multi-valued

Nếu toán tử “=” được sử dụng với truy vấn con, truy vấn con phải trả về một giá trị vô hướng duy nhất. Nếu nhiều hơn một giá trị được trả về, sẽ có lỗi và truy vấn sẽ không được xử lý. Trong các trường hợp như vậy, các từ khóa ANY, ALL, IN và EXISTS có thể được sử dụng với mệnh đề WHERE của câu lệnh SELECT khi truy vấn trả về một cột nhưng một hoặc nhiều hàng.

Những từ khóa này, còn được gọi là vị từ, được sử dụng với các truy vấn đa giá trị. Ví dụ: hãy xem xét rằng tất cả họ và tên của nhân viên có chức danh công việc là ‘Research and Development Manager’ phải được hiển thị. Ở đây, truy vấn bên trong có thể trả về nhiều hơn một hàng, vì có thể có nhiều nhân viên với chức danh công việc đó. Để đảm bảo rằng truy vấn bên ngoài có thể sử dụng kết quả của truy vấn bên trong, từ khóa IN sẽ phải được sử dụng.

Ví dụ:

SELECT FirstName,LastName FROM Person.Person WHERE Person.Person.BusinessEntityID IN (SELECT BusinessEntityID FROM HumanResources.Employee WHERE JobTitle='Research and Development Manager');

Từ khóa SOME hoặc ANY nào được đánh giá là true nếu kết quả là một truy vấn bên trong có chứa tại một hàng ngang bằng với phép so sánh. So sánh một giá trị vô hướng với một cột giá trị. SOME và ANY là tương đương, cả hai đều trả về cùng một kết quả. Chúng hiếm khi được sử dụng.

Một số hướng dẫn khi sử dụng sub query:

• Kiểu dữ liệu ntext,text, image khoong tể sử dụng trong SELECT ở subqueries
• Danh sách SELECT của subquery được giới thiệu với toán tử so sánh chỉ có thể có một tên biểu thức hoặc cột.
• Sub query khi làm việc với toán tử so sánh có từ khóa ANY hoặc ALL thì không thể sử dụng các mệnh đề GROUP BY và HAVING
• Bạn không thể sử dụng từ khóa DISTINCT với các sub query chứa mệnh đề GROUP BY
• Bạn chỉ có thể chỉ định ORDER BY khi TOP cũng được chỉ định khi sử dụng sub query

Bên cạnh các subquery scalar và multi valued, bạn cũng có thể chọn giữa các truy vấn con độc lập và các truy vấn con tương quan. Chúng được định nghĩa như sau:

• Truy vấn con độc lập được viết dưới dạng truy vấn độc lập, không có bất kỳ sự phụ thuộc nào vào truy vấn bên ngoài. Một truy vấn con độc lập được xử lý một lần khi truy vấn bên ngoài chạy và chuyển kết quả của nó cho truy vấn bên ngoài.
• Các truy vấn con có liên quan tham chiếu đến một hoặc nhiều cột từ truy vấn bên ngoài và do đó, phụ thuộc vào truy vấn bên ngoài. Các truy vấn con có liên quan không thể chạy riêng biệt với truy vấn bên ngoài.

Từ khóa EXISTS được sử dụng cùng subquery để kểm tra sự tồn tại của hàng trả về bởi subquery. Nó không thực sự return về dữ liệu và return về TRUE hoặc FALSE

Cú pháp của subquery chứa từ khóa EXISTS:

SELECT <ColumnName> FROM <table> WHERE [NOT] EXISTS (subquery_statement)

Ví dụ:

use AdventureWorks2019
go
SELECT FirstName,LastName FROM Person.Person AS p WHERE EXISTS (SELECT * FROM HumanResources.Employee AS e WHERE JobTitle='Research and Development Manager' AND p.BusinessEntityID=e.BusinessEntityID )

Tại đây, truy vấn con bên trong truy xuất tất cả các bản ghi phù hợp với chức danh công việc là ‘Research and Development Manager’ và BusinessEntityID có BusinessEntityID phù hợp với bản ghi đó trong bảng Person. Nếu không có bản ghi nào phù hợp với cả hai điều kiện này, truy vấn con bên trong sẽ không trả về bất kỳ hàng nào. Tuy nhiên, đoạn mã dưới đây sẽ trả về hai hàng vì các điều kiện đã cho được thỏa mãn.

Tương tự, bạn có thể sử dụng từ khóa NOT EXISTS, đây là mệnh đề phủ định của EXISTS.

Nested Subqueries (Truy vấn con lồng nhau)

Một truy vấn con được xác định bên trong một truy vấn con khác được gọi là truy vấn con lồng nhau (nested subqueries). Xét tình huống bạn muốn truy xuất và hiển thị tên của những người từ Canada trong adventureworks2019, không có cách nào trực tiếp để truy xuất thông tin này vì bảng Sales.SalesTerritory không liên quan đến bảng Person.Person. Do đó, một truy vấn con lồng nhau có thể là giải pháp xử lý.

Ví dụ:

SELECT LastName, FirstName FROM Person.Person WHERE BusinessEntityID IN (SELECT BusinessEntityID FROM Sales.SalesPerson WHERE TerritoryID IN (SELECT TerritoryID FROM Sales.SalesTerritory WHERE Name='Canada'))

Correlated Queries ( Truy vấn liên quan)

Trong Nhiều truy vấn có chứa truy vấn con, truy vấn con chỉ được đánh giá một lần để cung cấp các giá trị theo yêu cầu của truy vấn cha. Điều này là do trong hầu hết các truy vấn, truy vấn con không tham chiếu đến truy vấn cha, vì vậy giá trị trong truy vấn con không đổi
Tuy nhiên, nếu truy vấn con tham chiếu đến truy vấn cha, thì truy vấn con phải được đánh giá lại cho mọi lần lặp trong truy vấn cha. Điều này là do tiêu chí tìm kiếm trong truy vấn con phụ thuộc vào giá trị của một bản ghi cụ thể trong truy vấn cha.
Khi một truy vấn con nhận các tham số từ truy vấn cha, nó được gọi là Truy vấn con có liên quan. Hãy xét tình huống bạn muốn truy xuất tất cả BusinessEntityID của những người có thông tin liên hệ được sửa đổi lần cuối sau năm 2012. Để thực hiện việc này, bạn có thể sử dụng truy vấn con có liên quan như sau:

SELECT a.BusinessEntityID FROM Person.BusinessEntityContact a WHERE a.ContactTypeID IN (SELECT c.ContactTypeID FROM Person.ContactType c WHERE YEAR (a.ModifiedDate) >= 12)

Trong đoạn mã, truy vấn bên trong truy xuất id loại liên hệ cho tất cả những người có thông tin liên hệ được sửa đổi lần cuối sau năm 2012. Sau đó, những kết quả này được chuyển đến truy vấn bên ngoài, kết quả này khớp các ContactTypeID trong bảng Person.BusinessEntityContact.