Bài 7: JOIN – Kết Hợp Nhiều Bảng
Giai đoạn 2 – Truy Vấn Dữ Liệu · Mục tiêu: Hiểu và sử dụng thành thạo các loại JOIN để kết hợp dữ liệu từ nhiều bảng.
1. Tại sao cần JOIN?
Trong CSDL thực tế, dữ liệu được tách ra nhiều bảng để tránh trùng lặp. Khi cần xem thông tin đầy đủ, ta phải kết hợp (JOIN) các bảng lại.
Ví dụ minh hoạ
Bảng KhachHang:
| MaKH | HoTen | DienThoai |
|---|---|---|
| 1 | Nguyễn Văn An | 0901234567 |
| 2 | Trần Thị Bình | 0912345678 |
| 3 | Lê Minh Châu | 0923456789 |
Bảng DonHang:
| MaDH | MaKH | SanPham | TongTien |
|---|---|---|---|
| 101 | 1 | iPhone 15 | 28990000 |
| 102 | 1 | AirPods | 5990000 |
| 103 | 2 | MacBook | 27490000 |
→ Câu hỏi: "Khách hàng nào đã mua gì?" → Cần JOIN 2 bảng.
2. INNER JOIN – Kết hợp các dòng khớp nhau
INNER JOIN chỉ trả về những dòng có giá trị khớp ở cả hai bảng.
Cú pháp
SELECT cot1, cot2, ...
FROM bang_A
INNER JOIN bang_B ON bang_A.cot_chung = bang_B.cot_chung;
Ví dụ
-- Liệt kê đơn hàng kèm tên khách hàng
SELECT
DonHang.MaDH,
KhachHang.HoTen,
KhachHang.DienThoai,
DonHang.SanPham,
DonHang.TongTien
FROM DonHang
INNER JOIN KhachHang ON DonHang.MaKH = KhachHang.MaKH;
Kết quả:
| MaDH | HoTen | DienThoai | SanPham | TongTien |
|---|---|---|---|---|
| 101 | Nguyễn Văn An | 0901234567 | iPhone 15 | 28990000 |
| 102 | Nguyễn Văn An | 0901234567 | AirPods | 5990000 |
| 103 | Trần Thị Bình | 0912345678 | MacBook | 27490000 |
→ Lê Minh Châu (MaKH = 3) không xuất hiện vì chưa có đơn hàng.
Dùng Alias để viết ngắn gọn
SELECT
dh.MaDH,
kh.HoTen,
dh.SanPham,
dh.TongTien
FROM DonHang AS dh
INNER JOIN KhachHang AS kh ON dh.MaKH = kh.MaKH;
3. LEFT JOIN – Lấy tất cả từ bảng bên trái
LEFT JOIN trả về tất cả dòng từ bảng bên trái, dù bảng bên phải không có dòng khớp (sẽ hiển thị NULL).
-- Liệt kê TẤT CẢ khách hàng, kể cả chưa mua hàng
SELECT
kh.HoTen,
dh.MaDH,
dh.SanPham,
dh.TongTien
FROM KhachHang AS kh
LEFT JOIN DonHang AS dh ON kh.MaKH = dh.MaKH;
Kết quả:
| HoTen | MaDH | SanPham | TongTien |
|---|---|---|---|
| Nguyễn Văn An | 101 | iPhone 15 | 28990000 |
| Nguyễn Văn An | 102 | AirPods | 5990000 |
| Trần Thị Bình | 103 | MacBook | 27490000 |
| Lê Minh Châu | NULL | NULL | NULL |
→ Lê Minh Châu xuất hiện với các cột đơn hàng = NULL.
Tìm khách hàng chưa mua hàng
SELECT kh.HoTen, kh.DienThoai
FROM KhachHang AS kh
LEFT JOIN DonHang AS dh ON kh.MaKH = dh.MaKH
WHERE dh.MaDH IS NULL;
4. RIGHT JOIN – Lấy tất cả từ bảng bên phải
RIGHT JOIN ngược lại với LEFT JOIN: trả về tất cả dòng từ bảng bên phải.
-- Tất cả đơn hàng, kể cả đơn hàng không có thông tin khách (hiếm gặp)
SELECT
kh.HoTen,
dh.MaDH,
dh.SanPham
FROM KhachHang AS kh
RIGHT JOIN DonHang AS dh ON kh.MaKH = dh.MaKH;
:::tip LEFT JOIN vs RIGHT JOIN
Trong thực tế, hầu hết mọi người sử dụng LEFT JOIN và đổi thứ tự bảng thay vì dùng RIGHT JOIN. Cả hai cho kết quả giống nhau:
-- Hai câu này cho kết quả giống nhau:
SELECT * FROM A LEFT JOIN B ON A.id = B.id;
SELECT * FROM B RIGHT JOIN A ON A.id = B.id;
:::
5. FULL JOIN – Lấy tất cả từ cả hai bảng
FULL JOIN trả về tất cả dòng từ cả hai bảng. Dòng không khớp sẽ có NULL.
:::warning MySQL không hỗ trợ FULL JOIN trực tiếp
MySQL không có FULL OUTER JOIN. Cách giải quyết:
-- Mô phỏng FULL JOIN bằng UNION
SELECT kh.HoTen, dh.MaDH, dh.SanPham
FROM KhachHang AS kh
LEFT JOIN DonHang AS dh ON kh.MaKH = dh.MaKH
UNION
SELECT kh.HoTen, dh.MaDH, dh.SanPham
FROM KhachHang AS kh
RIGHT JOIN DonHang AS dh ON kh.MaKH = dh.MaKH;
:::
6. CROSS JOIN – Tích Descartes
CROSS JOIN kết hợp mỗi dòng của bảng A với mỗi dòng của bảng B.
-- Mỗi nhân viên × mỗi sản phẩm
SELECT nv.HoTen, sp.TenSP
FROM NhanVien AS nv
CROSS JOIN SanPham AS sp;
-- Nếu NhanVien có 5 dòng, SanPham có 3 dòng → kết quả: 5 × 3 = 15 dòng
:::caution Cẩn thận với CROSS JOIN CROSS JOIN tạo ra rất nhiều dòng (N × M). Chỉ dùng khi thực sự cần tạo tất cả tổ hợp. :::
7. Self Join – Tự kết hợp với chính mình
Một bảng JOIN với chính nó, hữu ích khi bảng có quan hệ cha-con.
-- Bảng NhanVien có cột MaQuanLy tham chiếu đến MaNV
SELECT
nv.HoTen AS NhanVien,
ql.HoTen AS QuanLy
FROM NhanVien AS nv
LEFT JOIN NhanVien AS ql ON nv.MaQuanLy = ql.MaNV;
Kết quả:
| NhanVien | QuanLy |
|---|---|
| Nguyễn Văn An | Lê Minh Châu |
| Trần Thị Bình | Lý Kim Khoa |
| Lê Minh Châu | NULL |
| ... | ... |
8. JOIN nhiều bảng
-- Đơn hàng + Khách hàng + Nhân viên bán hàng
SELECT
dh.MaDH,
kh.HoTen AS KhachHang,
nv.HoTen AS NhanVienBan,
dh.SanPham,
dh.TongTien,
dh.NgayDat
FROM DonHang AS dh
INNER JOIN KhachHang AS kh ON dh.MaKH = kh.MaKH
INNER JOIN NhanVien AS nv ON dh.MaNV = nv.MaNV
ORDER BY dh.NgayDat DESC;
JOIN 4 bảng (Chi tiết đơn hàng)
SELECT
dh.MaDH,
kh.HoTen AS KhachHang,
sp.TenSP,
ct.SoLuong,
ct.DonGia,
ct.SoLuong * ct.DonGia AS ThanhTien
FROM ChiTietDonHang AS ct
INNER JOIN DonHang AS dh ON ct.MaDH = dh.MaDH
INNER JOIN KhachHang AS kh ON dh.MaKH = kh.MaKH
INNER JOIN SanPham AS sp ON ct.MaSP = sp.MaSP;
9. Bảng tóm tắt các loại JOIN
| Loại JOIN | Mô tả | Kết quả |
|---|---|---|
INNER JOIN | Chỉ lấy dòng khớp ở cả hai bảng | A ∩ B |
LEFT JOIN | Tất cả bảng trái + khớp bảng phải | A + (A ∩ B) |
RIGHT JOIN | Tất cả bảng phải + khớp bảng trái | B + (A ∩ B) |
FULL JOIN | Tất cả cả hai bảng | A ∪ B |
CROSS JOIN | Tích Descartes (mọi tổ hợp) | A × B |
Self JOIN | Bảng JOIN với chính nó | Quan hệ cha-con |
Bài tập thực hành
Dữ liệu mẫu
CREATE DATABASE IF NOT EXISTS BaiTapJoin;
USE BaiTapJoin;
CREATE TABLE PhongBan (
MaPB INT PRIMARY KEY,
TenPB VARCHAR(100),
DiaDiem VARCHAR(100)
);
CREATE TABLE NhanVien (
MaNV INT AUTO_INCREMENT PRIMARY KEY,
HoTen VARCHAR(100) NOT NULL,
MaPB INT,
Luong DECIMAL(12,2),
MaQuanLy INT,
FOREIGN KEY (MaPB) REFERENCES PhongBan(MaPB),
FOREIGN KEY (MaQuanLy) REFERENCES NhanVien(MaNV)
);
CREATE TABLE DuAn (
MaDA INT AUTO_INCREMENT PRIMARY KEY,
TenDA VARCHAR(200),
NgayBatDau DATE,
NgayKetThuc DATE
);
CREATE TABLE PhanCong (
MaNV INT,
MaDA INT,
VaiTro VARCHAR(50),
PRIMARY KEY (MaNV, MaDA),
FOREIGN KEY (MaNV) REFERENCES NhanVien(MaNV),
FOREIGN KEY (MaDA) REFERENCES DuAn(MaDA)
);
INSERT INTO PhongBan VALUES
(1, 'Kỹ thuật', 'Tầng 3'),
(2, 'Kinh doanh', 'Tầng 2'),
(3, 'Nhân sự', 'Tầng 1'),
(4, 'Marketing', 'Tầng 4');
INSERT INTO NhanVien (HoTen, MaPB, Luong, MaQuanLy) VALUES
('Nguyễn Văn An', 1, 30000000, NULL),
('Trần Thị Bình', 1, 20000000, 1),
('Lê Minh Châu', 2, 25000000, NULL),
('Phạm Đức Dũng', 2, 15000000, 3),
('Hoàng Thị Em', 3, 18000000, NULL),
('Võ Văn Phong', NULL, 12000000, 1);
INSERT INTO DuAn VALUES
(1, 'Website Bán Hàng', '2024-01-01', '2024-06-30'),
(2, 'App Di Động', '2024-03-01', '2024-12-31'),
(3, 'Hệ Thống CRM', '2024-06-01', NULL);
INSERT INTO PhanCong VALUES
(1, 1, 'Trưởng nhóm'),
(2, 1, 'Lập trình viên'),
(1, 2, 'Kiến trúc sư'),
(4, 2, 'Kinh doanh'),
(2, 3, 'Lập trình viên'),
(5, 3, 'Nhân sự');
Bài tập
- Liệt kê nhân viên kèm tên phòng ban (INNER JOIN).
- Liệt kê tất cả nhân viên kèm phòng ban, kể cả nhân viên chưa thuộc phòng ban nào (LEFT JOIN).
- Liệt kê tất cả phòng ban, kể cả phòng ban chưa có nhân viên (LEFT JOIN đổi thứ tự).
- Liệt kê nhân viên và tên quản lý của họ (Self Join).
- Liệt kê nhân viên, dự án họ tham gia và vai trò (JOIN 3 bảng).
- Tìm nhân viên chưa tham gia dự án nào.
- Tìm dự án chưa có ai tham gia.
- Thống kê số nhân viên và lương trung bình theo phòng ban (JOIN + GROUP BY).
- Tìm dự án có nhiều hơn 2 thành viên.
- Liệt kê nhân viên tham gia nhiều hơn 1 dự án, kèm tên phòng ban.
Quay lại: Roadmap · Bài tiếp: Bài 8 – Truy Vấn Con (Subquery)