Điều kiện bổ sung trong JOIN: ON ... AND ...

Mày chắc cũng biết cách kết nối bảng bằng JOIN rồi nhỉ. Nhưng thực tế thì chỉ so khớp theo key thôi thì chưa đủ đâu. Nhiều lúc cần phải join dữ liệu chỉ khi nó thỏa mãn thêm điều kiện nào đó — ví dụ chỉ lấy record active, chỉ lấy data năm nay hoặc chỉ lấy đơn hàng đã hoàn thành.

Và đây là lúc mở rộng cú pháp ON với AND phát huy tác dụng.

Điều kiện bổ sung trong JOIN ... ON giúp kiểm soát chính xác dòng nào sẽ được join, ngay trước khi SQL build kết quả. Như vậy query sẽ:

• Nhanh hơn (ít dòng phải đi qua JOIN hơn),
• Chính xác hơn (lọc ngay lúc join),
• Dễ đoán hơn khi dùng LEFT JOIN (khác với filter ở WHERE).

Ví dụ: Chỉ lấy record đăng ký khóa học đang active

Giả sử bảng enrollments có trạng thái tham gia của sinh viên: active, dropped, pending.

Bảng students:

Cập nhật bảng enrollments:

Bảng courses:

Bây giờ mình muốn lấy chỉ sinh viên có khóa học active thôi:

SELECT 
    students.name AS student_name,
    courses.name AS course_name
FROM students 
INNER JOIN enrollments 
    ON students.id = enrollments.student_id
    AND enrollments.status = 'active'
INNER JOIN courses 
    ON enrollments.course_id = courses.id;

Kết quả:

Ở đây mình thêm AND enrollments.status = 'active' vào trong ON, để chỉ join record active, chứ không phải lọc sau khi đã join xong.

Tại sao không dùng WHERE?

Có thể viết như này:

...
WHERE enrollments.status = 'active'

Nhưng nó sẽ khác khi dùng LEFT JOIN. Filter ở WHERE sẽ xóa dòng không có match (NULL), và biến LEFT JOIN thành INNER JOIN luôn.

Còn nếu để AND enrollments.status = 'active' trong ON thì nó sẽ giới hạn luôn dòng được join — kiểm soát dòng nào được join, chứ không chỉ filter kết quả sau đó.

Cách này rất quan trọng nếu mày muốn giữ lại dòng từ một bảng, kể cả khi bảng kia không có giá trị phù hợp (hay gặp trong report, analytic).

Thêm ví dụ dùng ON ... AND ...

Bảng students:

Bảng enrollments:

Bảng courses:

Ví dụ: chỉ lấy khóa học năm hiện tại

SELECT
    students.name,
    courses.name,
    enrollments.enrolled_at
FROM students
JOIN enrollments
    ON students.id = enrollments.student_id
    AND EXTRACT(YEAR FROM enrollments.enrolled_at) = EXTRACT(YEAR FROM CURRENT_DATE)
JOIN courses
    ON enrollments.course_id = courses.id;

Ở đây chỉ join record thuộc năm hiện tại thôi.

Ví dụ: loại trừ theo giá trị

JOIN enrollments
    ON students.id = enrollments.student_id
    AND enrollments.status != 'dropped'

Loại trừ sinh viên đã bỏ học ngay lúc join, chứ không phải filter sau.

Khi để điều kiện trong ON, PostgreSQL có thể tối ưu hóa plan join và xử lý ít dòng hơn. Cực kỳ quan trọng khi data lớn. Lọc bên trong hiệu quả hơn là "lọc sau" JOIN.

JOIN ON — không chỉ là key

Nhiều người nghĩ ON chỉ là id = id. Thực ra có thể nhét vào đó:

• Toán tử logic: AND, OR, NOT
• So sánh: >, <, <>, BETWEEN, IN
• Biểu thức: EXTRACT, DATE_TRUNC, COALESCE, NULLIF

Kết hợp tất cả lại

Bảng students:

Bảng faculties:

Bảng courses:

Bảng enrollments:

SELECT
    s.name AS student_name,
    c.name AS course_name,
    f.name AS faculty_name
FROM students s
JOIN enrollments e
    ON s.id = e.student_id
    AND e.status = 'active'
JOIN courses c
    ON e.course_id = c.id
    AND c.name != 'Fizkultura'
JOIN faculties f
    ON c.faculty_id = f.id
    AND f.name IS NOT NULL;

Ở đây mình filter cùng lúc theo:

• Record active,
• Khóa học không phải "Fizkultura",
• Khoa có tên.

Kết quả query:

Hy vọng mày thích bài giảng này nha. Mày sẽ dùng nhiều JOIN với filter trong query lắm luôn. Gần như lúc nào cũng thế :)