[Hệ quản trị cơ sở dữ liệu] SQL join

Mệnh đề Joins trong SQL được sử dụng để kết hợp các bản ghi từ hai hoặc nhiều bảng trong một Database. Một JOIN là một phương tiện để kết hợp các trường từ hai bảng bằng việc sử dụng các giá trị chung của mỗi bảng.

Giả sử có hai bảng, bảng SINHVIEN có các bản ghi sau:

+----+---------+-----+-----------+--------+
| ID |  TEN    | TUOI|  KHOAHOC  | HOCPHI |
+----+---------+-----+-----------+---------+
|  1 | Hoang   |  21 | CNTT      | 4000000       |
|  2 | Viet    |  19 | DTVT      | 3000000     |
|  3 | Thanh   |  18 | KTDN      |  4000000      |
|  4 | Nhan    |  19 | CK        |   4500000   |
|  5 | Huong   |  20 | TCNH      |   5000000     |
+----+---------+-----+-----------+---------+

Bảng MUONSACH có các bản ghi sau:

+-----+---------------------+-------------+--------+
|STT  | NGAY                | SINHVIEN_ID | SOTIEN |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   320 |
| 100 | 2009-10-08 00:00:00 |           3 |   250 |
| 101 | 2009-11-20 00:00:00 |           2 |   280 |
| 103 | 2008-05-20 00:00:00 |           4 |   290 |
+-----+---------------------+-------------+--------+

Bây giờ, chúng ta kết hợp hai bảng này trong lệnh SELECT như sau:

SQL> SELECT ID, TEN, TUOI, SOTIEN
        FROM SINHVIEN, MUONSACH
        WHERE  SINHVIEN.ID = MUONSACH.SINHVIEN_ID;

Ví dụ trên sẽ cho kết quả:

+----+----------+-----+--------+
| ID | TEN     | TUOI | SOTIEN |
+----+----------+-----+--------+
|  3 | Thanh  |  18 |   320 |
|  3 | Thanh  |  18 |   250 |
|  2 | Viet   |  19 |   280 |
|  4 | Nhan |  19 |   290 |
+----+----------+-----+--------+

Ở đây, điều đáng chú ý là, JOIN được thực hiện trong mệnh đề WHERE. Một số toán tử có thể được sử dụng để kết hợp các bảng là: =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, và NOT; tất cả toán tử này có thể được sử dụng để kết hợp các bảng. Tuy nhiên, toán tử được sử dụng phổ biến nhất là dấu bằng (=).

Các loại JOIN trong SQL

Trong SQL, có một số loại JOIN có sẵn là:

• INNER JOIN trong SQL: Trả về các hàng khi có một so khớp (match) trong các bảng.
• LEFT JOIN trong SQL: Trả về tất cả các hàng từ bảng bên trái, ngay cả khi không có so khớp nào trong bảng bên phải.
• RIGHT JOIN trong SQL: Trả về tất cả các hàng từ bảng bên phải, ngay cả khi không có so khớp nào trong bảng bên trái.
• FULL JOIN trong SQL: Trả về các hàng khi có một so khớp ở một trong các bảng.
• SELF JOIN trong SQL: Được sử dụng để kết hợp một bảng với chính nó, khi nếu table đó là hai bảng dữ liệu, thay tên tạm thời cho ít nhất một bảng trong lệnh SQL.
• CARTESIAN JOIN trong SQL: Trả về tích Đề-các của tập hợp các bản ghi từ hai hoặc nhiều bảng kết hợp.

[INNER JOIN trong SQL] Loại JOIN thường được sử dụng nhất và quan trọng nhất là INNER JOIN. Chúng cũng được gọi là một EQUIJOIN.

INNER JOIN tạo một bảng kết quả mới bằng việc kết hợp các giá trị cột của hai bảng (bang1 và bang2) dựa trên join-predicate. Truy vấn này so sánh mỗi hàng trong bang1 với mỗi hàng trong bang2 để tìm ra các cặp hàng mà thỏa mãn join-predicate. Khi join-predicate được thỏa mãn, các giá trị cột của mỗi cặp hàng được kết nối A và B được kết hợp vào trong một hàng kết quả.

Cú pháp

Cú pháp cơ bản của INNER JOIN trong SQL là:

SELECT bang1.cot1, bang2.cot2...
FROM bang1
INNER JOIN bang2
ON bang1.field_chung = bang2.field_chung;

Ví dụ

Giả sử có hai bảng, bảng SINHVIEN có các bản ghi sau:

+----+---------+-----+-----------+--------+
| ID |  TEN    | TUOI|  KHOAHOC  | HOCPHI |
+----+---------+-----+-----------+---------+
|  1 | Hoang   |  21 | CNTT      | 4000000       |
|  2 | Viet    |  19 | DTVT      | 3000000     |
|  3 | Thanh   |  18 | KTDN      |  4000000      |
|  4 | Nhan    |  19 | CK        |   4500000   |
|  5 | Huong   |  20 | TCNH      |   5000000     |
+----+---------+-----+-----------+---------+

Bảng MUONSACH có các bản ghi sau:

+-----+---------------------+-------------+--------+
|STT  | NGAY                | SINHVIEN_ID | SOTIEN |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   320 |
| 100 | 2009-10-08 00:00:00 |           3 |   250 |
| 101 | 2009-11-20 00:00:00 |           2 |   280 |
| 103 | 2008-05-20 00:00:00 |           4 |   290 |
+-----+---------------------+-------------+--------+

Bây giờ, chúng ta kết hợp hai bảng bởi sử dụng INNER JOIN trong SQL như sau:

SQL> SELECT  ID, TEN, SOTIEN, NGAY
     FROM SINHVIEN
     INNER JOIN MUONSACH
     ON SINHVIEN.ID = MUONSACH.SINHVIEN_ID;

Ví dụ trên sẽ cho kết quả:

+----+----------+--------+---------------------+
| ID | TEN     | SOTIEN | NGAY                |
+----+----------+--------+---------------------+
|  3 | Thanh  |   320 | 2009-10-08 00:00:00 |
|  3 | Thanh  |   250 | 2009-10-08 00:00:00 |
|  2 | Viet   |   280 | 2009-11-20 00:00:00 |
|  4 | Nhan |   290 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+

LEFT JOIN trong SQL trả về tất cả các hàng từ bảng bên trái, ngay cả khi không có so khớp nào trong bảng bên phải. Nghĩa là, nếu mệnh đề ON so khớp với 0 bản ghi trong bảng bên phải, thì LEFT JOIN sẽ vẫn trả về một hàng trong kết quả, nhưng với NULL trong mỗi cột từ bảng bên phải.

Điều này nghĩa là LEFT JOIN trả về tất cả giá trị từ bảng bên trái, cộng với các giá trị được so khớp từ bảng bên phải hoặc NULL trong trường hợp không có so khớp nào.

Cú pháp

Cú pháp cơ bản của LEFT JOIN trong SQL như sau:

SELECT bang1.cot1, bang2.cot2...
FROM bang1
LEFT JOIN bang2
ON bang1.field_chung = bang2.field_chung;

Ví dụ

Giả sử có hai bảng, bảng SINHVIEN có các bản ghi sau:

+----+---------+-----+-----------+--------+
| ID |  TEN    | TUOI|  KHOAHOC  | HOCPHI |
+----+---------+-----+-----------+---------+
|  1 | Hoang   |  21 | CNTT      | 4000000       |
|  2 | Viet    |  19 | DTVT      | 3000000     |
|  3 | Thanh   |  18 | KTDN      |  4000000      |
|  4 | Nhan    |  19 | CK        |   4500000   |
|  5 | Huong   |  20 | TCNH      |   5000000     |
+----+---------+-----+-----------+---------+

Bảng MUONSACH có các bản ghi sau:

+-----+---------------------+-------------+--------+
|STT  | NGAY                | SINHVIEN_ID | SOTIEN |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   320 |
| 100 | 2009-10-08 00:00:00 |           3 |   250 |
| 101 | 2009-11-20 00:00:00 |           2 |   280 |
| 103 | 2008-05-20 00:00:00 |           4 |   290 |
+-----+---------------------+-------------+--------+

Bây giờ, chúng ta kết hợp hai bảng bởi sử dụng LEFT JOIN trong SQL như sau:

SQL> SELECT  ID, TEN, SOTIEN, NGAY
     FROM SINHVIEN
     LEFT JOIN MUONSACH
     ON SINHVIEN.ID = MUONSACH.SINHVIEN_ID;

Ví dụ trên sẽ cho kết quả:

+----+----------+--------+---------------------+
| ID | TEN     | SOTIEN | NGAY                |
+----+----------+--------+---------------------+
|  1 | Hoang   |   NULL | NULL                |
|  2 | Viet   |   280 | 2009-11-20 00:00:00 |
|  3 | Thanh  |   320 | 2009-10-08 00:00:00 |
|  3 | Thanh  |   250 | 2009-10-08 00:00:00 |
|  4 | Nhan |   290 | 2008-05-20 00:00:00 |
|  5 | Huong   |   NULL | NULL                |
+----+----------+--------+---------------------+

RIGHT JOIN trong SQL trả về tất cả hàng từ bảng bên phải, ngay cả khi không có so khớp nào trong bảng bên trái. Nghĩa là nếu mệnh đề ON so khớp với 0 bản ghi trong bảng bên trái, thì RIGHT JOIN sẽ vẫn trả về một hàng trong kết quả, nhưng với NULL trong mỗi cột từ bảng bên trái.

Điều này nghĩa là RIGHT JOIN trả về tất cả giá trị từ bảng bên phải, cộng với các giá trị được so khớp từ bảng bên trái hoặc NULL trong trường hợp không có so khớp nào.

Cú pháp

Cú pháp cơ bản của RIGHT JOIN trong SQL như sau:

SELECT bang1.cot1, bang2.cot2...
FROM bang1
RIGHT JOIN bang2
ON bang1.field_chung = bang2.field_chung;

Ví dụ

Giả sử có hai bảng, bảng SINHVIEN có các bản ghi sau:

+----+---------+-----+-----------+--------+
| ID |  TEN    | TUOI|  KHOAHOC  | HOCPHI |
+----+---------+-----+-----------+---------+
|  1 | Hoang   |  21 | CNTT      | 4000000       |
|  2 | Viet    |  19 | DTVT      | 3000000     |
|  3 | Thanh   |  18 | KTDN      |  4000000      |
|  4 | Nhan    |  19 | CK        |   4500000   |
|  5 | Huong   |  20 | TCNH      |   5000000     |
+----+---------+-----+-----------+---------+

Bảng MUONSACH có các bản ghi sau:

+-----+---------------------+-------------+--------+
|STT  | NGAY                | SINHVIEN_ID | SOTIEN |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   320 |
| 100 | 2009-10-08 00:00:00 |           3 |   250 |
| 101 | 2009-11-20 00:00:00 |           2 |   280 |
| 103 | 2008-05-20 00:00:00 |           4 |   290 |
+-----+---------------------+-------------+--------+

Bây giờ, chúng ta kết hợp hai bảng bởi sử dụng RIGHT JOIN trong SQL như sau:

SQL> SELECT  ID, TEN, SOTIEN, NGAY
     FROM SINHVIEN
     RIGHT JOIN MUONSACH
     ON SINHVIEN.ID = MUONSACH.SINHVIEN_ID;

Ví dụ trên sẽ cho kết quả:

+------+----------+--------+---------------------+
| ID   | TEN     | SOTIEN | NGAY                |
+------+----------+--------+---------------------+
|    3 | Thanh  |   320 | 2009-10-08 00:00:00 |
|    3 | Thanh  |   250 | 2009-10-08 00:00:00 |
|    2 | Viet   |   280 | 2009-11-20 00:00:00 |
|    4 | Nhan |   290 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

FULL JOIN trong SQL kết hợp kết quả của cả hai loại LEFT và RIGHT JOIN.

Bảng được kết hợp sẽ chứa tất cả bản ghi từ cả hai bảng, và điền vào đó giá trị NULL cho các giá trị không so khớp nhau.

Cú pháp

Cú pháp cơ bản của FULL JOIN trong SQL như sau:

SELECT bang1.cot1, bang2.cot2...
FROM bang1
FULL JOIN bang2
ON bang1.field_chung = bang2.field_chung;

Ví dụ

Giả sử có hai bảng, bảng SINHVIEN có các bản ghi sau:

+----+---------+-----+-----------+--------+
| ID |  TEN    | TUOI|  KHOAHOC  | HOCPHI |
+----+---------+-----+-----------+---------+
|  1 | Hoang   |  21 | CNTT      | 4000000       |
|  2 | Viet    |  19 | DTVT      | 3000000     |
|  3 | Thanh   |  18 | KTDN      |  4000000      |
|  4 | Nhan    |  19 | CK        |   4500000   |
|  5 | Huong   |  20 | TCNH      |   5000000     |
+----+---------+-----+-----------+---------+

Bảng MUONSACH có các bản ghi sau:

+-----+---------------------+-------------+--------+
|STT  | NGAY                | SINHVIEN_ID | SOTIEN |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   320 |
| 100 | 2009-10-08 00:00:00 |           3 |   250 |
| 101 | 2009-11-20 00:00:00 |           2 |   280 |
| 103 | 2008-05-20 00:00:00 |           4 |   290 |
+-----+---------------------+-------------+--------+

Bây giờ, chúng ta kết hợp hai bảng bởi sử dụng FULL JOIN trong SQL như sau:

SQL> SELECT  ID, TEN, SOTIEN, NGAY
     FROM SINHVIEN
     FULL JOIN MUONSACH
     ON SINHVIEN.ID = MUONSACH.SINHVIEN_ID;

Ví dụ trên sẽ cho kết quả:

+------+----------+--------+---------------------+
| ID   | TEN     | SOTIEN | NGAY                |
+------+----------+--------+---------------------+
|    1 | Hoang   |   NULL | NULL                |
|    2 | Viet   |   280 | 2009-11-20 00:00:00 |
|    3 | Thanh  |   320 | 2009-10-08 00:00:00 |
|    3 | Thanh  |   250 | 2009-10-08 00:00:00 |
|    4 | Nhan |   290 | 2008-05-20 00:00:00 |
|    5 | Huong   |   NULL | NULL                |
|    3 | Thanh  |   320 | 2009-10-08 00:00:00 |
|    3 | Thanh  |   250 | 2009-10-08 00:00:00 |
|    2 | Viet   |   280 | 2009-11-20 00:00:00 |
|    4 | Nhan |   290 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

Nếu Database của bạn không hỗ trợ FULL JOIN (chẳng hạn như MySQL), thì bạn có thể sử dụng mệnh đề UNION ALL để kết hợp hai JOIN như sau:

SQL> SELECT  ID, TEN, SOTIEN, NGAY
     FROM SINHVIEN
     LEFT JOIN MUONSACH
     ON SINHVIEN.ID = MUONSACH.SINHVIEN_ID
UNION ALL
     SELECT  ID, TEN, SOTIEN, NGAY
     FROM SINHVIEN
     RIGHT JOIN MUONSACH
     ON SINHVIEN.ID = MUONSACH.SINHVIEN_ID

SELF JOIN trong SQL được sử dụng để kết hợp một bảng với chính nó như khi coi bảng đó là hai bảng, thay tên tạm thời ít nhất một bảng trong lệnh SQL.

Cú pháp

Cú pháp cơ bản của SELF JOIN trong SQL như sau:

SELECT a.ten_cot, b.ten_cot...
FROM bang1 a, bang1 b
WHERE a.field_chung = b.field_chung;

Ở đây, mệnh đề WHERE có thể là bất kỳ biểu thức nào theo yêu cầu của bạn.

Ví dụ

Giả sử có hai bảng, bảng NHANVIEN có các bản ghi sau:

+----+----------+-----+-----------+----------+
| ID | TEN     | TUOI | DIACHI   | LUONG   |
+----+----------+-----+-----------+----------+
|  1 | Hoang   |  32 | DongDa |  2000.00 |
|  2 | Thanh   |  25 | QuangTrung     |  1500.00 |
|  3 | Viet  |  23 | KimLien      |  2000.00 |
|  4 | Nhan |  25 | BaDinh    |  6500.00 |
|  5 | Huong   |  27 | CauGiay    |  8500.00 |
|  6 | Long    |  22 | TrungKinh        |  4500.00 |
|  7 | Nam    |  24 | GiapBat    | 10000.00 |
+----+----------+-----+-----------+----------+

Bây giờ, chúng ta kết hợp bảng này với chính nó bởi sử dụng SELF JOIN trong SQL như sau:

SQL> SELECT  a.ID, b.NAME, a.LUONG
     FROM NHANVIEN a, NHANVIEN b
     WHERE a.LUONG < b.LUONG;

Ví dụ trên sẽ cho kết quả:

+----+----------+---------+
| ID | TEN     | LUONG  |
+----+----------+---------+
|  2 | Hoang   | 1500.00 |
|  2 | Viet  | 1500.00 |
|  1 | Nhan | 2000.00 |
|  2 | Nhan | 1500.00 |
|  3 | Nhan | 2000.00 |
|  6 | Nhan | 4500.00 |
|  1 | Huong   | 2000.00 |
|  2 | Huong   | 1500.00 |
|  3 | Huong   | 2000.00 |
|  4 | Huong   | 6500.00 |
|  6 | Huong   | 4500.00 |
|  1 | Long    | 2000.00 |
|  2 | Long    | 1500.00 |
|  3 | Long    | 2000.00 |
|  1 | Nam    | 2000.00 |
|  2 | Nam    | 1500.00 |
|  3 | Nam    | 2000.00 |
|  4 | Nam    | 6500.00 |
|  5 | Nam    | 8500.00 |
|  6 | Nam    | 4500.00 |
+----+----------+---------+

CARTESIAN JOIN hoặc CROSS JOIN trong SQL trả về tích Đề-các của các tập hợp bản ghi từ hai hoặc nhiều bảng đã được kết hợp. Vì thế, nó tương đương như một INNER JOIN khi join-condition luôn luôn ước lượng là True hoặc khi join-condition là vắng mặt trong lệnh.

Cú pháp

Cú pháp cơ bản của CARTESIAN JOIN hoặc CROSS JOIN trong SQL như sau:

SELECT bang1.cot1, bang2.cot2...
FROM  bang1, bang2 [, bang3 ]

Ví dụ

Giả sử có hai bảng, bảng SINHVIEN có các bản ghi sau:

+----+---------+-----+-----------+--------+
| ID |  TEN    | TUOI|  KHOAHOC  | HOCPHI |
+----+---------+-----+-----------+---------+
|  1 | Hoang   |  21 | CNTT      | 4000000       |
|  2 | Viet    |  19 | DTVT      | 3000000     |
|  3 | Thanh   |  18 | KTDN      |  4000000      |
|  4 | Nhan    |  19 | CK        |   4500000   |
|  5 | Huong   |  20 | TCNH      |   5000000     |
+----+---------+-----+-----------+---------+

Bảng MUONSACH có các bản ghi sau:

+-----+---------------------+-------------+--------+
|STT  | NGAY                | SINHVIEN_ID | SOTIEN |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   320 |
| 100 | 2009-10-08 00:00:00 |           3 |   250 |
| 101 | 2009-11-20 00:00:00 |           2 |   280 |
| 103 | 2008-05-20 00:00:00 |           4 |   290 |
+-----+---------------------+-------------+--------+

Bây giờ, chúng ta kết hợp hai bảng bởi sử dụng CARTESIAN JOIN trong SQL như sau:

SQL> SELECT  ID, TEN, SOTIEN, NGAY
     FROM SINHVIEN, MUONSACH;

Ví dụ trên sẽ cho kết quả:

+----+----------+--------+---------------------+
| ID | TEN     | SOTIEN | NGAY                |
+----+----------+--------+---------------------+
|  1 | Hoang   |   320 | 2009-10-08 00:00:00 |
|  1 | Hoang   |   250 | 2009-10-08 00:00:00 |
|  1 | Hoang   |   280 | 2009-11-20 00:00:00 |
|  1 | Hoang   |   290 | 2008-05-20 00:00:00 |
|  2 | Viet   |   320 | 2009-10-08 00:00:00 |
|  2 | Viet   |   250 | 2009-10-08 00:00:00 |
|  2 | Viet   |   280 | 2009-11-20 00:00:00 |
|  2 | Viet   |   290 | 2008-05-20 00:00:00 |
|  3 | Thanh  |   320 | 2009-10-08 00:00:00 |
|  3 | Thanh  |   250 | 2009-10-08 00:00:00 |
|  3 | Thanh  |   280 | 2009-11-20 00:00:00 |
|  3 | Thanh  |   290 | 2008-05-20 00:00:00 |
|  4 | Nhan |   320 | 2009-10-08 00:00:00 |
|  4 | Nhan |   250 | 2009-10-08 00:00:00 |
|  4 | Nhan |   280 | 2009-11-20 00:00:00 |
|  4 | Nhan |   290 | 2008-05-20 00:00:00 |
|  5 | Huong   |   320 | 2009-10-08 00:00:00 |
|  5 | Huong   |   250 | 2009-10-08 00:00:00 |
|  5 | Huong   |   280 | 2009-11-20 00:00:00 |
|  5 | Huong   |   290 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+