Trino SQL Bài học 24: Điều kiện JOIN phức tạp và Non-Equi JOIN

Mục tiêu bài học:

• Thoát khỏi tư duy chỉ dùng dấu bằng (=) khi kết nối các bảng.

• Ứng dụng Non-Equi JOIN để giải quyết bài toán phân dải dữ liệu (Range Matching).

• Nhận diện cạm bẫy kinh điển: Sự khác biệt giữa mệnh đề ON và WHERE trong OUTER JOIN.

• Nắm vững kiến thức chuyên sâu về Hash Join vs Nested Loop Join để tối ưu hiệu năng trên hệ thống phân tán.

1. Non-Equi JOIN là gì?

Từ Bài 21 đến Bài 23, chúng ta luôn kết nối các bảng dựa trên điều kiện bằng nhau, ví dụ: ON A.id = B.id. Kỹ thuật này gọi là Equi JOIN.

Tuy nhiên, SQL cho phép bạn sử dụng bất kỳ toán tử logic nào trong mệnh đề ON. Khi bạn sử dụng các toán tử không phải dấu bằng (như >, <, >=, <=, BETWEEN, <>), phép nối đó được gọi là Non-Equi JOIN.

2. Ứng dụng thực tế: Bài toán Phân dải (Range Matching)

Giả sử bạn có một bảng orders chứa doanh thu, và một bảng reward_tiers chứa chính sách hạng thành viên (Vàng, Bạc, Đồng) với các mức ngưỡng (min, max). Bạn không thể dùng dấu = để nối doanh thu chính xác vào từng ngưỡng được.

Đây là lúc Non-Equi JOIN (cụ thể là toán tử BETWEEN) tỏa sáng.

SQL

-- Dùng CTE (mệnh đề WITH) để tạo một bảng chính sách giả lập 
-- (Chúng ta sẽ học sâu về CTE ở Bài 38, tạm hiểu đây là một bảng tạm)
WITH reward_tiers AS (
    SELECT 'Bronze' AS tier_name, 0 AS min_val, 100000 AS max_val 
    UNION ALL
    SELECT 'Silver', 100001, 200000 
    UNION ALL
    SELECT 'Gold', 200001, 9999999
)
SELECT 
    o.orderkey, 
    o.totalprice, 
    r.tier_name
FROM tpch.tiny.orders AS o
JOIN reward_tiers AS r
    -- Phép nối Non-Equi JOIN: Tổng giá trị đơn hàng rơi vào khoảng nào thì nối với hạng đó
    ON o.totalprice BETWEEN r.min_val AND r.max_val
LIMIT 10;

3. Ghép nối nhiều điều kiện phức tạp (Complex ON)

Mệnh đề ON không bị giới hạn ở một điều kiện duy nhất. Bạn có thể sử dụng AND, OR để tạo ra các logic kết nối cực kỳ phức tạp.

Ví dụ: Bạn muốn tìm những khách hàng có số dư tài khoản (acctbal) âm, và muốn xem họ đã đặt những đơn hàng nào mà trị giá đơn hàng còn lớn hơn cả số dư hiện tại của họ (dấu hiệu rủi ro tín dụng).

SQL

SELECT 
    c.name, 
    c.acctbal, 
    o.orderkey, 
    o.totalprice
FROM tpch.tiny.customer AS c
JOIN tpch.tiny.orders AS o
    -- Điều kiện 1: Phép Equi JOIN cơ bản để đúng người đúng đơn
    ON c.custkey = o.custkey 
    -- Điều kiện 2: Phép Non-Equi JOIN để đánh giá rủi ro
    AND o.totalprice > c.acctbal 
WHERE c.acctbal < 0;

4. Cạm bẫy kinh điển: ON vs WHERE trong LEFT JOIN

Đây là lỗi logic mà 80% người viết SQL mắc phải khi mới làm quen với LEFT JOIN. Bạn cần nhớ luật này:

• Trong INNER JOIN, viết điều kiện ở ON hay WHERE đều cho ra kết quả giống nhau.

• Nhưng trong LEFT JOIN, ON và WHERE mang ý nghĩa hoàn toàn khác biệt!

Bài toán: Lấy ra TẤT CẢ khách hàng. Nếu khách hàng có đơn hàng trị giá > 200,000 thì hiển thị, không thì để NULL.

Cách viết SAI LOGIC (Bộ lọc ở WHERE):

SQL

SELECT c.name, o.orderkey, o.totalprice
FROM tpch.tiny.customer AS c
LEFT JOIN tpch.tiny.orders AS o ON c.custkey = o.custkey
WHERE o.totalprice > 200000;

Tại sao sai? LEFT JOIN cố gắng giữ lại toàn bộ khách hàng. Nhưng sau khi nối xong, mệnh đề WHERE lao vào quét kết quả. Những người không mua hàng (bị NULL) hoặc mua dưới 200,000 sẽ bị màng lọc WHERE vứt bỏ hoàn toàn. Câu LEFT JOIN lúc này bị "thoái hóa" và hoạt động y hệt như INNER JOIN.

Cách viết ĐÚNG LOGIC (Bộ lọc ở ON):

SQL

SELECT c.name, o.orderkey, o.totalprice
FROM tpch.tiny.customer AS c
LEFT JOIN tpch.tiny.orders AS o 
    ON c.custkey = o.custkey 
    AND o.totalprice > 200000; -- Điều kiện gắn thẳng vào quá trình JOIN

Tại sao đúng? Trino sẽ hiểu là: "Tôi vẫn giữ lại tất cả khách hàng (do LEFT JOIN), nhưng tôi chỉ nối thông tin Đơn hàng vào nếu đơn đó > 200,000". Khách hàng không thỏa mãn vẫn hiện tên, và cột đơn hàng bị NULL (Đúng yêu cầu).

5. Góc nhìn Kỹ sư: Cẩn thận với "Nested Loop Join"

Khi viết SQL cho các hệ quản trị truyền thống (như MySQL với vài chục nghìn dòng), Non-Equi JOIN chạy rất bình thường. Nhưng trên kiến trúc phân tán hàng tỷ dòng của Trino, nó có thể là thảm họa.

• Hash Join (Siêu nhanh): Khi bạn dùng dấu =, Trino dùng thuật toán Hash Join. Nó băm các giá trị thành mã Hash, phân phối các dữ liệu khớp nhau về cùng một Worker để nối. Quá trình này diễn ra song song và cực kỳ nhanh.

• Nested Loop Join - NLJ (Siêu chậm): Khi bạn CHỈ dùng <, >, BETWEEN mà không có dấu = nào, Trino không thể băm dữ liệu. Nó bắt buộc phải dùng thuật toán NLJ: Trino engine sẽ lấy từng dòng của bảng này, quét đối chiếu với toàn bộ các dòng của bảng kia. Quá trình này về cơ bản gần giống một CROSS JOIN (Bài 23), tiêu tốn lượng CPU và thời gian khổng lồ.

Best Practice từ chuyên gia Data Engineer: Khi viết mệnh đề ON phức tạp, LUÔN LUÔN cố gắng có ít nhất một điều kiện Equi JOIN (dấu =) đặt làm mỏ neo. Nếu bạn có một điều kiện =, Trino sẽ kích hoạt Hash Join trước để lọc tập dữ liệu nhỏ lại, sau đó mới áp dụng các điều kiện >, < lên tập kết quả nhỏ đó, giúp truy vấn chạy nhanh hơn hàng trăm lần.