Tìm Hiểu Về Mệnh Đề "JOIN" Trong SQL

Trong các cơ sở dữ liệu phức tạp, mệnh đề JOIN được sử dụng rất thường xuyên với mục đích kết hợp dữ liệu trên hai hoặc nhiều bảng khác nhau dựa trên mối quan hệ giữa chúng. Trong bài viết này chúng ta sẽ cùng tìm hiểu các ví dụ minh họa cụ thể để hiểu về mệnh đề JOIN trong SQL.

Lưu ý: Tương tự như SELECT, FROM hay WHERE... JOIN là một mệnh đề SQL thay vì một câu truy vấn SQL hoàn chỉnh.

Ví Dụ Về Mệnh Đề JOIN

Giả sử bạn cần phát triển một cơ sở dữ liệu cho ứng dụng web bán đĩa CD cho một cửa hàng để lưu trữ thông tin khách hàng và quản lý đơn đặt hàng. Trên cơ sở dữ liệu chúng ta có bảng customers chứa thông tin khách hàng với dữ liệu như sau:

 +----+---------------------+----------------+
 | id | fullname            | city           |
 +----+---------------------+----------------+
 |  1 | Nguyễn Văn Tựu      | Hà Nội         |
 |  2 | Lê Văn Thành        | Hồ Chí Minh    |
 |  3 | Phạm Huỳnh Như      | Đà Nẵng        |
 +----+---------------------+----------------+

Đồng thời chúng ta cũng có bảng orders chứa thông tin đơn đặt hàng với dữ liệu như sau:

+-----+-------------+-------+------------+
| id  | customer_id | units | order_date |
+-----+-------------+-------+------------+
| 208 |           1 |     2 | 2017-05-08 |
| 209 |           3 |     5 | 2017-03-15 |
| 210 |          57 |    10 | 2017-02-20 |
+-----+-------------+-------+------------+

Ngoài ra trong bảng orders có một trường (hay cột) với tên là customer_id và trường này sẽ tham chiếu tới trường id trong bảng customers.

Bây giờ yêu cầu đưa ra đó là chúng ta cần phải lấy ra được họ tên của khách hàng cùng số lượng đĩa CD họ mua cùng ngày mua hàng. Với quan hệ giữa hai bảng như trên thì chúng ta có thể chạy câu lệnh truy vấn SQL dưới đây để lấy ra họ tên (fullname) của từng order sử dụng mệnh đề JOIN như sau:

SELECT orders.id, orders.units, orders.order_date, customers.fullname FROM orders
INNER JOIN customers
ON orders.customer_id=customers.id;

Câu lệnh trên sẽ in ra kết quả như sau:

+-----+-------+------------+---------------------+
| id  | units | order_date | fullname            |
+-----+-------+------------+---------------------+
| 208 |     2 | 2017-05-08 | Nguyễn Văn Tựu      |
| 209 |     5 | 2017-03-15 | Phạm Huỳnh Như      |
+-----+-------+------------+---------------------+

Trong kết quả trên chỉ có hai dòng dữ liệu được trả về ứng với các đơn đặt hàng có giá trị id lần lượt là 208 và 209. Đơn đặt hàng thứ ba (nằm trong bảng order với giá trị id là 210) không xuất hiện trong kết quả trả về do giá trị của trường customer_id của đơn hàng này là 57 không có trong bảng customers.

Mối Quan Hệ Giữa Các Bảng Khi JOIN

Một điều quan trọng mà chúng ta cần nhớ khi sử dụng mệnh đề JOIN trong câu truy vấn query đó là cần tồn tại mối quan hệ giữa các bảng mà dữ liệu được kết hợp với nhau.

Trong ví dụ trên, trường customer_id trong bảng orders được sử dụng để tạo ra mối quan hệ giữa bảng orders và customers. Sử dụng giá trị của trường customer_id chúng ta có thể tham chiếu quay trở lại dữ liệu trên bảng customers. Nếu không có trường này thì chúng ta không thể thực hiện được việc kết hợp dữ liệu giữa hai bảng.

Khóa Ngoại (Foreign Key)

Đối với các hệ quản trị cơ sở dữ liệu có mối quan hệ (Relational Database Management System hay RDBMS) thì việc tạo mối quan hệ như ở ví dụ trên được thực hiện thông qua khóa ngoại.

Khóa ngoại thực chất là một sự ràng buộc (constraint) giữa các bảng để đảm bảo tính trung thực của dữ liệu. Trong bảng orders ở ví dụ trên chúng ta có thể tạo khóa ngoại sử dụng trường customer_id để tham chiếu tới trường id trên bảng customers. Điều này được thực hiện thông qua câu lệnh thêm khóa ngoại như sau:

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

Tuy nhiên nếu bạn chạy câu lệnh trên (giả sử bạn dùng MySQL là DBMS) thì bạn sẽ thấy lỗi sau xuất hiện:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`#sql-482_9`, CONSTRAINT `#sql-482_9_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

Điều này là bởi vì đơn đặt hàng thứ 3 trên bảng orders có giá trị customer_id là 57 tuy nhiên trong bảng customers không có khách hàng nào với giá trị id là 57. Để khắc phục lỗi trên bạn có thể xóa đi đơn hàng này thông qua câu lệnh sau:

DELETE FROM orders WHERE id=210;

Sau đó chạy lại câu lệnh:

ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

Nếu làm đúng bạn sẽ thấy thông báo kết quả tương tự như sau:

mysql> ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);
Query OK, 2 rows affected (0,04 sec)
Records: 2  Duplicates: 0  Warnings: 0

Với việc thêm vào khóa ngoại vào bảng orders như trên thì bây giờ nếu bạn chèn dữ liệu vào trường orders với giá trị customer_id không tồn tại trong bảng customers ví dụ như sau:

INSERT INTO orders(id, customer_id, units, order_date) VALUES(210, 57, 10, '2017-02-20');

Thì bạn sẽ thấy lỗi sau xuất hiện:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test_db`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))