Subquery trong SQL Server

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 orderscustomers trong cơ sở dữ liệu mẫu BikeStores.

Subquery trong SQL Server

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ả:

Subquery trong SQL Server

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:

Subquery trong SQL Server

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'
Subquery trong SQL Server

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;
Subquery lồng nhau trong SQL Server

Đầ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 StriderTrek:

SELECT
    brand_id
FROM
    production.brands
WHERE
    brand_name = 'Strider'
OR brand_name = 'Trek';
Subquery trong SQL Server

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ặc NOT IN
  • Subquery với toán tử ANY hoặc ALL
  • Subquery với toán tử EXISTS hoặc NOT EXISTS
  • Subquery trong câu lệnh UPDATE, DELETE hoặc INSERT.
  • 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 trong SQL Server

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'
    );
Subquery trong SQL Server

Truy vấn này được đánh giá theo hai bước:

  1. Đầ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ặc Road Bikes.
  2. 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
    )
Subquery trong SQL Server

Đố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 trong SQL Server

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;
Subquery trong SQL Server

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 SQL Server

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;
Subquery trong SQL Server

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;
Subquery trong SQL Server

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.

Subquery tương quan trong SQL Server

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ả:

Subquery tương quan trong SQL Server

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.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *