Subquery trong SQL Server
Trong hướng dẫn này, bạn sẽ tìm hiểu về truy vấn con (subquery) trong SQL Server và cách sử dụng truy vấn con để truy vấn dữ liệu.
Giới thiệu về Subquery trong SQL Server
Một subquery là một truy vấn lồng vào bên trong một tuyên bố như SELECT
, INSERT
, UPDATE
, hoặc DELETE
.
Ví dụ: hãy xem xét các bảng orders
và customers
trong cơ sở dữ liệu mẫu BikeStores.
Câu lệnh sau đây chỉ ra cách sử dụng một truy vấn con trong mệnh đề WHERE
của một câu lệnh SELECT
để tìm đơn đặt hàng bán hàng của những khách hàng ở New York
:
SELECT
order_id,
order_date,
customer_id
FROM
sales.orders
WHERE
customer_id IN (
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'New York'
)
ORDER BY
order_date DESC;
Đây là kết quả:
Trong ví dụ này, câu lệnh sau là một truy vấn con:
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'New York'
Lưu ý rằng bạn phải luôn đặt câu truy vấn SELECT
của một truy vấn con trong dấu ngoặc đơn ()
.
Truy vấn con còn được gọi là truy vấn bên trong hoặc vùng chọn bên trong trong khi câu lệnh chứa truy vấn con được gọi là vùng chọn bên ngoài hoặc truy vấn bên ngoài:
SQL Server thực thi toàn bộ ví dụ truy vấn ở trên như sau:
Đầu tiên, SQL Server thực hiện truy vấn con để lấy danh sách mã khách hàng của những khách hàng có địa chỉ ở New York
.
SELECT
customer_id
FROM
sales.customers
WHERE
city = 'New York'
Tiếp theo, SQL Server sử dụng các mã khách hàng được trả về bởi truy vấn con gắn vào toán tử IN
và thực hiện truy vấn bên ngoài để có được tập kết quả cuối cùng.
Như bạn có thể thấy, bằng cách sử dụng truy vấn con, bạn có thể kết hợp hai bước với nhau. Truy vấn con loại bỏ nhu cầu chọn mã khách hàng và gắn chúng vào truy vấn bên ngoài. Hơn nữa, bản thân truy vấn sẽ tự động điều chỉnh bất cứ khi nào dữ liệu khách hàng thay đổi.
Subquery lồng nhau trong SQL Server
Một truy vấn con có thể được lồng trong một truy vấn con khác. SQL Server hỗ trợ tối đa 32 cấp độ lồng nhau. Hãy xem xét ví dụ sau:
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price > (
SELECT
AVG (list_price)
FROM
production.products
WHERE
brand_id IN (
SELECT
brand_id
FROM
production.brands
WHERE
brand_name = 'Strider'
OR brand_name = 'Trek'
)
)
ORDER BY
list_price;
Đầu tiên, SQL Server thực thi truy vấn con sau để nhận danh sách các mã thương hiệu của thương hiệu Strider
và Trek
:
SELECT
brand_id
FROM
production.brands
WHERE
brand_name = 'Strider'
OR brand_name = 'Trek';
Tiếp theo, SQL Server tính toán bảng giá trung bình của tất cả các sản phẩm thuộc các thương hiệu đó.
SELECT
AVG (list_price)
FROM
production.products
WHERE
brand_id IN (6,9)
Cuối cùng, SQL Server tìm các sản phẩm có giá niêm yết lớn hơn giá niêm yết trung bình của tất cả các sản phẩm có nhãn hiệu Strider
hoặc Trek
.
Các loại subquery trong SQL Server
Bạn có thể sử dụng một truy vấn con (subquery) ở nhiều nơi như:
- Subquery thay cho một biểu thức
- Subquery với toán tử
IN
hoặcNOT IN
- Subquery với toán tử
ANY
hoặcALL
- Subquery với toán tử
EXISTS
hoặcNOT EXISTS
- Subquery trong câu lệnh
UPDATE
,DELETE
hoặcINSERT
. - Subquery trong mệnh đề
FROM
.
Subquery được sử dụng thay cho một biểu thức trong SQL Server
Nếu truy vấn con trả về một giá trị duy nhất, nó có thể được sử dụng ở bất kỳ nơi nào mà biểu thức được sử dụng.
Trong ví dụ sau, một truy vấn con được sử dụng như một biểu thức cột có tên max_list_price
trong một câu lệnh SELECT
.
SELECT
order_id,
order_date,
(
SELECT
MAX (list_price)
FROM
sales.order_items i
WHERE
i.order_id = o.order_id
) AS max_list_price
FROM
sales.orders o
order by order_date desc;
Subquery được sử dụng với toán tử IN
trong SQL Server
Truy vấn con được sử dụng với toán tử IN
trả về một tập hợp các giá trị. Sau khi truy vấn con trả về các giá trị, truy vấn bên ngoài sẽ sử dụng chúng.
Truy vấn sau đây tìm tên của tất cả các sản phẩm xe đạp leo núi và xe đạp đường trường mà Cửa hàng xe đạp bán.
SELECT
product_id,
product_name
FROM
production.products
WHERE
category_id IN (
SELECT
category_id
FROM
production.categories
WHERE
category_name = 'Mountain Bikes'
OR category_name = 'Road Bikes'
);
Truy vấn này được đánh giá theo hai bước:
- Đầu tiên, truy vấn bên trong trả về một danh sách các mã danh mục khớp với tên
Mountain Bikes
hoặcRoad Bikes
. - Thứ hai, các giá trị này được thay thế vào truy vấn bên ngoài để tìm tên sản phẩm có mã danh mục khớp với một trong các giá trị trong danh sách.
Subquery được sử dụng với toán tử ANY
trong SQL Server
Truy vấn con được giới thiệu với toán tử ANY
có cú pháp sau:
scalar_expression comparison_operator ANY (subquery)
Giả sử rằng truy vấn con trả về một danh sách có giá trị V1, V2,… Vn. Toán tử ANY
trả về TRUE
nếu một trong những cặp so sánh (scalar_expression
, Vi) trả về TRUE
; nếu không, nó trả về FALSE
.
Ví dụ: truy vấn sau đây tìm các sản phẩm có giá niêm yết lớn hơn hoặc bằng giá niêm yết trung bình của bất kỳ nhãn hiệu sản phẩm nào.
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price >= ANY (
SELECT
AVG (list_price)
FROM
production.products
GROUP BY
brand_id
)
Đối với mỗi thương hiệu, truy vấn con sẽ tìm giá niêm yết tối đa. Truy vấn bên ngoài sử dụng các giá tối đa này và xác định giá niêm yết của sản phẩm riêng lẻ nào lớn hơn hoặc bằng giá niêm yết tối đa của bất kỳ thương hiệu nào.
Subquery được sử dụng với toán tử ALL
trong SQL Server
Cú pháp truy vấn con kết hợp toán tử ALL
tương tự như các ANY
:
scalar_expression comparison_operator ALL (subquery)
Toán tử ALL
trả về TRUE
nếu tất cả các cặp so sánh (scalar_expression
, Vi) trả về TRUE
; nếu không, nó trả về FALSE
.
Truy vấn sau tìm các sản phẩm có giá niêm yết lớn hơn hoặc bằng giá niêm yết trung bình do truy vấn con trả về:
SELECT
product_name,
list_price
FROM
production.products
WHERE
list_price >= ALL (
SELECT
AVG (list_price)
FROM
production.products
GROUP BY
brand_id
)
Subquery được sử dụng với toán tử EXISTS
hoặc NOT EXISTS
trong SQL Server
Sau đây minh họa cú pháp của một truy vấn con được giới thiệu với toán tử EXISTS
:
WHERE [NOT] EXISTS (subquery)
Toán tử EXISTS
trả về TRUE
nếu subquery trả về kết quả; nếu không thì nó trả về FALSE
.
Toán tử NOT EXISTS
thì ngược lại với toán tử EXISTS
.
Truy vấn sau đây tìm những khách hàng đã mua sản phẩm trong năm 2017:
SELECT
customer_id,
first_name,
last_name,
city
FROM
sales.customers c
WHERE
EXISTS (
SELECT
customer_id
FROM
sales.orders o
WHERE
o.customer_id = c.customer_id
AND YEAR (order_date) = 2017
)
ORDER BY
first_name,
last_name;
Nếu bạn sử dụng toán tử NOT EXISTS
thay thế toán tử EXISTS
ở trong ví dụ trên, bạn sẽ tìm thấy những khách hàng không mua bất kỳ sản phẩm nào trong năm 2017.
SELECT
customer_id,
first_name,
last_name,
city
FROM
sales.customers c
WHERE
NOT EXISTS (
SELECT
customer_id
FROM
sales.orders o
WHERE
o.customer_id = c.customer_id
AND YEAR (order_date) = 2017
)
ORDER BY
first_name,
last_name;
Subquery trong mệnh đề FROM
trong SQL Server
Giả sử rằng bạn muốn tìm giá trị trung bình của tổng tiền đơn đặt hàng của tất cả các nhân viên bán hàng. Để làm điều này, trước tiên bạn có thể tìm số lượng đơn đặt hàng của nhân viên:
SELECT
staff_id,
COUNT(order_id) order_count
FROM
sales.orders
GROUP BY
staff_id;
Sau đó, bạn có thể áp dụng hàm AVG()
cho tập kết quả này. Vì một truy vấn trả về một tập hợp kết quả trông giống như một bảng ảo, bạn có thể đặt toàn bộ truy vấn trong mệnh đề FROM
của một truy vấn khác như sau:
SELECT
AVG(order_count) average_order_count_by_staff
FROM
(
SELECT
staff_id,
COUNT(order_id) order_count
FROM
sales.orders
GROUP BY
staff_id
) t;
Truy vấn mà bạn đặt trong mệnh đề FROM
phải có bí danh bảng. Trong ví dụ này, chúng tôi sử dụng t làm bí danh bảng cho truy vấn con. Để đưa ra kết quả cuối cùng, SQL Server thực hiện các bước sau:
- Thực thi truy vấn con trong mệnh đề
FROM
. - Sử dụng kết quả của truy vấn con và thực hiện truy vấn bên ngoài.
Subquery tương quan trong SQL Server
Subquery tương quan là một truy vấn con sử dụng các giá trị của truy vấn bên ngoài. Nói cách khác, nó phụ thuộc vào truy vấn bên ngoài cho các giá trị của nó. Do sự phụ thuộc này, một truy vấn con tương quan không thể được thực thi độc lập như một truy vấn con đơn giản.
Hơn nữa, một truy vấn con tương quan được thực thi lặp lại, một lần cho mỗi hàng được đánh giá bởi truy vấn bên ngoài. Truy vấn con tương quan còn được gọi là truy vấn con lặp lại.
Hãy xem xét bảng products
trong cơ sở dữ liệu mẫu BikeStores.
Ví dụ sau đây tìm các sản phẩm có giá niêm yết bằng giá niêm yết cao nhất của các sản phẩm trong cùng danh mục:
SELECT
product_name,
list_price,
category_id
FROM
production.products p1
WHERE
list_price IN (
SELECT
MAX (p2.list_price)
FROM
production.products p2
WHERE
p2.category_id = p1.category_id
GROUP BY
p2.category_id
)
ORDER BY
category_id,
product_name;
Đây là kết quả:
Trong ví dụ này, đối với mỗi sản phẩm được đánh giá bởi truy vấn bên ngoài, truy vấn phụ sẽ tìm giá cao nhất của tất cả các sản phẩm trong danh mục của nó. Nếu giá của sản phẩm hiện tại bằng với giá cao nhất của tất cả các sản phẩm trong danh mục của nó, thì sản phẩm đó được thêm vào tập kết quả trả về. Quá trình này tiếp tục cho các sản phẩm tiếp theo và như vậy.
Như bạn có thể thấy, truy vấn con tương quan được thực thi một lần cho mỗi sản phẩm được đánh giá bởi truy vấn bên ngoài.
Trong hướng dẫn này, bạn đã học về khái niệm truy vấn con (subquery) của SQL Server và cách sử dụng các loại truy vấn con khác nhau để truy vấn dữ liệu.