Một số câu hỏi thực hiện hai bước để trả lời. “Đơn hàng nào trên mức trung bình?” yêu cầu bạn phải tìm mức trung bình trước, sau đó so sánh từng đơn hàng với nó. Truy vấn con SQL cho phép bạn viết cả hai bước dưới dạng một câu lệnh.

Hướng dẫn này đề cập đến từng loại bằng cách sử dụng hai bảng nhỏ. Chúng tôi đã chạy các truy vấn này trong SQLite 3.50 và kết quả hiển thị là từ lần chạy đó. Cú pháp truy vấn con tiêu chuẩn được hỗ trợ rộng rãi và chúng tôi chỉ ra những điểm khác nhau của cơ sở dữ liệu.

Truy vấn phụ là gì?

Truy vấn con, còn được gọi là truy vấn bên trong hoặc truy vấn lồng nhau, là câu lệnh SELECT được đặt bên trong câu lệnh khác, trong dấu ngoặc tròn. Về mặt khái niệm, truy vấn bên trong tạo ra kết quả mà truy vấn bên ngoài sẽ sử dụng.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Các bảng mẫu

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Lưu ý rằng Dev không có đơn đặt hàng. Chi tiết đó quan trọng đối với một số ví dụ dưới đây.

Sơ lược về các loại truy vấn con

Truy vấn con vô hướng

Truy vấn con vô hướng trả về chính xác một giá trị, vì vậy bạn có thể sử dụng nó ở bất cứ nơi nào có một giá trị hợp lý. Đây là truy vấn từ trước đó, cùng với kết quả của nó. Trung bình của tất cả các đơn đặt hàng là 980:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

Bạn không thể viết WHERE total > AVG(total) vì các tổng hợp không được phép trực tiếp trong WHERE. Truy vấn con giải quyết vấn đề này bằng cách tính toán giá trị riêng biệt.

Điều gì sẽ xảy ra nếu truy vấn con “vô hướng” trả về một số hàng? Hầu hết các cơ sở dữ liệu, bao gồm PostgreSQL, MySQL và SQL Server, đều phát sinh lỗi. SQLite thì không. Trong thử nghiệm của chúng tôi, nó chỉ âm thầm sử dụng hàng đầu tiên, hàng này có thể ẩn một lỗi thực sự, vì vậy đừng phụ thuộc vào hành vi này.

TRONG và KHÔNG TRONG

Khi truy vấn bên trong trả về một danh sách, hãy sử dụng IN. Điều này tìm thấy khách hàng có ít nhất một đơn hàng trên 1000:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN làm ngược lại, tìm khách hàng chưa từng đặt hàng:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

Điều đó có hiệu quả ở đây, nhưng NOT IN có một trường hợp nguy hiểm mà chúng tôi sẽ đề cập bên dưới.

Truy vấn con trong TỪ

Truy vấn con trong FROM hoạt động như một bảng tạm thời, thường được gọi là bảng dẫn xuất. Tính năng này rất hữu ích khi bạn cần tổng hợp hai lần, như trong truy vấn này về mức chi tiêu trung bình cho mỗi khách hàng ở mỗi thành phố:

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

Truy vấn bên trong tính tổng từng khách hàng và truy vấn bên ngoài tính trung bình các tổng đó. Đặt bí danh cho bảng dẫn xuất là một thói quen tốt vì một số cơ sở dữ liệu yêu cầu điều đó. Nếu GROUP BY là mới đối với bạn, hãy xem SQL GROUP BY và HAVING.

Truy vấn con trong CHỌN

Truy vấn con vô hướng cũng có thể thêm cột được tính toán. Cái này tính số đơn đặt hàng cho mỗi khách hàng:

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev xuất hiện với con số 0. Phép nối bên trong sẽ loại bỏ hoàn toàn Dev vì Dev không có hàng phù hợp. Bạn có thể nhận được kết quả tương tự với LEFT JOIN và GROUP BY mà chúng tôi giải thích trong Giải thích về các phép nối SQL.

Truy vấn con tương quan

Ví dụ trước cũng có mối tương quan vì truy vấn bên trong đề cập đến c.id từ truy vấn bên ngoài. Truy vấn con không tương quan có thể chạy một mình. Không thể có mối tương quan vì nó phụ thuộc vào giá trị từ hàng ngoài hiện tại.

Điều này tìm thấy các đơn đặt hàng trên mức trung bình của chính khách hàng đó:

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

Đây là cách đọc nó. Đối với đơn hàng 101, truy vấn bên trong tính trung bình các đơn hàng của khách hàng 1 (1200 và 800), kết quả là 1000. Vì 1200 lớn hơn nên hàng được giữ nguyên. Đối với lệnh 102, áp dụng mức trung bình tương tự, nhưng 800 không lớn hơn nên bị loại bỏ. Đơn hàng của khách hàng 2 bằng mức trung bình của chính khách hàng đó nên đơn hàng đó cũng bị loại bỏ.

“Một lần trên mỗi hàng” mô tả logic và trình tối ưu hóa truy vấn thường viết lại những điều này thành thứ gì đó hiệu quả hơn nhiều. Tuy nhiên, một truy vấn con tương quan chậm vẫn đáng để nghiên cứu. Chỉ mục ở cột bên trong, ở đây orders.customer_id, có thể tạo ra sự khác biệt lớn. Xem Giải thích về chỉ mục SQL.

Tồn tại và KHÔNG Tồn tại

EXISTS đặt câu hỏi có hoặc không: truy vấn bên trong có trả về ít nhất một hàng không? Nó có thể dừng lại ở trận đấu đầu tiên, và những gì bạn chọn bên trong không quan trọng, đó là lý do tại sao người ta thường viết SELECT 1.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

Bẫy NOT IN NULL

Đây là một trong những lỗi truy vấn con phổ biến nhất. Chúng tôi đã tạo một bảng có một ID thực và một NULL, sau đó chạy cả hai biểu mẫu với nó:

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

Tại sao lần đầu tiên không trả lại gì? id NOT IN (1, NULL) có nghĩa là id <> 1 AND id <> NULL. Mọi so sánh với NULL đều không xác định chứ không phải đúng, vì vậy toàn bộ điều kiện không bao giờ có thể đúng. Không có thông báo lỗi và không có cảnh báo, chỉ là một kết quả trống có vẻ hợp lý.

Truy vấn con vs THAM GIA vs CTE

Nhiều truy vấn có thể được viết theo nhiều cách. Trước đó chúng tôi đã tìm thấy những khách hàng có đơn đặt hàng lớn bằng cách sử dụng IN. Đây là kết quả tương tự khi tham gia:

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

Lưu ý DISTINCT. Một phép nối có thể tạo ra các bản sao nếu khách hàng có nhiều kết quả trùng khớp, trong khi IN và EXISTS thì không thể.

Hiệu suất thay đổi tùy theo cơ sở dữ liệu và dữ liệu, đồng thời các trình tối ưu hóa hiện đại thường tạo ra các kế hoạch tương tự. Vì vậy, trước tiên hãy chọn hình thức rõ ràng nhất, sau đó kiểm tra EXPLAIN nếu nó chạy chậm.

Những lỗi thường gặp

• Sử dụng = với kết quả nhiều hàng. Sử dụng IN hoặc đảm bảo truy vấn bên trong trả về một hàng.
• Sử dụng NOT IN trên cột có thể rỗng. Bạn có thể âm thầm mất mọi hàng.
• Sử dụng bí danh không rõ ràng. Trong các truy vấn tương quan, hãy xác định rõ từng cột, nếu không nó có thể liên kết với bảng sai.
• Lồng nhau sâu. Ba cấp độ khó đọc, vì vậy hãy chuyển đổi chúng thành CTE.
• Thêm ORDER BY vào bên trong. Nó thường không ảnh hưởng đến kết quả bên ngoài và một số cơ sở dữ liệu từ chối nó.

Lời kết

Truy vấn con là một cách tự nhiên để diễn đạt các câu hỏi hai bước. Bắt đầu với các truy vấn con vô hướng và IN, sau đó thực hành các truy vấn tương quan bằng cách suy luận thông qua một hàng bên ngoài tại một thời điểm. Và hãy nhớ NOT EXISTS bất cứ khi nào có thể có NULL. Để thực hành, hãy viết một câu hỏi theo ba cách, với truy vấn phụ, nối và CTE, rồi so sánh cả kết quả và khả năng đọc.

Câu hỏi thường gặp

Tiếp tục học hỏi