Minh họa các kiểu JOIN trong SQL bằng dữ liệu chạy thật, không phải ảnh tĩnh
Công cụ cho bạn chọn tám kiểu nối bảng, xem sơ đồ Venn tương ứng, đọc câu SQL mẫu và quan trọng nhất là chạy thật phép nối đó trên hai bảng dữ liệu mà bạn sửa được ngay trên trang. Bảng kết quả chỉ rõ dòng nào được giữ, dòng nào bị loại và ô nào trở thành NULL.
Tính năng nổi bật
- Tám kiểu nối: INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF cùng hai biến thể loại trừ
- Sơ đồ Venn vẽ bằng SVG, tô đúng vùng dữ liệu mà kiểu nối đang chọn giữ lại
- Câu SQL mẫu sinh theo đúng tên bảng và tên cột khóa bạn đang đặt, chép được một nút
- Hai bảng dữ liệu mẫu sửa trực tiếp trên trang, thêm bớt dòng và cột tùy ý
- Phép nối chạy thật trên dữ liệu bạn nhập, bảng kết quả đánh dấu rõ từng ô NULL
- Chọn cột khóa cho từng bên, riêng SELF JOIN có hai ô chọn cột trỏ lên và cột được trỏ tới
- Bốn số đếm theo thời gian thực: dòng bảng trái, dòng bảng phải, dòng kết quả và số dòng chứa NULL
- Ô khóa để trống được xử lý đúng như NULL trong SQL, tức là không khớp với bất kỳ giá trị nào
Sơ đồ Venn giải thích được ý tưởng nhưng không giải thích được dữ liệu
Gần như mọi bài viết về JOIN đều mở đầu bằng bộ ảnh bảy vòng tròn giao nhau. Bộ ảnh đó giúp nhớ tên các kiểu nối, nhưng nó ngầm giả định mỗi vòng tròn là một tập hợp giá trị không trùng lặp, còn bảng thật thì không như vậy. Ba câu hỏi mà sơ đồ Venn không trả lời được: khi một khóa xuất hiện ba lần ở bảng phải thì dòng bên trái ra sao, khi ô khóa bỏ trống thì dòng đó rơi vào vùng nào, và vì sao đặt điều kiện lọc ở WHERE lại làm LEFT JOIN cho ra kết quả giống hệt INNER JOIN. Cả ba đều là nguyên nhân khiến báo cáo sai số mà người viết truy vấn không nhận ra, vì câu lệnh vẫn chạy và vẫn trả về dữ liệu trông hợp lý. Công cụ này giữ lại sơ đồ Venn cho phần trực giác, nhưng đặt bên cạnh nó một bảng kết quả tính từ chính dữ liệu bạn gõ vào. Sửa một ô, thêm một dòng trùng mã, xóa một giá trị khóa rồi nhìn số dòng kết quả nhảy, đó là cách hiểu JOIN nhanh hơn đọc mười trang lý thuyết.
Lợi ích khi sử dụng
- Hiểu được vì sao số dòng kết quả có thể nhiều hơn số dòng của cả hai bảng cộng lại
- Nhìn tận mắt cách NULL sinh ra ở phép nối ngoài thay vì chỉ nghe mô tả
- Có sẵn câu SQL mẫu đúng cú pháp để chép vào truy vấn thật, đổi tên bảng ngay trên trang
- Dùng làm giáo cụ khi hướng dẫn thành viên mới trong nhóm hoặc khi ôn thi môn cơ sở dữ liệu
- Không cần cài database, không cần tạo bảng, mở trang là thử được ngay trên điện thoại
Cách dùng công cụ minh họa JOIN
- 1Bấm chọn một trong tám kiểu nối ở khối trên cùng, sơ đồ Venn và câu SQL mẫu đổi theo ngay.
- 2Sửa dữ liệu hai bảng ở hai ô văn bản phía dưới, dòng đầu là tên cột, các dòng sau là dữ liệu ngăn bằng dấu phẩy.
- 3Chọn cột khóa cho bảng trái và bảng phải, hoặc chọn cặp cột trỏ lên và được trỏ tới nếu đang xem SELF JOIN.
- 4Đọc bảng kết quả ở cuối trang, ô hiện chữ NULL là ô không có dữ liệu tương ứng chứ không phải ô rỗng.
- 5Thử thêm một dòng trùng mã ở bảng phải hoặc xóa giá trị khóa của một dòng bên trái để thấy số dòng kết quả thay đổi.
Tám kiểu nối và câu hỏi nghiệp vụ tương ứng với từng kiểu
Cách nhớ hiệu quả nhất không phải là nhớ hình vẽ mà là gắn mỗi kiểu nối với một câu hỏi. INNER JOIN trả lời câu hỏi những nhân viên đã được xếp vào một phòng ban có thật thì thuộc phòng nào. LEFT JOIN trả lời danh sách toàn bộ nhân viên kèm tên phòng, ai chưa có phòng thì để trống, đây là kiểu nối dùng nhiều nhất khi làm báo cáo vì nó không làm mất dòng nào của bảng chính. RIGHT JOIN hỏi ngược lại từ phía phòng ban, và thực tế ít ai dùng vì chỉ cần đổi chỗ hai bảng là thành LEFT JOIN dễ đọc hơn. FULL OUTER JOIN dùng khi đối chiếu hai nguồn dữ liệu mà không nguồn nào được coi là chuẩn, ví dụ so sổ kế toán với sao kê ngân hàng. CROSS JOIN sinh mọi tổ hợp, hữu ích khi cần dựng lưới đủ mọi cặp như tất cả sản phẩm nhân tất cả chi nhánh để tính tồn kho thiếu. SELF JOIN dùng cho dữ liệu tự tham chiếu như nhân viên với quản lý hay danh mục cha con. Hai biến thể loại trừ trả lời câu hỏi soát lỗi: nhân viên nào đang trỏ tới phòng ban không tồn tại, và ngược lại phòng ban nào chưa có ai.
Vì sao số dòng kết quả tăng vọt: hiện tượng nhân bản dòng
Đây là hiểu nhầm phổ biến nhất và cũng nguy hiểm nhất vì nó làm sai số liệu tổng hợp mà không báo lỗi. Nhiều người tin rằng LEFT JOIN luôn trả về đúng số dòng của bảng trái. Điều đó chỉ đúng khi giá trị khóa ở bảng phải là duy nhất. Nếu một mã phòng ban xuất hiện hai lần ở bảng phải, mỗi nhân viên thuộc phòng đó sẽ xuất hiện hai lần trong kết quả. Hậu quả trực tiếp là mọi phép tính tổng phía sau đều nhân đôi: tổng lương phòng đó gấp đôi thực tế, số lượng đơn hàng gấp đôi, doanh thu gấp đôi. Trường hợp hay gặp trong thực tế là nối bảng đơn hàng với bảng chi tiết đơn hàng rồi cộng tổng tiền của đơn, con số phình lên theo số dòng chi tiết. Cách phát hiện là đếm số dòng trước và sau khi nối; nếu tăng thì quan hệ không phải một với một. Cách xử lý là gom nhóm dữ liệu bên phải trước rồi mới nối, hoặc dùng truy vấn con trả về đúng một dòng cho mỗi khóa. Bạn có thể tái hiện toàn bộ hiện tượng này ngay trong công cụ bằng cách thêm một dòng phòng ban trùng mã.
NULL trong phép nối: không bằng chính nó và không khớp với ai
SQL dùng logic ba trạng thái, phép so sánh trả về đúng, sai hoặc không xác định. Vì NULL nghĩa là không biết giá trị, nên biểu thức NULL bằng NULL không cho ra đúng mà cho ra không xác định, và điều kiện nối chỉ giữ dòng khi biểu thức cho ra đúng. Hệ quả trực tiếp: hai dòng cùng bỏ trống ô khóa sẽ không bao giờ được ghép với nhau. Công cụ này tái hiện đúng quy tắc đó, ô khóa để trống luôn rơi vào nhóm không khớp. Từ đây suy ra hai kinh nghiệm thực dụng. Thứ nhất, khi dùng kiểu nối loại trừ để tìm dòng mồ côi, kết quả sẽ gộp cả dòng thiếu khóa lẫn dòng có khóa sai, hai loại lỗi này cần xử lý khác nhau nên hãy tách chúng ra bằng điều kiện kiểm tra IS NULL trên chính cột khóa bên trái. Thứ hai, nếu dữ liệu của bạn dùng chuỗi rỗng thay cho NULL ở một bảng và dùng NULL ở bảng kia, phép nối sẽ trượt hết dù nhìn bằng mắt thấy hai ô đều trống. Hãy chuẩn hóa về một cách biểu diễn duy nhất trước khi nối.
Điều kiện lọc đặt ở ON hay ở WHERE cho kết quả hoàn toàn khác nhau
Với INNER JOIN, đặt điều kiện ở mệnh đề ON hay ở WHERE đều cho cùng kết quả nên nhiều người tưởng hai chỗ này tương đương. Với các phép nối ngoài thì không. Thứ tự thực thi logic là nối trước, lọc sau. Điều kiện trong ON tham gia vào bước quyết định dòng nào được ghép, những dòng bên trái không tìm được cặp vẫn được giữ lại kèm NULL. Điều kiện trong WHERE chạy sau khi bảng kết quả đã hình thành, và vì các dòng không khớp mang giá trị NULL ở mọi cột bên phải nên bất kỳ so sánh nào trên cột bên phải cũng cho ra không xác định, khiến chúng bị loại. Kết cục là LEFT JOIN biến thành INNER JOIN một cách âm thầm. Ví dụ kinh điển: muốn liệt kê mọi khách hàng kèm đơn hàng trong tháng này, nếu viết điều kiện tháng ở WHERE thì khách chưa mua gì biến mất khỏi báo cáo, còn viết ở ON thì họ vẫn có mặt với cột đơn hàng để trống. Ngoại lệ hợp lệ duy nhất là khi bạn cố tình kiểm tra IS NULL ở WHERE để lấy đúng nhóm không khớp, tức là kiểu nối loại trừ mà công cụ này có sẵn.
Khác biệt giữa các hệ quản trị khi viết phép nối
Cú pháp JOIN thuộc chuẩn SQL nhưng mức hỗ trợ không đồng đều. MySQL và MariaDB tới nay vẫn không có FULL OUTER JOIN, cách thay thế là viết một câu LEFT JOIN và một câu RIGHT JOIN rồi ghép bằng UNION, nhớ dùng UNION thường để loại dòng trùng ở phần giao. SQLite chỉ hỗ trợ RIGHT JOIN và FULL JOIN từ phiên bản 3.39 phát hành năm 2022, các bản cũ hơn buộc phải đổi chỗ hai bảng. Oracle có cú pháp cũ dùng dấu cộng trong ngoặc ở mệnh đề WHERE để đánh dấu phép nối ngoài, còn xuất hiện nhiều trong mã nguồn cũ nhưng không nên dùng cho câu lệnh mới. SQL Server hỗ trợ đầy đủ và còn có thêm APPLY để nối với truy vấn con phụ thuộc từng dòng. Về từ khóa, chữ OUTER trong LEFT OUTER JOIN là tùy chọn ở mọi hệ, viết LEFT JOIN là đủ. Cuối cùng, mệnh đề USING viết gọn hơn ON khi hai cột khóa trùng tên, được PostgreSQL, MySQL và SQLite hỗ trợ nhưng SQL Server thì không.
Câu hỏi thường gặp (FAQ)
Công cụ có thật sự chạy phép nối hay chỉ hiện ảnh minh họa?
Chạy thật. Toàn bộ phép nối được thực hiện bằng JavaScript trên hai bảng dữ liệu bạn nhập, theo đúng quy tắc so khớp và sinh NULL của SQL. Bạn sửa một ô là bảng kết quả tính lại ngay, kể cả hiện tượng nhân bản dòng khi khóa bên phải bị trùng.
LEFT JOIN với LEFT OUTER JOIN có khác nhau không?
Không, hoàn toàn giống nhau. Từ khóa OUTER là tùy chọn trong chuẩn SQL và mọi hệ quản trị đều chấp nhận cả hai cách viết. Tương tự, RIGHT JOIN và RIGHT OUTER JOIN, FULL JOIN và FULL OUTER JOIN cũng là một. Viết ngắn cho dễ đọc là đủ.
Vì sao LEFT JOIN trả về nhiều dòng hơn số dòng bảng trái?
Vì giá trị khóa ở bảng phải bị trùng. Mỗi dòng bên trái được ghép với từng dòng khớp bên phải nên bị nhân bản. Đây là nguyên nhân số một khiến báo cáo tính tổng bị phồng số liệu. Hãy thêm một dòng trùng mã vào bảng phải trong công cụ để thấy hiện tượng này.
Hai dòng cùng để trống ô khóa có được ghép với nhau không?
Không. Trong SQL, NULL không bằng NULL vì phép so sánh trả về trạng thái không xác định chứ không phải đúng. Dòng thiếu khóa luôn rơi vào nhóm không khớp, và ở phép nối ngoài nó xuất hiện kèm NULL ở toàn bộ cột của bảng kia.
Đặt điều kiện lọc ở ON hay ở WHERE thì đúng?
Với INNER JOIN thì hai chỗ cho cùng kết quả. Với LEFT JOIN thì khác hẳn: điều kiện lọc trên cột bảng phải đặt ở WHERE sẽ loại luôn các dòng không khớp và biến LEFT JOIN thành INNER JOIN. Muốn giữ dòng không khớp, hãy đưa điều kiện đó vào ON.
MySQL không chạy được FULL OUTER JOIN thì thay bằng gì?
Viết một câu LEFT JOIN và một câu RIGHT JOIN trên cùng hai bảng rồi ghép bằng UNION. Dùng UNION thường chứ không dùng UNION ALL để phần giao không bị lặp hai lần. SQLite thì chỉ cần nâng lên phiên bản 3.39 trở đi là có sẵn FULL JOIN.
RIGHT JOIN có cần thiết không khi đã có LEFT JOIN?
Về mặt kết quả thì không, đổi chỗ hai bảng là chuyển được qua lại. Trong thực tế nhiều nhóm quy ước chỉ dùng LEFT JOIN để câu lệnh dễ đọc theo một hướng. RIGHT JOIN vẫn hữu ích khi bạn phải chèn thêm một bảng vào chuỗi nối dài mà không muốn viết lại thứ tự.
Kiểu nối loại trừ dùng để làm gì trong công việc thật?
Để soát dữ liệu mồ côi. LEFT JOIN loại trừ tìm ra dòng trỏ tới bản ghi không tồn tại, ví dụ nhân viên gắn mã phòng ban đã bị xóa. FULL OUTER loại trừ dùng khi đối chiếu hai nguồn và cần biết cả hai bên có gì lệch, chẳng hạn so đơn hàng với phiếu giao.
CROSS JOIN có tác dụng gì ngoài việc tạo ra rất nhiều dòng?
Nó dựng lưới đầy đủ mọi tổ hợp, hữu ích khi cần bảng báo cáo không được thiếu ô, ví dụ mọi sản phẩm nhân mọi chi nhánh rồi nối tiếp với dữ liệu bán hàng để chỗ nào không bán được vẫn hiện số 0. Cần thận trọng vì số dòng bằng tích số dòng hai bảng.
SELF JOIN dùng trong tình huống nào?
Khi dữ liệu tự tham chiếu trong cùng một bảng: nhân viên với người quản lý, danh mục con với danh mục cha, bình luận với bình luận gốc. Bảng được đặt hai bí danh khác nhau để database phân biệt hai vai trò. Dùng LEFT JOIN để không mất dòng ở gốc cây.
Thứ tự các bảng trong câu JOIN có ảnh hưởng tốc độ không?
Với phép nối trong thì bộ tối ưu thường tự sắp xếp lại nên thứ tự bạn viết ít ảnh hưởng. Với phép nối ngoài thì thứ tự đổi cả ngữ nghĩa nên không thể hoán vị tùy ý. Yếu tố quyết định tốc độ vẫn là chỉ mục trên cột khóa, hãy xem công cụ tối ưu truy vấn của site.
Công cụ này khác gì trang định dạng SQL và trang tối ưu truy vấn?
Trang này giải thích ngữ nghĩa của phép nối, tức câu lệnh trả về dòng nào. SQL Formatter lo phần trình bày, xuống dòng và thụt lề cho câu lệnh dài dễ đọc. Database Query Optimizer lo phần tốc độ, chỉ mục và kế hoạch thực thi. Ba việc khác nhau, không chồng lấn.
Từ khóa liên quan
- sql join là gì
- phân biệt inner join và left join
- minh họa join bằng sơ đồ venn
- left join trả về nhiều dòng hơn
- full outer join trong mysql
- right join khác left join
- cross join dùng khi nào
- self join nhân viên quản lý
- left join loại trừ tìm dữ liệu mồ côi
- null trong phép join sql
- điều kiện lọc đặt ở on hay where
- left join thành inner join
- nhân bản dòng khi join
- sqlite right join phiên bản
- using thay cho on trong join
- học sql join cho người mới
- bài tập join sql có lời giải
- công cụ minh họa join online
- join nhiều bảng trong sql
- cách viết join đúng chuẩn
