Subquery trong SQLite: SELECT lồng trong WHERE, FROM, SELECT

Subquery là một câu SELECT bên trong câu SELECT

Subquery (truy vấn con) đúng như tên gọi: một câu lệnh SELECT nằm gọn bên trong một câu lệnh khác, đặt trong dấu ngoặc đơn. SQLite chạy truy vấn bên trong, lấy kết quả rồi đưa cho truy vấn bên ngoài.

Hãy tạo một ví dụ nhỏ để dùng lại nhiều lần:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders;

Năm đơn hàng, bốn khách hàng, trong đó có một khách chưa đặt gì. Chúng ta sẽ dùng dữ liệu này xuyên suốt trang.

Subquery trong WHERE: lọc theo một danh sách

Dạng phổ biến nhất: lấy danh sách id bằng truy vấn bên trong, rồi lọc truy vấn bên ngoài theo danh sách đó.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Truy vấn bên trong tạo ra mọi customer_id có trong orders. Truy vấn bên ngoài chỉ giữ lại các khách hàng có id nằm trong danh sách đó. Cleo, Boris và Ada xuất hiện; Dmitri (không có đơn hàng) thì không.

IN (SELECT ...) là mẫu chủ lực cho câu hỏi "những hàng trong A có hàng khớp trong B". Hãy hình dung nó là "khi giá trị của cột này là một trong các giá trị mà truy vấn bên trong trả về".

NOT IN: cẩn thận với NULL

Câu hỏi ngược lại, "khách hàng nào chưa đặt hàng?", chỉ cách một dòng:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Ở đây nó chạy đúng. Nhưng NOT IN có một cạnh sắc: nếu subquery trả về một NULL nào đó, toàn bộ NOT IN trở thành NULL (không phải TRUE), và bạn nhận về không hàng nào. Vừa bất ngờ vừa im lặng.

Thói quen an toàn khi dùng NOT IN với một cột có thể chứa NULL:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Hoặc dùng NOT EXISTS, vốn hoàn toàn không gặp vấn đề này. Chúng ta sẽ nói đến nó ngay sau đây.

Subquery vô hướng: một hàng, một cột

Một subquery vô hướng (scalar) trả về một giá trị duy nhất, tức một hàng và một cột, và bạn có thể dùng nó ở bất kỳ đâu cần một giá trị.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);

SELECT MAX(total) FROM orders bên trong trả về 200. Truy vấn bên ngoài sau đó lọc các đơn hàng khớp với giá trị đó. Rất hữu ích mỗi khi bạn cần so sánh với một giá trị tổng hợp.

Bạn cũng có thể dùng subquery vô hướng trong danh sách SELECT để gắn một giá trị tính toán vào mỗi hàng:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Mỗi hàng của customers chạy truy vấn bên trong một lần, với customers.id được thay vào. Đó là một subquery tương quan, sẽ nói rõ hơn ở bên dưới. Với những trường hợp "một con số cho mỗi hàng" như thế này, LEFT JOIN kèm GROUP BY thường cho hiệu năng tốt hơn, nhưng dạng vô hướng đọc rất dễ hiểu.

EXISTS: chỉ kiểm tra xem có gì khớp không

EXISTS là người anh em kín đáo hơn của IN. Nó không quan tâm đến giá trị, chỉ kiểm tra xem subquery có trả về bất kỳ hàng nào không. Thường bạn viết SELECT 1 bên trong, vì cột nào cũng không quan trọng.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Truy vấn này tìm các khách hàng đã đặt ít nhất một đơn trên 100. Truy vấn bên trong tham chiếu c.id từ truy vấn bên ngoài, đó là điều khiến nó trở thành subquery tương quan. SQLite dừng quét bảng bên trong ngay khi tìm thấy một hàng khớp, đó là lý do EXISTS thường nhanh hơn IN với câu hỏi "hàng này có hàng liên quan không?".

Phủ định của nó, NOT EXISTS, là cách an toàn với NULL để hỏi "không có hàng liên quan nào":

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

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

Subquery trong FROM: bảng dẫn xuất

Subquery có thể thay thế ở bất cứ đâu một bảng có thể xuất hiện, kể cả mệnh đề FROM. Truy vấn bên trong trở thành một "bảng dẫn xuất" (derived table) tạm thời có tên, mà bạn có thể join, lọc hoặc tổng hợp.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.total) AS customer_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS per_customer
GROUP BY country;

Truy vấn bên trong tính tổng tiền của từng khách hàng. Truy vấn bên ngoài lấy trung bình các tổng đó theo từng quốc gia. Tổng hợp hai giai đoạn như thế này chính là việc bảng dẫn xuất sinh ra để làm, khi bạn không thể làm mọi thứ trong một GROUP BY.

Alias AS per_customer là bắt buộc: mỗi bảng dẫn xuất đều cần có tên.

Subquery tương quan: chạy cho từng hàng bên ngoài

Một subquery là tương quan khi nó tham chiếu đến một cột của truy vấn bên ngoài. SQLite phải tính lại truy vấn bên trong cho mỗi hàng bên ngoài, điều này linh hoạt nhưng có thể tốn kém.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name,
       (SELECT MAX(total)
        FROM orders
        WHERE customer_id = customers.id) AS biggest_order
FROM customers;

Với mỗi khách hàng, tìm đơn hàng lớn nhất của họ. Truy vấn bên trong phụ thuộc vào customers.id, nên nó chạy một lần cho mỗi khách hàng. Khách hàng không có đơn nào nhận NULL, đúng như bạn mong muốn.

Subquery tương quan hợp tự nhiên với câu hỏi "với mỗi hàng trong A, tính một thứ gì đó từ B". Nếu bảng nhỏ hoặc phép tra cứu có index, chúng hoàn toàn ổn. Trên bảng lớn không có index hỗ trợ, hãy đo hiệu năng trước khi đưa lên production: JOIN kèm GROUP BY thường nhanh hơn.

Subquery hay JOIN: chọn cái nào?

Hai truy vấn này trả lời cùng một câu hỏi:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Subquery
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

Cả hai trả về cùng các hàng. Bộ tối ưu của SQLite thường tự viết lại dạng này thành dạng kia ở bên trong. Hãy chọn dựa trên độ dễ đọc:

• Dùng subquery khi bạn chỉ cần lọc và không muốn các cột của bảng bên trong làm rối kết quả.
• Dùng JOIN khi kết quả cần cột từ cả hai bảng.
• Dùng EXISTS khi bạn hỏi "có ít nhất một hàng liên quan không?": nó rõ ràng hơn và tránh được các bẫy NULL của IN/NOT IN.

Khi phân vân, hãy viết phiên bản tự giải thích được khi đọc thành lời.

Một lỗi thường gặp: subquery trả về nhiều hàng

Một subquery dùng với = phải trả về tối đa một hàng. Nếu trả về nhiều hơn, SQLite chọn một hàng (về cơ bản là ngẫu nhiên) và bạn nhận kết quả sai mà không hề có thông báo lỗi.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Risky: what if more than one customer is from the UK?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

Dùng IN khi truy vấn bên trong có thể trả về nhiều hàng:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

Nếu bạn kỳ vọng đúng một hàng và muốn bảo đảm điều đó, hãy thêm LIMIT 1 và một ORDER BY để ít nhất lựa chọn là xác định. Tốt hơn nữa: viết truy vấn sao cho dữ liệu bảo đảm chỉ có một hàng (lọc theo một cột duy nhất).

Tiếp theo: Common Table Expression

Subquery trong FROM nhanh chóng trở nên khó quản lý, nhất là khi bạn cần cùng một bảng dẫn xuất hai lần, hoặc khi lồng sâu đến ba cấp. Common Table Expression (WITH ... AS (...)) cho phép bạn đặt tên cho một subquery ngay từ đầu và tham chiếu đến nó bằng tên trong phần còn lại của câu lệnh. Đó là nội dung của trang tiếp theo.