Trong bài viết này, chúng ta sẽ cùng học cách sử dụng toán tử EXISTS và khi nào nên sử dụng nó để cải thiện hiệu suất của các truy vấn.
Toán tử EXISTS là một toán tử Boolean trả về đúng hoặc sai. Toán tử EXISTS thường được sử dụng để kiểm tra sự tồn tại của các hàng do truy vấn con trả về .
Sau đây minh họa cú pháp cơ bản của toán tử EXISTS:
SELECT
select_list
FROM
a_table
WHERE
[NOT] EXISTS(subquery);
Nếu truy vấn con trả về ít nhất một hàng, thì toán tử EXISTS trả về true, nếu không, nó trả về false.
Ngoài ra, toán tử EXISTS kết thúc quá trình xử lý tiếp theo ngay lập tức sau khi tìm thấy hàng phù hợp, điều này có thể giúp cải thiện hiệu suất của truy vấn.
Toán tử NOT là phủ định của toán tử EXISTS. Nói cách khác, NOT EXISTS trả về true nếu truy vấn con không trả về hàng nào, ngược lại trả về false.
Lưu ý rằng bạn có thể sử dụng SELECT *, SELECT column, SELECT a_constant hoặc bất kỳ thứ gì trong truy vấn phụ. Các kết quả giống nhau vì MySQL bỏ qua danh sách lựa chọn xuất hiện trong mệnh đề SELECT.
Hãy lấy một số ví dụ về việc sử dụng toán tử EXISTS để hiểu cách thức hoạt động của nó.
Hãy xem xét các bảng customersvà orders trong cơ sở dữ liệu mẫu .
Câu lệnh sau sử dụng toán tử EXISTS để tìm khách hàng có ít nhất một đơn hàng:
SELECT
customerNumber,
customerName
FROM
customers
WHERE
EXISTS(
SELECT
1
FROM
orders
WHERE
orders.customernumber
= customers.customernumber);
kết quả
Trong ví dụ này, đối với mỗi hàng trong bảng customers, truy vấn sẽ kiểm tra hàng customerNumber trong bảng orders. Nếu customerNumber, xuất hiện trong bảng customers, tồn tại trong bảng orders, thì truy vấn con sẽ trả về hàng phù hợp đầu tiên. Kết quả là toán tử EXISTS trả về giá trị true và ngừng kiểm tra bảng orders. Mặt khác, truy vấn con không trả về hàng nào và toán tử EXISTS trả về false.
Ví dụ sau sử dụng toán tử NOT EXISTS để tìm những khách hàng không có bất kỳ đơn đặt hàng nào:
SELECT
customerNumber,
customerName
FROM
customers
WHERE
NOT EXISTS(
SELECT
1
FROM
orders
WHERE
orders.customernumber = customers.customernumber
);
kết quả
Giả sử bạn phải cập nhật phần mở rộng điện thoại của nhân viên làm việc tại văn phòng ở San Francisco.
Câu lệnh sau đây tìm những nhân viên làm việc tại văn phòng ở San Franciso:
SELECT
employeenumber,
firstname,
lastname,
extension
FROM
employees
WHERE
EXISTS(
SELECT
1
FROM
offices
WHERE
city = 'San Francisco' AND
offices.officeCode = employees.officeCode);
kết quả:
Ví dụ này thêm số 1 vào phần mở rộng điện thoại của nhân viên làm việc tại văn phòng ở San Francisco:
UPDATE employees
SET
extension = CONCAT(extension, '1')
WHERE
EXISTS(
SELECT
1
FROM
offices
WHERE
city = 'San Francisco'
AND offices.officeCode = employees.officeCode);
Làm thế nào nó hoạt động.
EXISTS trong mệnh đề WHERE chỉ lấy những nhân viên làm việc tại văn phòng ở San Fransisco.CONCAT() có chức năng nối phần mở rộng điện thoại với số 1.Giả sử bạn muốn lưu trữ những khách hàng không có bất kỳ đơn đặt hàng nào trong một bảng riêng biệt. Để làm điều này, bạn sử dụng các bước sau:
Đầu tiên, tạo một bảng mới để lưu trữ khách hàng bằng cách sao chép cấu trúc từ bảng customers:
CREATE TABLE customers_archive LIKE customers;
Thứ hai, chèn những khách hàng không có bất kỳ đơn đặt hàng nào vào bảng customers_archive bảng bằng cách sử dụng câu lệnh sau INSERT.
INSERT INTO customers_archive
SELECT *
FROM customers
WHERE NOT EXISTS(
SELECT 1
FROM
orders
WHERE
orders.customernumber = customers.customernumber
);
Thứ ba, truy vấn dữ liệu từ bảng customers_archive để xác minh thao tác insert.
SELECT * FROM customers_archive;
Một nhiệm vụ cuối cùng trong việc lưu trữ dữ liệu khách hàng là xóa các khách hàng tồn tại trong bảng customers_archive khỏi bảng customers.
Để làm điều này, bạn sử dụng toán tử EXISTS trong mệnh đề WHERE của câu lệnh DELETE như sau:
DELETE FROM customers
WHERE EXISTS(
SELECT
1
FROM
customers_archive a
WHERE
a.customernumber = customers.customerNumber);
Để tìm khách hàng đã đặt ít nhất một đơn hàng, bạn có thể sử dụng toán tử IN như trong truy vấn sau:
SELECT
customerNumber,
customerName
FROM
customers
WHERE
customerNumber IN (
SELECT
customerNumber
FROM
orders);
Hãy so sánh truy vấn sử dụng toán tử IN với truy vấn sử dụng toán tử EXISTS bằng cách sử dụng câu lệnh EXPLAIN.
EXPLAIN SELECT
customerNumber,
customerName
FROM
customers
WHERE
EXISTS(
SELECT
1
FROM
orders
WHERE
orders.customernumber = customers.customernumber);
kết quả
Bây giờ, hãy kiểm tra hiệu suất của truy vấn sử dụng toán tử IN.
SELECT
customerNumber, customerName
FROM
customers
WHERE
customerNumber IN (SELECT
customerNumber
FROM
orders);
kết quả
Truy vấn sử dụng toán tử EXISTS nhanh hơn nhiều so với truy vấn sử dụng toán tử IN.
Lý do là toán tử EXISTS hoạt động dựa trên nguyên tắc “ít nhất được tìm thấy”. Việc EXISTS dừng quét bảng khi tìm thấy một hàng phù hợp.
Mặt khác, khi toán tử IN được kết hợp với truy vấn con, MySQL phải xử lý truy vấn con trước rồi sử dụng kết quả của truy vấn con để xử lý toàn bộ truy vấn.
Nguyên tắc chung là nếu truy vấn con chứa một lượng lớn dữ liệu, toán tử EXISTS sẽ cung cấp hiệu suất tốt hơn.
Tuy nhiên, truy vấn sử dụng toán tử IN sẽ thực hiện nhanh hơn nếu tập kết quả trả về từ truy vấn con rất nhỏ.
Ví dụ, câu lệnh sau sử dụng toán tử IN chọn tất cả nhân viên làm việc tại văn phòng ở San Francisco.
SELECT
employeenumber,
firstname,
lastname
FROM
employees
WHERE
officeCode IN (SELECT
officeCode
FROM
offices
WHERE
offices.city = 'San Francisco');
Hãy kiểm tra hiệu suất của truy vấn.
Nó nhanh hơn một chút so với truy vấn sử dụng toán tử EXISTS mà chúng tôi đã đề cập trong ví dụ đầu tiên. Xem hiệu suất của truy vấn sử dụng toán tử EXIST bên dưới:
Trong hướng dẫn này, bạn đã học cách sử dụng toán tử MySQL EXISTS để kiểm tra sự tồn tại của các hàng được trả về bởi một truy vấn con.
Các bạn có thể tham khảo: https://www.mysqltutorial.org/mysql-exists/
Các bài viết liên quan:
Bình luận: