Menu

JOIN trong PostgreSQL: INNER, LEFT, FULL, CROSS và LATERAL

Join kết hợp các hàng của hai bảng theo một điều kiện. INNER JOIN giữ các hàng khớp, LEFT JOIN giữ mọi hàng của bảng thứ nhất, FULL JOIN giữ cả hai, CROSS JOIN ghép mọi thứ, và LATERAL chạy một truy vấn con cho mỗi hàng.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

Join dựng các hàng từ hai bảng, ghép theo một điều kiện: SELECT ... FROM a JOIN b ON a.id = b.a_id. JOIN (INNER JOIN) chỉ giữ những hàng khớp, LEFT JOIN giữ mọi hàng của bảng thứ nhất, và FULL JOIN giữ mọi hàng của cả hai bảng.

Kết quả có ba hàng. Grace không có đơn hàng nào, còn đơn hàng 13 thuộc về một khách hàng không tồn tại, nên cả hai đều bị loại. Các ví dụ join bên dưới dùng cùng hai bảng này.

Các loại join

JoinCác hàng trả về
INNER JOINCác cặp khớp với điều kiện
LEFT JOINMọi hàng bên trái; cột bên phải là NULL khi không có gì khớp
RIGHT JOINMọi hàng bên phải; ngược lại với LEFT JOIN
FULL JOINMọi hàng của cả hai phía, NULL lấp vào chỗ trống
CROSS JOINMỗi hàng bên trái ghép với mọi hàng bên phải

LEFT JOIN: giữ lại hàng không có cặp khớp

Grace xuất hiện với 0 đơn hàng. Hãy đếm o.id, không phải *: count(*) sẽ tính hàng toàn NULL của Grace là 1.

  • Hàng không có cặp khớp (anti-join): thêm WHERE o.id IS NULL để chỉ giữ những khách hàng không có đơn hàng. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) làm cùng việc đó và nói rõ ý định hơn.
  • ON và WHERE: điều kiện trên bảng bên phải nên nằm trong ON. LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20 loại mất Grace, vì total NULL của cô không qua được WHERE. Join lúc này đã thành inner join. Hãy viết ON o.customer_id = c.id AND o.total > 20 để giữ mọi khách hàng.

FULL JOIN: các hàng bị thiếu ở một trong hai phía

FULL JOIN tìm ra chỗ không khớp theo cả hai chiều, rất có ích khi so sánh hai danh sách:

Kết quả có Grace không có đơn hàng và đơn hàng 13 không có khách hàng. Một khóa ngoại trên orders.customer_id đã có thể ngăn hàng thứ hai.

CROSS JOIN: mọi tổ hợp

CROSS JOIN không có điều kiện. Nó ghép mọi hàng của một phía với mọi hàng của phía kia, nên 3 kích cỡ và 2 màu cho ra 6 hàng:

Hai danh sách ở đây là các danh sách VALUES được đặt tên như bảng. Với bảng thật, câu lệnh là FROM sizes CROSS JOIN colors, và FROM sizes, colors cũng tương tự. Một join có điều kiện ON luôn đúng cũng nhân số hàng lên theo cùng cách, một nguyên nhân hay gặp khiến truy vấn bỗng trả về quá nhiều hàng.

LATERAL: một truy vấn con cho mỗi hàng

Một truy vấn con trong FROM thường không nhìn thấy các bảng khác trong cùng FROM. Với LATERAL thì có, và nó được tính một lần cho mỗi hàng của bảng đứng trước nó. Cách này giải quyết bài toán "N hàng đầu mỗi nhóm":

Ada có hai đơn hàng lớn nhất, Linus có một đơn của anh ấy, còn Grace có một hàng toàn NULL nhờ LEFT JOIN ... ON true. CROSS JOIN LATERAL sẽ loại những khách hàng không có đơn hàng. Với một index trên orders (customer_id, total), mỗi truy vấn con chỉ đọc vài mục trong index. Khi cần một hàng cho mỗi nhóm, DISTINCT ON thường ngắn gọn hơn.

Câu hỏi thường gặp

JOIN có giống INNER JOIN trong PostgreSQL không?

Có. JOIN đứng một mình nghĩa là INNER JOIN, và LEFT JOIN nghĩa là LEFT OUTER JOIN; các từ khóa INNER và OUTER có thể bỏ.

Vì sao LEFT JOIN trả về nhiều hàng hơn bảng bên trái?

Một hàng bên trái xuất hiện một lần cho mỗi hàng khớp bên phải. Một khách hàng có ba đơn hàng sẽ xuất hiện ba lần. Hãy tổng hợp bằng GROUP BY, hoặc join với một truy vấn con đã có sẵn một hàng cho mỗi khóa.

ON và USING khác nhau thế nào?

ON nhận bất kỳ điều kiện nào. USING (id) là cách viết tắt cho các cột bằng nhau có cùng tên ở cả hai bảng, và kết quả chỉ có một cột id thay vì hai.

Thứ tự join có ảnh hưởng tới hiệu năng trong PostgreSQL không?

Thường là không. Bộ lập kế hoạch sắp xếp lại các inner join để tìm kế hoạch rẻ khi truy vấn có tối đa join_collapse_limit (mặc định 8) bảng. Outer join chỉ sắp xếp lại được trong một số trường hợp, vì di chuyển chúng có thể làm thay đổi kết quả.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu