I: Giới thiệu về JOIN
Trong SQL, mệnh đề JOIN được sử dụng để kết hợp các hàng từ hai hoặc nhiều bảng dựa trên một cột có liên quan giữa chúng. Có một số loại mệnh đề JOIN:
INNER JOIN: Trả về các record có giá trị khớp trong cả hai bảngLEFT JOIN: Trả về tất cả các record từ bảng bên trái (table1) và mọi record phù hợp từ bảng bên phải (table2). Nếu không khớp, giá trị NULL sẽ được hiển thị cho các cột của bảng bên phải (table2).RIGHT JOIN: Trả về tất cả các record từ bảng bên phải (table2) và mọi record phù hợp từ bảng bên trái (table1). Nếu không khớp, giá trị NULL sẽ được hiển thị cho các cột của bảng bên trái (table1).FULL JOIN: Trả về các record có giá trị khớp trong cả hai bảng, cũng như bất kỳ record nào chưa khớp từ một trong hai bảng <==>LEFT JOINunionRIGHT JOINCROSS JOIN: Trả về tích Descartes của hai bảng (nghĩa là mọi tổ hợp các record từ cả hai bảng)SELF JOIN: Trả về các record có giá trị khớp với điều kiện khi một bảng được join với chính nó
Trên đây mới chỉ define và mô tả ngắn ngọn ý nghĩa của các loại JOIN. Ngoài những loại JOIN ở trên thì còn 1 số loại JOIN nữa như NATURAL JOIN (Postgres support), vv.. nhưng nó sử dụng khá ít nên trong nội dung bài này mình sẽ không đề cập đến. Để tìm hiểu chi tiết hơn và thực hành qua các ví dụ cụ thể thì hãy cùng mình đi xuống các phần bên dưới nhé!
II: Inner join
Inner join trong SQL là một cách để kết hợp dữ liệu từ hai hoặc nhiều bảng trong cơ sở dữ liệu quan hệ. Nó chỉ trả về những hàng có giá trị khớp trong cả hai bảng. Nói cách khác, inner join chỉ trả về các hàng có giá trị của các cột được chỉ định trong cả hai bảng khớp với nhau.
Cú pháp cơ bản cho phép nối bên trong như sau:
1 2 3 4 5 | SELECT column1, column2, ... FROM table1 JOIN table2 ON table1.column = table2.column; |
Trong ví dụ này, table1 và table2 là tên của các bảng mà bạn muốn inner join, còn table1.column và table2.column là các cột mà bạn muốn khớp. Câu lệnh SELECT chỉ định cột nào bạn muốn truy xuất từ từ các bảng.
Bạn cũng có thể inner join nhiều bảng và cũng có thể sử dụng từ khóa “JOIN” thay vì “INNER JOIN” vì SQL hỗ trợ inner join ngầm định.
Dưới đây là một ví dụ về việc join 3 bảng lại với nhau:
1 2 3 4 5 | SELECT orders.order_id, customers.name, products.name FROM orders JOIN customers ON orders.customer_id = customers.customer_id JOIN products ON orders.product_id = products.product_id; |
Truy vấn này lấy ra order_id, customers.name và products.name cho mỗi đơn đặt hàng trong bảng “orders” bằng cách so khớp customer_id trong bảng “orders” với customer_id trong bảng “customers” và so khớp product_id trong bảng “orders” với product_id trong bảng “products“.
Đây là một ví dụ về inner join trong SQL với một số dữ liệu mẫu:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -- Sample data for the "employees" table CREATE TABLE employees ( employee_id INT, name VARCHAR(255), department_id INT ); INSERT INTO employees (employee_id, name, department_id) VALUES (1, 'John Smith', 1), (2, 'Jane Doe', 2), (3, 'Bob Johnson', 1); -- Sample data for the "departments" table CREATE TABLE departments ( department_id INT, department_name VARCHAR(255) ); INSERT INTO departments (department_id, department_name) VALUES (1, 'IT'), (2, 'HR'), (3, 'Marketing'); -- Inner join the "employees" and "departments" tables SELECT employees.name, departments.department_name FROM employees JOIN departments ON employees.department_id = departments.department_id; |
Bảng “employees” có 3 nhân viên với id 1,2,3, tên và id bộ phận của họ. Và bảng “departments” có 3 phòng ban với id 1,2,3 và tên phòng ban. Inner join thực hiện trên cột department_id trong cả hai bảng, do đó, chỉ những hàng có giá trị department_id khớp trong cả hai bảng mới được trả về. Câu lệnh SELECT chỉ định rằng chúng ta muốn truy xuất employees.name và department_name từ hai bảng.
Kết quả của truy vấn này sẽ là một bảng có hai cột, một cho employees.name và một cho department_name. Bảng kết quả sẽ chỉ chứa các hàng có giá trị department_id khớp với cả bảng “employees” và “departments“.
1 2 3 4 | Name | Department_Name John Smith | IT Jane Doe | HR |
Nó chỉ chứa những hàng mà department_id khớp trong cả hai bảng là nhân viên John Smith và Jane Doe.
III: Left join
Left join (còn được gọi là left outer join) trong SQL là một cách để kết hợp dữ liệu từ hai hoặc nhiều bảng trong cơ sở dữ liệu quan hệ, tương tự như inner join. Điểm khác biệt chính là left join trả về tất cả các hàng từ bảng bên trái (tức là bảng đầu tiên được chỉ định trong join), ngay cả khi không có kết quả khớp trong bảng bên phải (tức là bảng thứ hai được chỉ định trong join). Trong trường hợp khi tồn tại các record của table1 không có kết quả khớp với table2 thì các column select của table2 sẽ được trả về bằng NULL .
Cú pháp cơ bản cho left join như sau:
1 2 3 4 5 | SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.column = table2.column; |
Trong ví dụ này, table1 là bảng bên trái và table2 là bảng bên phải, đồng thời table1.column và table2.column là các cột mà bạn muốn so khớp.
Đây là một ví dụ về left join trong SQL với một số dữ liệu mẫu:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -- Sample data for the "employees" table CREATE TABLE employees ( employee_id INT, name VARCHAR(255), department_id INT ); INSERT INTO employees (employee_id, name, department_id) VALUES (1, 'John Smith', 1), (2, 'Jane Doe', 2), (3, 'Bob Johnson', 1), (4, 'Marry', NULL); -- Sample data for the "departments" table CREATE TABLE departments ( department_id INT, department_name VARCHAR(255) ); INSERT INTO departments (department_id, department_name) VALUES (1, 'IT'), (2, 'HR'); -- Left join the "employees" and "departments" tables SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id; |
Trong ví dụ này, bảng “employees” có 4 nhân viên và id bộ phận của họ. Còn bảng “departments” có 2 phòng ban. Việc left join được thực hiện trên cột department_id trong cả hai bảng. Câu lệnh SELECT chỉ định rằng chúng ta muốn truy xuất employees.name và department_name từ hai bảng.
Kết quả của truy vấn này sẽ là một bảng có hai cột, một cho employees.name và một cho department_name. Bảng kết quả sẽ chứa tất cả các hàng của bảng “employees“, bất kể có trùng khớp trong bảng “departments” hay không. Nhân viên thứ tư, Marry không có department_id và khi sử dụng left join, nó sẽ vẫn hiển thị trong tập hợp kết quả nhưng với giá trị NULL trong cột department_name.
1 2 3 4 5 6 | Name | Department_Name John Smith | IT Jane Doe | HR Bob Johnson | IT Marry | NULL |
Nó sẽ trả về tất cả các hàng từ bảng “employees” và chỉ những hàng phù hợp từ bảng “departments” và đối với các hàng chưa khớp trong bảng “departments” sẽ trả về NULL trong cột department_name.
IV: Right join
Right join (còn được gọi là right outer join) trong SQL tương tự như left join, nhưng nó trả về tất cả các hàng từ bảng bên phải (tức là bảng thứ hai được chỉ định trong join), ngay cả khi không có kết quả khớp ở bảng bên trái (tức là bảng đầu tiên được chỉ định trong liên kết).Trong trường hợp khi tồn tại các record của table2 không có kết quả khớp với table1 thì các column select của table1 sẽ được trả về bằng NULL .
Cú pháp cơ bản cho right join như sau:
1 2 3 4 5 | SELECT column1, column2, ... FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; |
Nó ít được sử dụng hơn left join và inner join, vì hầu hết thời gian, yêu cầu là truy xuất tất cả dữ liệu từ một bảng và dữ liệu phù hợp từ một bảng khác và left join được ưa thích hơn trong trường hợp đó.
Đây là một ví dụ về right join trong SQL với một số dữ liệu mẫu:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | -- Sample data for the "orders" table CREATE TABLE orders ( order_id INT, customer_id INT, product_id INT ); INSERT INTO orders (order_id, customer_id, product_id) VALUES (1, 1, 1), (2, 2, 2), (3, 1, 2); -- Sample data for the "customers" table CREATE TABLE customers ( customer_id INT, name VARCHAR(255) ); INSERT INTO customers (customer_id, name) VALUES (1, 'John Smith'), (2, 'Jane Doe'), (3, 'Bob Johnson'); -- Sample data for the "products" table CREATE TABLE products ( product_id INT, name VARCHAR(255) ); INSERT INTO products (product_id, name) VALUES (1, 'Product 1'), (2, 'Product 2'), (3, 'Product 3'); -- Right join the "orders" , "customers" and "products" tables SELECT orders.order_id, products.name, customers.name FROM orders RIGHT JOIN products ON orders.product_id = products.product_id RIGHT JOIN customers ON orders.customer_id = customers.customer_id; |
Trong ví dụ này, bảng “orders” có 3 đơn hàng, bảng “customers” có 3 khách hàng và bảng “products” có 3 sản phẩm. Việc right join được thực hiện trên các cột product_id và customer_id trong cả hai bảng. Câu lệnh SELECT chỉ định rằng chúng ta muốn truy xuất order_id, products.name và customers.name từ ba bảng.
Kết quả của truy vấn này sẽ là một bảng có ba cột, một cột dành cho order_id, một cột dành cho products.name và một cột dành cho customers.name . Bảng kết quả sẽ chứa tất cả các hàng của bảng “products” và “customers“, bất kể có khớp trong bảng “orders” hay không. Các hàng chưa khớp từ bảng “orders” sẽ có giá trị NULL cho cột order_id.
1 2 3 4 5 6 | Order_id | Product_Name | Customer_Name 1 | Product 1 | John Smith 2 | Product 2 | Jane Doe 3 | Product 2 | John Smith NULL | Product 3 | Bob Johnson |
V: Full outer join
Full outer join (hoặc full join) trong SQL là sự kết hợp của cả left join và right join. Nó trả về tất cả các hàng từ cả hai bảng, ngay cả khi không có kết quả khớp trong bảng kia. Bảng kết quả sẽ chứa tất cả các hàng từ bảng bên trái, tất cả các hàng từ bảng bên phải và mọi hàng phù hợp từ cả hai bảng.
Full outer join không phải là thao tác SQL tiêu chuẩn, hệ thống cơ sở dữ liệu khác có thể hỗ trợ hoặc không hỗ trợ nó, đồng thời cú pháp về cách viết full outer join có thể khác nhau tùy theo cơ sở dữ liệu. Một số cơ sở dữ liệu sử dụng từ khóa FULL OUTER JOIN, một số sử dụng FULL JOIN.
Ví dụ, trong MySQL, bạn có thể mô phỏng FULL OUTER JOIN bằng cách sử dụng UNION của LEFT JOIN và RIGHT JOIN. Cú pháp cơ bản sẽ là:
1 2 3 4 5 6 7 8 9 10 | SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.column = table2.column UNION SELECT column1, column2, ... FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; |
Ngoài ra, bạn có thể sử dụng một số cơ sở dữ liệu như Oracle và SQL Server có cú pháp FULL OUTER JOIN có thể được sử dụng:
1 2 3 4 5 | SELECT column1, column2, ... FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column; |
Trong ví dụ này, table1 là bảng bên trái và table2 là bảng bên phải, đồng thời table1.column và table2.column là các cột mà bạn muốn so khớp.
Truy vấn này trả về tất cả các hàng từ cả hai bảng và bất kỳ hàng khớp nào từ cả hai bảng, với các hàng không khớp được thay thế bằng giá trị NULL.
Đây là một ví dụ về full outer join trong SQL với một số dữ liệu mẫu:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -- Sample data for the "employees" table CREATE TABLE employees ( employee_id INT, name VARCHAR(255), department_id INT ); INSERT INTO employees (employee_id, name, department_id) VALUES (1, 'John Smith', 1), (2, 'Jane Doe', 2), (3, 'Bob Johnson', 1); -- Sample data for the "departments" table CREATE TABLE departments ( department_id INT, department_name VARCHAR(255) ); INSERT INTO departments (department_id, department_name) VALUES (1, 'IT'), (2, 'HR'), (3, 'Finance'); -- Full outer join the "employees" and "departments" tables SELECT employees.name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.department_id; |
Trong ví dụ này, bảng “employees” có 3 nhân viên và bảng “departments” có 3 phòng ban. Việc full outer join được thực hiện trên cột department_id trong cả hai bảng. Câu lệnh SELECT chỉ định rằng chúng ta muốn truy xuất employees.name và department_name từ hai bảng.
Kết quả của truy vấn này sẽ là một bảng có hai cột, một cho employees.name và một cho department_name. Bảng kết quả sẽ chứa tất cả các hàng của bảng “employees“, cũng như tất cả các hàng của bảng “departments” và các hàng phù hợp sẽ được kết hợp thành một hàng. Nếu không có kết quả khớp trong bất kỳ bảng nào, nó sẽ trả về giá trị departments cho các cột employees.name hoặc department_name.
1 2 3 4 5 6 | Name | Department_Name John Smith | IT Jane Doe | HR Bob Johnson | IT NULL | Finance |
VI: Cross join
Cross join, còn được gọi là tích Đề-các, trong SQL trả về tổ hợp của tất cả các hàng từ bảng đầu tiên với tất cả các hàng từ bảng thứ hai. Điều này có nghĩa là số hàng trong bảng kết quả là tích của số hàng trong bảng đầu tiên và số hàng trong bảng thứ hai.
Cú pháp cơ bản của cross join như sau:
1 2 3 4 | SELECT column1, column2, ... FROM table1 CROSS JOIN table2 |
Điều đáng chú ý là số lượng hàng kết quả trong cross join có thể tăng theo cấp số nhân với số lượng hàng trong mỗi bảng. Thông thường, bạn không nên sử dụng cross join, trừ khi bạn có yêu cầu hoặc nhu cầu cụ thể về nó, vì nó có thể tạo ra một tập hợp kết quả rất lớn và chiếm nhiều bộ nhớ.
Đây là một ví dụ về cross join:
1 2 3 4 | SELECT colors.color, sizes.size FROM colors CROSS JOIN sizes; |
Trong ví dụ này, bảng “colors” có 4 màu và bảng “sizes” có 6 kích cỡ. cross join được thực hiện mà không có bất kỳ điều kiện bổ sung nào. Câu lệnh SELECT xác định rằng chúng ta muốn truy xuất màu sắc và kích thước.
Kết quả của truy vấn này sẽ là một bảng có hai cột, một cho color và một cho size. Bảng kết quả sẽ chứa 24 hàng, kết hợp màu sắc và kích thước 4×6
1 2 3 4 5 6 7 8 9 10 11 | Color | Size Red | Small Red | Medium Red | Large Red | X-Large Red | XX-Large Red | XXX-Large Green | Small Green | Medium ... |
VII: Self join
Self join trong SQL là một kiểu join trong đó một bảng được nối với chính nó, thường sử dụng các bí danh để phân biệt giữa hai thể hiện của bảng. Điều này hữu ích khi một bảng có mối quan hệ với chính nó, chẳng hạn như khi một bảng chứa mối quan hệ cha-con hoặc mối quan hệ phân cấp.
Cú pháp cơ bản self join như sau:
1 2 3 4 5 | SELECT column1, column2, ... FROM table1 t1 JOIN table1 t2 ON t1.column = t2.column |
Trong ví dụ này, table1 là tên của bảng được join, t1 là bí danh cho phiên bản đầu tiên của bảng và t2 là bí danh cho phiên bản thứ hai của bảng. t1.column và t2.column là các cột mà bạn muốn so khớp.
1 2 3 4 5 | SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id |
Trong ví dụ này, bảng “employees” có quan hệ tự tham chiếu, mỗi nhân viên có một cột manager_id tham chiếu đến một employee_id trên cùng một bảng. Việc nối được thực hiện trên các cột manager_id và employee_id của cùng một bảng. Câu lệnh SELECT chỉ định rằng chúng ta muốn truy xuất tên của nhân viên và người quản lý của anh ta.
Kết quả của truy vấn này sẽ là một bảng có hai cột, một cho tên của nhân viên và một cho tên của người quản lý. Bảng kết quả sẽ chứa tất cả tên nhân viên cùng với tên người quản lý của họ, những nhân viên không có người quản lý sẽ có giá trị NULL cho cột tên người quản lý.
Self join là một cách mạnh mẽ để truy vấn dữ liệu phân cấp hoặc đệ quy được lưu trữ trong một bảng và nó có thể được sử dụng trong các tình huống khác nhau như truy vấn mối quan hệ giữa nhân viên và người quản lý, sơ đồ tổ chức và nhiều trường hợp sử dụng khác.
VIII: Tổng kết
Ở trên mình đã giới thiệu với các bạn các loại JOIN trong SQL kèm các ví dụ thực tiễn để dễ hình dung nhất có thể. Các bạn có thể tìm hiểu ở những nguồn khác để có thể so sánh và bổ sung kiến thức cho bản thân nhé. Nếu trong quá trình viết bài mình có sai sót hoặc bạn thắc mắc có thể comment vào bài viết này giúp mình nhé. Mình vẫn tiếp tục ra những bài viết mới trong series này mong các bạn ủng hộ. Cảm ơn mọi người 