Kết hợp dữ liệu đầy đủ với FULL OUTER JOIN

Hôm nay tụi mình sẽ tìm hiểu về kiểu kết hợp dữ liệu "dân chủ" nhất – FULL OUTER JOIN. Kiểu này ai cũng được vào kết quả, kể cả không có cặp tương ứng.

FULL OUTER JOIN là kiểu kết hợp dữ liệu mà nó trả về tất cả các dòng từ cả hai bảng. Nếu một dòng ở một bảng không có dòng tương ứng ở bảng kia, thì các giá trị thiếu sẽ được điền bằng NULL. Nó giống như ghi nhận tất cả mọi người đã đến hai bữa tiệc khác nhau: dù ai chỉ đến một bữa thì vẫn được tính.

Nhìn trực quan thì nó như này:

Bảng A                        Bảng B
+----+----------+             +----+----------+
| id | tên      |             | id | khóa_học |
+----+----------+             +----+----------+
| 1  | Alice    |             | 2  | Toán     |
| 2  | Bob      |             | 3  | Vật lý   |
| 4  | Charlie  |             | 5  | Lịch sử  |
+----+----------+             +----+----------+

FULL OUTER JOIN KẾT QUẢ:
+----+----------+----------+
| id | tên      | khóa_học |
+----+----------+----------+
| 1  | Alice    | NULL     |
| 2  | Bob      | Toán     |
| 3  | NULL     | Vật lý   |
| 4  | Charlie  | NULL     |
| 5  | NULL     | Lịch sử  |
+----+----------+----------+

Những dòng không có cặp vẫn được giữ lại, nhưng dữ liệu ở các cột thiếu sẽ là NULL.

Cú pháp FULL OUTER JOIN

Cú pháp thì đơn giản thôi, nhưng sức mạnh thì không đùa được đâu:

SELECT
    cột
FROM 
    bảng1
FULL OUTER JOIN 
    bảng2
ON bảng1.cột_chung = bảng2.cột_chung;

Phần quan trọng ở đây là FULL OUTER JOIN, nó bắt PostgreSQL lấy tất cả các dòng từ cả hai bảng. Nếu dòng nào không có cặp theo điều kiện ON, giá trị sẽ thành NULL.

Ví dụ sử dụng

Cùng xem ví dụ thực tế với database university quen thuộc, có hai bảng students và enrollments.

Ví dụ 1: danh sách tất cả sinh viên và khóa học

Giả sử mình có hai bảng:

Bảng students:

Bảng enrollments:

Nhiệm vụ là tạo danh sách đầy đủ sinh viên và khóa học, kể cả sinh viên chưa đăng ký khóa nào và khóa học không có sinh viên.

Đây là câu truy vấn:

SELECT
    s.student_id, 
    s.tên, 
    e.khóa_học
FROM 
    students s
FULL OUTER JOIN 
    enrollments e
ON 
    s.student_id = e.student_id;

Kết quả:

Nhìn nè, tất cả sinh viên và khóa học đều có mặt trong kết quả. Sinh viên Charlie chưa đăng ký khóa nào nên khóa_học là NULL. Còn khóa Lịch sử không có sinh viên nên student_id và tên là NULL.

Ví dụ 2: Phân tích bán hàng và sản phẩm

Giờ nghĩ về một cửa hàng. Có hai bảng:

Bảng products:

Bảng sales:

Mình muốn lấy danh sách đầy đủ tất cả sản phẩm và bán hàng, kể cả sản phẩm chưa bán và bán hàng với product_id không hợp lệ.

Truy vấn:

SELECT
    p.product_id,
    p.tên AS tên_sản_phẩm,
    s.số_lượng
FROM 
    products p
FULL OUTER JOIN 
    sales s
ON 
    p.product_id = s.product_id;

Kết quả:

Ở đây thấy Smartphone chưa bán (số_lượng = NULL), còn bán hàng với product_id = 4 thì không khớp sản phẩm nào.

Bài tập thực hành

Thử viết truy vấn cho hai bảng departments và employees nhé:

Bảng departments:

Bảng employees:

Viết FULL OUTER JOIN để lấy danh sách đầy đủ phòng ban và nhân viên. Điền dữ liệu thiếu bằng NULL.

Làm sao xử lý giá trị NULL

Vấn đề NULL là điều không tránh khỏi khi dùng FULL OUTER JOIN. Ví dụ, trong thực tế bạn có thể muốn thay NULL bằng giá trị dễ hiểu hơn. Trong PostgreSQL, dùng hàm COALESCE() là chuẩn bài.

Ví dụ:

SELECT
    COALESCE(s.tên, 'Không có sinh viên') AS tên_sinh_viên,
    COALESCE(e.khóa_học, 'Không có khóa học') AS tên_khóa_học
FROM 
    students s
FULL OUTER JOIN 
    enrollments e
ON 
    s.student_id = e.student_id;

Kết quả:

Giờ thì thay vì NULL là các giá trị dễ hiểu hơn, giúp báo cáo dễ đọc hơn nhiều.

Khi nào dùng FULL OUTER JOIN

FULL OUTER JOIN hữu ích khi bạn cần thấy tất cả dữ liệu từ hai bảng, kể cả khi không liên kết hết. Ví dụ:

• Báo cáo bán hàng và sản phẩm – để thấy cả sản phẩm đã bán và chưa bán.
• Phân tích sinh viên và khóa học – để kiểm tra có dữ liệu nào bị bỏ sót không.
• So sánh danh sách – ví dụ để phát hiện sự khác biệt giữa hai bộ dữ liệu.

Hy vọng bài này giúp bạn hiểu rõ về FULL OUTER JOIN. Giờ thì chuẩn bị bước vào thế giới kết hợp dữ liệu và xử lý dữ liệu phức tạp hơn nhé!