INNER JOIN trong SQLite: kết hợp hàng từ nhiều bảng

Join ghép hai bảng lại với nhau

Cơ sở dữ liệu quan hệ cố ý chia dữ liệu ra nhiều bảng: khách hàng ở một bảng, đơn hàng ở bảng khác, sản phẩm ở bảng thứ ba. Nhờ vậy mỗi thông tin chỉ nằm ở một chỗ. Nhưng khi muốn trả lời một câu hỏi thực tế ("khách hàng nào đã đặt món gì?"), bạn phải ghép các mảnh đó lại. Đó chính là việc của join.

INNER JOIN là kiểu join được dùng nhiều nhất. Nó ghép cặp các hàng của hai bảng ở bất cứ đâu điều kiện khớp, và loại bỏ mọi hàng còn lại.

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

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

INSERT INTO customers (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 80.00);

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;

Ba khách hàng, ba đơn hàng, nhưng Chen chưa có đơn nào nên Chen không xuất hiện. Đó là ý nghĩa của chữ "inner": chỉ những hàng khớp mới được giữ lại.

Cách hình dung: ghép hàng rồi lọc

Hãy đọc một câu INNER JOIN như sau: lấy từng hàng của bảng thứ nhất, xét từng hàng của bảng thứ hai, và chỉ giữ cặp nào thỏa điều kiện ON. Về mặt khái niệm, đó là một phép tích chéo khổng lồ rồi lọc. Thực tế SQLite không làm đúng như vậy (nó dùng chỉ mục khi có thể), nhưng mô hình này đúng khi bạn cần đoán kết quả trả về.

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

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 1, 70), (12, 2, 15);

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

Vài thói quen nên tập ngay từ đây:

• Đặt bí danh cho bảng (customers AS c) khi bạn nhắc đến nó nhiều lần. Code sẽ gọn hơn.
• Ghi rõ bảng của cột (c.name, o.total) khi cả hai bảng đều có thể có cột đó.
• Thứ tự trong ON o.customer_id = c.id không quan trọng: c.id = o.customer_id cũng cho kết quả như nhau.

INNER JOIN và JOIN

Trong SQLite (và SQL chuẩn), JOIN đứng một mình nghĩa là INNER JOIN. Từ khóa INNER là tùy chọn.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Cả hai cách viết đều cho cùng một kế hoạch thực thi và cùng các hàng. Viết đầy đủ INNER JOIN giúp code dễ đọc hơn một chút khi có nhiều kiểu join: ý định sẽ rõ ràng khi đặt cạnh một LEFT JOIN ở vài dòng bên dưới.

ON và USING

Khi cột dùng để join có cùng tên ở cả hai bảng, USING (column) ngắn hơn ON a.col = b.col:

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

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT name, total
FROM customers
INNER JOIN orders USING (customer_id);

USING (customer_id) làm hai việc: ghép các hàng có customer_id bằng nhau, và gộp cột đó để nó chỉ xuất hiện một lần trong kết quả. Hãy dùng nó khi hai bên thật sự đặt cùng một tên. Giữ ON khi tên khác nhau (orders.customer_id = customers.id) hoặc khi điều kiện không chỉ là một phép so sánh bằng.

Nối ba bảng

Muốn nối nhiều bảng, bạn thêm các mệnh đề JOIN ... ON .... Mỗi mệnh đề nối kết quả hiện có với một bảng khác.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TABLE order_items (
    order_id INTEGER,
    product TEXT,
    quantity INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);
INSERT INTO order_items VALUES (10, 'Notebook', 2), (10, 'Pen', 5), (11, 'Mug', 1);

SELECT c.name, o.id AS order_id, i.product, i.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;

Đọc từ trên xuống: khách hàng nối với đơn hàng, đơn hàng nối với các mặt hàng. Mỗi hàng trong kết quả là một tổ hợp khách hàng, đơn hàng và mặt hàng. Hàng nào thiếu cặp khớp ở bất kỳ bước nào trong chuỗi đều bị loại: đó là quy tắc của inner join áp dụng ở từng bước.

Lọc bằng WHERE

ON cho biết cách ghép cặp các hàng. WHERE lọc kết quả sau khi ghép. Riêng với inner join, đặt thêm điều kiện ở ON hay ở WHERE đều cho cùng các hàng, nhưng quy ước chung là để điều kiện join trong ON và điều kiện lọc hàng trong WHERE.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada', 'UK'), (2, 'Boris', 'FR'), (3, 'Chen', 'UK');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 80), (12, 3, 12);

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.total > 20;

Câu này đọc là "nối khách hàng với đơn hàng, rồi chỉ giữ khách hàng ở UK có đơn trên 20". Hai vai trò, hai mệnh đề: sau này đọc lại bạn sẽ thấy rất dễ hiểu. (Khi bắt đầu viết LEFT JOIN, sự khác nhau giữa ON và WHERE không còn chỉ là chuyện hình thức nữa, nhưng đó là nội dung của trang tiếp theo.)

Nhiều điều kiện trong ON

ON có thể chứa bất kỳ biểu thức boolean nào, không chỉ một phép so sánh bằng. Điều này hữu ích khi quan hệ giữa hai bảng dựa trên nhiều cột, hoặc khi bạn muốn lọc bảng bên phải ngay lúc join.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES
    (10, 1, 30, 'paid'),
    (11, 1, 50, 'cancelled'),
    (12, 2, 15, 'paid');

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id
   AND o.status = 'paid';

Đơn hàng đã hủy biến mất vì điều kiện thứ hai không thỏa. Với inner join, bạn cũng có thể viết WHERE o.status = 'paid' và nhận cùng kết quả. Cách viết trong ON giữ logic "thế nào là khớp" ở ngay cạnh phép join.

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

Vài điều hay khiến người mới vấp:

• Quên mệnh đề ON. FROM a INNER JOIN b mà không có ON là lỗi cú pháp trong SQLite. (Một dấu phẩy đơn thuần, FROM a, b, thì vẫn chạy được, tạo ra cross join, và gần như không bao giờ là thứ bạn muốn.)
• Hàng trùng lặp ngoài dự kiến. Nếu một khách hàng có ba đơn hàng, tên khách hàng đó xuất hiện ba lần trong kết quả. Đó là hành vi đúng của join, không phải bug. Hãy gộp bằng GROUP BY nếu bạn muốn mỗi khách hàng một hàng.
• Thiếu hàng. Nếu một khách hàng lẽ ra phải xuất hiện nhưng lại không có, nghĩa là điều kiện join không khớp: hãy kiểm tra NULL trong các cột join, hoặc chuyển sang LEFT JOIN.
• Tên cột mơ hồ. SELECT id FROM customers JOIN orders ON ... báo lỗi vì cả hai bảng đều có cột id. Hãy ghi rõ: c.id hoặc o.id.

Tiếp theo: LEFT JOIN

INNER JOIN rất phù hợp khi không khớp nghĩa là "bỏ qua hàng này". Nhưng đôi khi bạn muốn liệt kê mọi khách hàng, kể cả những người chưa có đơn hàng, với NULL thay cho dữ liệu còn thiếu. Đó là LEFT JOIN, nội dung của trang tiếp theo.