Trino SQL Bài học 22: Kết nối dữ liệu giữ lại bảng gốc: LEFT, RIGHT và FULL OUTER JOIN

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

• Hiểu rõ sự khác biệt giữa phép nối chặt chẽ (INNER) và phép nối lỏng lẻo (OUTER).

• Nắm vững cách sử dụng LEFT JOIN để truy xuất toàn bộ dữ liệu gốc, bất chấp việc có dữ liệu đối chiếu hay không.

• Áp dụng kỹ thuật lọc IS NULL kết hợp với OUTER JOIN để tìm kiếm các bản ghi "mồ côi" (Anti-Join).

1. Bản chất của OUTER JOIN

Nếu INNER JOIN (Bài 21) là bộ lọc cực kỳ khắt khe chỉ giữ lại những gì khớp nhau ở cả hai bảng, thì OUTER JOIN mang tính chất "bao dung" hơn. Nó cho phép bạn ưu tiên giữ lại toàn bộ dữ liệu của một bảng (bảng gốc), và chỉ ghép thêm dữ liệu từ bảng kia vào nếu tìm thấy điểm chung.

Nơi nào không tìm thấy dữ liệu đối chiếu, Trino sẽ tự động điền giá trị NULL vào các cột đó.

2. LEFT JOIN (LEFT OUTER JOIN)

Đây là loại OUTER JOIN được sử dụng nhiều nhất (chiếm tới 90% các trường hợp dùng OUTER JOIN trong thực tế). Logic: Giữ lại TẤT CẢ các dòng của bảng nằm bên TRÁI chữ JOIN. Nếu bảng bên PHẢI có dòng khớp (dựa trên mệnh đề ON), dữ liệu sẽ được nối vào. Nếu không có, các cột của bảng PHẢI sẽ nhận giá trị NULL.

Ví dụ thực tế: Lấy danh sách toàn bộ khách hàng và tổng tiền họ đã chi tiêu. Nếu dùng INNER JOIN, những khách hàng mới đăng ký (chưa từng mua hàng) sẽ biến mất khỏi báo cáo. LEFT JOIN sẽ giải quyết vấn đề này.

SQL

SELECT 
    c.name, 
    c.mktsegment,
    o.orderkey, 
    o.totalprice
FROM tpch.tiny.customer AS c          -- Bảng TRÁI (Được ưu tiên giữ lại toàn bộ)
LEFT JOIN tpch.tiny.orders AS o       -- Bảng PHẢI (Chỉ nối vào nếu có)
    ON c.custkey = o.custkey;

Kết quả: Khách hàng nào chưa mua hàng sẽ có orderkey và totalprice mang giá trị NULL.

3. Kỹ thuật Anti-Join: Tìm kiếm mảnh vỡ mồ côi

Bằng cách kết hợp LEFT JOIN với mệnh đề WHERE ... IS NULL, bạn có thể tạo ra một bộ lọc Anti-Join để tìm ra những dòng dữ liệu KHÔNG có đối tác ở bảng kia. Đây là kỹ thuật "vàng" của Data Quality Analyst để tìm ra sự bất thường trong hệ thống.

Ví dụ: Tìm danh sách những khách hàng "đăng ký cho vui" (chưa từng tạo bất kỳ đơn hàng nào).

SQL

SELECT 
    c.name, 
    c.phone
FROM tpch.tiny.customer AS c
LEFT JOIN tpch.tiny.orders AS o 
    ON c.custkey = o.custkey
WHERE o.orderkey IS NULL; -- Lọc ra những người bị thiếu dữ liệu từ bảng Orders

4. RIGHT JOIN (RIGHT OUTER JOIN)

Logic: Ngược lại hoàn toàn với LEFT JOIN. Nó giữ lại toàn bộ dữ liệu của bảng bên PHẢI chữ JOIN và đính kèm dữ liệu bảng TRÁI nếu khớp.

SQL

-- Giữ lại tất cả đơn hàng, nối thông tin khách hàng vào
SELECT 
    c.name, 
    o.orderkey
FROM tpch.tiny.customer AS c
RIGHT JOIN tpch.tiny.orders AS o      -- Bảng PHẢI (Được ưu tiên)
    ON c.custkey = o.custkey;

Góc Kỹ sư: Trong thực tế, các lập trình viên và nhà phân tích rất hiếm khi dùng RIGHT JOIN. Lý do là con người có thói quen đọc từ trái sang phải, từ trên xuống dưới. Thay vì dùng RIGHT JOIN, người ta chỉ cần đảo vị trí hai bảng ở mệnh đề FROM và tiếp tục dùng LEFT JOIN để code dễ hiểu hơn.

5. FULL OUTER JOIN (Kết nối toàn phần)

Logic: Đây là phép "Hợp" (Union) của dữ liệu. Nó giữ lại TẤT CẢ các dòng từ CẢ HAI BẢNG. Nếu có dòng khớp nhau, chúng được gộp làm một. Nếu một dòng ở bảng TRÁI không có đối tác, nó đứng một mình (cột PHẢI bị NULL). Nếu một dòng ở bảng PHẢI không có đối tác, nó cũng đứng một mình (cột TRÁI bị NULL).

Ứng dụng: Thường dùng để đối soát (Reconciliation) dữ liệu giữa hai hệ thống độc lập.

Ví dụ: Đối soát hệ thống Kho hàng cũ và Kho hàng mới để xem sản phẩm nào chỉ có ở kho cũ, sản phẩm nào chỉ có ở kho mới, và sản phẩm nào có ở cả hai.

SQL

SELECT 
    old.product_id AS id_cu,
    new.product_id AS id_moi,
    old.stock,
    new.stock
FROM system_a.inventory AS old
FULL OUTER JOIN system_b.inventory AS new
    ON old.product_id = new.product_id;