Cách sử dụng Subquery trong MySQL

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');

Khi thực hiện truy vấn, MySQL đánh giá truy vấn con trước và sử dụng kết quả của truy vấn con cho truy vấn bên ngoài.

Sử dụng Subquery trong mệnh đề WHERE

Chúng tôi sẽ sử dụng bảng payments trong cơ sở dữ liệu mẫu để trình diễn.

Sử dụng Subquery với các toán tử so sánh

Bạn có thể sử dụng các toán tử so sánh, chẳng hạn như =, >, < để so sánh một giá trị đơn do truy vấn con trả về với biểu thức trong mệnh đề WHERE.

Ví dụ: truy vấn sau trả về khách hàng có khoản thanh toán cao nhất.

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount = (SELECT MAX(amount) FROM payments);

kết quả:

Bên cạnh toán tử =, bạn có thể sử dụng các toán tử so sánh khác như lớn hơn ( >), lớn hơn hoặc bằng (>=) nhỏ hơn ( <) và nhỏ hơn hoặc bằng (<=).

Ví dụ: bạn có thể tìm khách hàng có khoản thanh toán lớn hơn khoản thanh toán trung bình bằng cách sử dụng truy vấn phụ:

SELECT 
    customerNumber, 
    checkNumber, 
    amount
FROM
    payments
WHERE
    amount > (SELECT 
            AVG(amount)
        FROM
            payments);

kết quả:

Trong ví dụ này:

• Đầu tiên, nhận khoản thanh toán trung bình bằng cách sử dụng truy vấn phụ.
• Sau đó, chọn các khoản thanh toán lớn hơn khoản thanh toán trung bình do truy vấn con trả về trong truy vấn bên ngoài.

Sử dụng MySQL Subquert với toán tử IN và NOT IN

Nếu truy vấn con trả về nhiều hơn một giá trị, bạn có thể sử dụng các toán tử khác chẳng hạn như toán tử IN hoặc NOT IN trong mệnh đề WHERE.

Xem các bảng sau đây customers và orders:

Ví dụ: bạn có thể sử dụng truy vấn con với toán tử NOT IN để tìm những khách hàng chưa đặt bất kỳ đơn hàng nào như sau:

SELECT 
    customerName
FROM
    customers
WHERE
    customerNumber NOT IN (SELECT DISTINCT
            customerNumber
        FROM
            orders);

kết quả

Sử dụng Subquery trong mệnh đề FROM

Khi bạn sử dụng truy vấn phụ trong mệnh đề FROM, tập hợp kết quả trả về từ truy vấn phụ được dùng làm bảng tạm thời. Bảng này được gọi là bảng dẫn xuất hoặc truy vấn con cụ thể hóa.

Truy vấn con sau tìm số lượng mặt hàng tối đa , tối thiểu và trung bình trong đơn đặt hàng:

SELECT 
    MAX(items), 
    MIN(items), 
    FLOOR(AVG(items))
FROM
    (SELECT 
        orderNumber, COUNT(orderNumber) AS items
    FROM
        orderdetails
    GROUP BY orderNumber) AS lineitems;

kết quả

Lưu ý rằng FLOOR() được sử dụng để xóa các vị trí thập phân khỏi giá trị trung bình của các mục.

MySQL correlated subquery

Trong các ví dụ trước, bạn nhận thấy rằng truy vấn phụ là độc lập. Điều đó có nghĩa là bạn có thể thực thi truy vấn con dưới dạng truy vấn độc lập, ví dụ:

SELECT 
    orderNumber, 
    COUNT(orderNumber) AS items
FROM
    orderdetails
GROUP BY orderNumber;

Không giống như truy vấn con độc lập, truy vấn con tương quan là truy vấn con sử dụng dữ liệu từ truy vấn bên ngoài. Nói cách khác, truy vấn con tương quan phụ thuộc vào truy vấn bên ngoài. Truy vấn con tương quan được đánh giá một lần cho mỗi hàng trong truy vấn bên ngoài.

Xem bảng sau products từ cơ sở dữ liệu mẫu:

Ví dụ sau sử dụng truy vấn con tương quan để chọn các sản phẩm có giá mua lớn hơn giá mua trung bình của tất cả các sản phẩm trong mỗi  dòng sản phẩm.

SELECT 
    productname, 
    buyprice
FROM
    products p1
WHERE
    buyprice > (SELECT 
            AVG(buyprice)
        FROM
            products
        WHERE
            productline = p1.productline)

kết quả:

Trong ví dụ này, cả truy vấn bên ngoài và truy vấn con tương quan đều tham chiếu đến cùng một bảng products. Do đó, chúng ta cần sử dụng bí danh bảng p1 cho bảng products trong truy vấn bên ngoài.

Không giống như một truy vấn con thông thường, bạn không thể thực thi một truy vấn con tương quan một cách độc lập như thế này. Nếu bạn làm như vậy, MySQL không biết bảng p1 và sẽ báo lỗi.

SELECT 
    AVG(buyprice)
FROM
    products
WHERE
    productline = p1.productline;

Đối với mỗi hàng trong bảng products (hoặc p1), truy vấn con tương quan cần thực hiện một lần để có được giá mua trung bình của tất cả các sản phẩm trong hàng productline đó.

Nếu giá mua của hàng hiện tại lớn hơn giá mua trung bình do truy vấn con tương quan trả về, thì truy vấn sẽ bao gồm hàng đó trong tập hợp kết quả.

Sử dụng MySQL Subquert với EXISTS và NOT EXISTS

Khi truy vấn con được sử dụng với  toán tử EXISTS hoặc NOT EXISTS, truy vấn con trả về giá trị Boolean của TRUE hoặc FALSE. Truy vấn sau đây minh họa một truy vấn con được sử dụng với toán tử EXISTS:

SELECT 
    *
FROM
    table_name
WHERE
    EXISTS( subquery );

Trong truy vấn ở trên, nếu truy vấn con trả về bất kỳ hàng nào thì EXISTS subquery trả về TRUE, nếu không thì trả về FALSE.

và thường được sử dụng trong các truy vấn con tương quan EXISTS.NOT EXISTS

Chúng ta hãy xem các bảng orders và orderdetails từ cơ sở dữ liệu mẫu :

Truy vấn sau tìm các đơn đặt hàng có tổng giá trị lớn hơn 60K.

SELECT 
    orderNumber, 
    SUM(priceEach * quantityOrdered) total
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;

Nó trả về 3 hàng, nghĩa là có ba đơn đặt hàng có tổng giá trị lớn hơn 60K.

Bạn có thể sử dụng truy vấn ở trên làm truy vấn con tương quan để tìm những khách hàng đã đặt ít nhất một đơn đặt hàng có tổng giá trị lớn hơn 60K bằng cách sử dụng toán tử EXISTS:

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS( SELECT 
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)
        WHERE
            customerNumber = customers.customerNumber
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000);

Tổng kết

• Subquery là một truy vấn được lồng trong một truy vấn khác (hoặc truy vấn bên ngoài).
• Một truy vấn con tương quan phụ thuộc vào truy vấn bên ngoài.
• Tham khảo thêm tại: https://www.mysqltutorial.org/mysql-subquery/

Các bài viết liên quan:

• Mysql JOIN
• Mysql inner join
• Mysql left join
• Mysql Group By
• MySQL HAVING