Đang tải trang
Trong bài này
Cô Mai có danh sách đăng ký chỉ gồm mã người và mã lớp. Cô cần tên học viên để gọi điểm danh, nhưng tên lại nằm ở bảng khác. Chép tên vào từng đăng ký sẽ tạo nguy cơ sửa thiếu như bài 13. Ta cần ghép tên vào kết quả khi đọc báo cáo. Tên học viên vẫn chỉ cần lưu ở bảng học viên.
INNER JOIN giữ những cặp dòng có mã khớp
Phép nối (join) kết hợp các dòng từ nhiều bảng theo một điều kiện. Phép nối trong (inner join) chỉ giữ các cặp thỏa điều kiện ấy. Với đăng ký và học viên, ta so enrollments.student_id với students.id. Cột student_id của đăng ký lưu mã id của học viên đã đăng ký.
Trước hết, ta lấy tên học viên cùng mã lớp của từng đăng ký. Bước này chưa cần lấy tên lớp:
Dữ liệu đầu vào
students6 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
enrollments7 dòng| student_id | class_id |
|---|---|
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
Câu lệnh
SELECT students.name, enrollments.class_id
FROM enrollments
INNER JOIN students
ON students.id = enrollments.student_id
ORDER BY students.id, enrollments.class_id;Kết quả
7 dòng
| name | class_id |
|---|---|
| An | 1 |
| An | 3 |
| Bình | 1 |
| Chi | 2 |
| Chi | 3 |
| Dung | 2 |
| Huy | 1 |
Hai dòng đầu đều có tên An và mang mã lớp 1, 3. Mỗi dòng đăng ký được ghép với người mã 1 nên tên An xuất hiện hai lần. Hai dòng cho biết An đăng ký hai lớp khác nhau. Phép nối không tạo thêm học viên trong bảng gốc.
Kết quả hiện có một dòng cho mỗi đăng ký.
Từ khóa ON mở điều kiện quyết định cặp nào được giữ. Bạn có thể đọc cả câu như sau: lấy đăng ký, tìm học viên có mã tương ứng, rồi chọn tên và mã lớp. SQL không tự biết hai cột nào cần so sánh. Người viết truy vấn phải mô tả đúng quan hệ.
studentsbảng trái| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
enrollmentsbảng phải| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
- Có dòng ghép
- Không có dòng ghép, không xuất hiện trong kết quả
Lan vẫn nằm trong bảng students, nhưng không có dòng mang mã 6 trong enrollments. Vì không có đăng ký nào khớp mã của Lan, kết quả không có tên bạn ấy.
Kết quả này phù hợp khi cô Mai chỉ cần liệt kê các đăng ký đã có. Bài 15 sẽ xử lý yêu cầu giữ cả người chưa có lớp.
Phép nối vừa dùng chỉ đọc dữ liệu. Nó không thêm tên vào bảng đăng ký và không xóa Lan khỏi bảng học viên. Sau khi bạn sửa tên trong students, lần đọc báo cáo tiếp theo sẽ lấy tên đã sửa. Bạn không phải đồng bộ nhiều bản sao tên trong các đăng ký.
Bí danh giúp đọc rõ cột thuộc bảng nào
Tên bảng dài làm câu truy vấn khó đọc khi có nhiều cột. Bí danh bảng (table alias) là tên ngắn dùng cho bảng trong một câu lệnh. students AS s đặt tên ngắn s; enrollments AS e đặt tên ngắn e. Cách viết s.name có nghĩa là cột name của bảng đang được gọi là s.
Ta viết lại phép nối và chỉ đọc đăng ký của học viên mã 1:
Dữ liệu đầu vào
students6 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
enrollments7 dòng| student_id | class_id | status |
|---|---|---|
| 1 | 1 | active |
| 1 | 3 | active |
| 2 | 1 | active |
| 3 | 2 | active |
| 3 | 3 | active |
| 4 | 2 | paused |
| 5 | 1 | active |
Câu lệnh
SELECT s.name, e.class_id, e.status
FROM students AS s
INNER JOIN enrollments AS e ON e.student_id = s.id
WHERE s.id = 1
ORDER BY e.class_id;Kết quả
2 dòng
| name | class_id | status |
|---|---|---|
| An | 1 | active |
| An | 3 | active |
Kết quả vẫn là hai đăng ký của An, cả hai đang active. Điều kiện trong ON nối đúng người với đăng ký. Điều kiện trong WHERE chọn riêng người mã 1 từ các dòng đã ghép. Hai điều kiện làm hai việc khác nhau.
| Thành phần | Cách đọc trong ví dụ |
|---|---|
students AS s | Gọi bảng học viên bằng tên ngắn s |
s.id | Mã học viên của bảng students |
e.student_id = s.id | Đăng ký phải thuộc đúng người |
s.id = 1 | Báo cáo chỉ lấy người mã 1 |
Bí danh cột và bí danh bảng cũng khác nhau. s.name AS learner đặt tên cho cột kết quả, không đổi cách gọi bảng. students AS s lại đặt tên cho nguồn dữ liệu. Hãy đọc vị trí của AS để biết nó đang đặt tên cho đối tượng nào.
Khi nhiều bảng có cột trùng tên, bạn nên ghi rõ tên bảng hoặc bí danh trước tên cột. students.id là mã người, còn classes.id là mã lớp. Hai cột cùng tên id nhưng xác định hai đối tượng khác nhau. Hãy kiểm tra quan hệ tham chiếu trước khi viết điều kiện nối.
Nếu nối trực tiếp mã học viên với mã lớp, câu lệnh vẫn chạy:
Dữ liệu đầu vào
students6 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
classes3 dòng| id | name |
|---|---|
| 1 | Python |
| 2 | Scratch |
| 3 | Database |
Câu lệnh
SELECT s.name AS student_name, c.name AS class_name
FROM students AS s
INNER JOIN classes AS c ON s.id = c.id
ORDER BY s.id;Kết quả
3 dòng
| student_name | class_name |
|---|---|
| An | Python |
| Bình | Scratch |
| Chi | Database |
Kết quả là An–Python, Bình–Scratch và Chi–Database. Cặp Bình–Scratch không có trong danh sách đăng ký. Hai cặp còn lại trùng với đăng ký thật chỉ vì mã tình cờ bằng nhau. Muốn biết ai đã đăng ký lớp nào, bạn cần đi qua enrollments.
Đi qua bảng đăng ký để lấy tên lớp
Bây giờ cô Mai cần tên lớp thay cho mã lớp. enrollments chứa mã học viên và mã lớp. Từ hai mã đó, ta có thể tìm tên trong bảng học viên và bảng lớp.
Ta nối học viên bằng student_id, rồi nối lớp bằng class_id. Mỗi dòng kết quả vẫn tương ứng với một đăng ký đã được lưu.
Câu truy vấn dưới chỉ lấy các lớp của An để bạn theo dõi đường nối:
Dữ liệu đầu vào
students6 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
enrollments7 dòng| student_id | class_id |
|---|---|
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
| 3 | 2 |
| 3 | 3 |
| 4 | 2 |
| 5 | 1 |
classes3 dòng| id | name |
|---|---|
| 1 | Python |
| 2 | Scratch |
| 3 | Database |
Câu lệnh
SELECT s.name AS learner, c.name AS class_name
FROM enrollments AS e
INNER JOIN students AS s ON s.id = e.student_id
INNER JOIN classes AS c ON c.id = e.class_id
WHERE s.id = 1
ORDER BY c.id;Kết quả
2 dòng
| learner | class_name |
|---|---|
| An | Python |
| An | Database |
Kết quả có hai dòng: An học lớp Python và An học lớp Database. Hai dòng xuất phát từ hai cặp (1, 1) và (1, 3). Mỗi mã lớp khớp đúng một dòng trong classes. Vì vậy, lấy thêm tên lớp vẫn cho hai dòng đăng ký của An.
Truy vấn không ghép thêm lớp mà An chưa đăng ký. Nếu muốn báo cáo chỉ gồm đăng ký đang học, bạn có thể lọc e.status = 'active'. Giá trị ấy thuộc đăng ký, không thuộc tên người hoặc tên lớp. Dung có một đăng ký paused, nên điều kiện lọc active sẽ loại đăng ký đó.
Phép nối chỉ ghép các cột. Muốn loại đăng ký tạm dừng, bạn phải viết thêm điều kiện lọc trạng thái.
| Cần lấy thông tin | Đi theo cột nào? | Đích tham chiếu |
|---|---|---|
| Tên học viên | e.student_id | s.id |
| Tên lớp | e.class_id | c.id |
| Trạng thái tham gia | Không cần nối thêm | e.status |
Trong trường hợp này, mỗi mã người và mã lớp đều được bảo vệ bằng khóa. Vì vậy mỗi đăng ký tìm đúng một người và đúng một lớp. Nếu ghép bằng tên, hai người trùng tên có thể bị nhận nhầm. SQL vẫn có thể chạy khi bạn viết sai quan hệ cần nối.
Vì vậy, câu lệnh chạy được chưa bảo đảm báo cáo ghép đúng người và lớp.
Bạn hãy kiểm cột đầu ra trước khi dùng DISTINCT. Nếu cần tên người cùng từng lớp, hai dòng của An đều cần giữ. Bỏ một dòng để tên An chỉ xuất hiện một lần sẽ làm mất lớp của người ấy. Danh sách học viên chỉ cần nhắc mỗi người một lần, còn danh sách đăng ký cần giữ từng lớp của mỗi người.
CROSS JOIN tạo mọi lựa chọn chứ không đọc đăng ký
Phép nối chéo (cross join) tạo mọi cặp giữa hai bảng. Mỗi dòng của bảng đầu được ghép với mỗi dòng của bảng sau. Nó hữu ích khi lập danh sách lựa chọn có thể có. Phép nối này không kiểm tra học viên đã đăng ký lớp nào.
Ta chỉ xem các lựa chọn của học viên mã 1 để bảng kết quả gọn:
Dữ liệu đầu vào
students6 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
classes3 dòng| id | name |
|---|---|
| 1 | Python |
| 2 | Scratch |
| 3 | Database |
Câu lệnh
SELECT s.name, c.name AS class_name
FROM students AS s
CROSS JOIN classes AS c
WHERE s.id = 1
ORDER BY c.id;Kết quả
3 dòng
| name | class_name |
|---|---|
| An | Python |
| An | Scratch |
| An | Database |
Kết quả có cả lớp Scratch, bên cạnh Python và Database. Trong dữ liệu mẫu, An chưa đăng ký Scratch. Dòng này chỉ cho biết An có thể chọn lớp Scratch, vì truy vấn ghép An với tất cả các lớp.
Nếu gửi kết quả này như danh sách lớp đang học, báo cáo sẽ sai.
Ta đếm toàn bộ các lựa chọn rồi so với số đăng ký đã có:
Dữ liệu đầu vào
students6 dòng| id | name |
|---|---|
| 1 | An |
| 2 | Bình |
| 3 | Chi |
| 4 | Dung |
| 5 | Huy |
| 6 | Lan |
classes3 dòng| id | name |
|---|---|
| 1 | Python |
| 2 | Scratch |
| 3 | Database |
Câu lệnh
SELECT COUNT(*) AS possible_pairs
FROM students AS s
CROSS JOIN classes AS c;Kết quả
1 dòng
| possible_pairs |
|---|
| 18 |
Kết quả là 18 vì mỗi người được ghép với đủ ba lớp. Trong khi đó, bảng enrollments chỉ có bảy đăng ký. Số 18 đếm các lựa chọn có thể có, còn số bảy đếm những đăng ký đã lưu. Thêm DISTINCT không thể biến các lựa chọn chưa đăng ký thành đăng ký thật.
| Câu hỏi | Nguồn và phép nối phù hợp |
|---|---|
| Ai đã đăng ký lớp nào? | Đi qua enrollments và nối theo mã |
| Mỗi người có thể chọn những lớp nào? | students CROSS JOIN classes |
| Vì sao thiếu người chưa đăng ký? | INNER JOIN chỉ giữ cặp khớp |
Trong SQLite, viết JOIN mà không có điều kiện cũng có thể tạo mọi cặp. SQLite có thể chạy câu lệnh đó mà không báo thiếu ON. Vì vậy, sau mỗi phép nối theo mã, hãy kiểm tra xem bạn đã so đúng hai cột tham chiếu hay chưa. Sau đó kiểm riêng một người nhiều lớp và một người chưa có lớp.
Lỗi thường gặp
| Lỗi | Vì sao sai | Cách sửa |
|---|---|---|
Ghép s.id = c.id | Mã người không phải mã lớp đã đăng ký | Đi qua enrollments |
Dùng CROSS JOIN để gọi điểm danh | Có cả lớp chưa đăng ký | Nối bằng khóa người và lớp |
Xóa tên lặp bằng DISTINCT khi cần từng lớp | Có thể làm mất quan hệ | Xác định một dòng báo cáo là gì |
| Nghĩ Lan bị xóa khỏi hệ thống | Lan chỉ không có cặp để trả về | Phân biệt bảng gốc với kết quả |
Tóm tắt
INNER JOINgiữ các cặp thỏa điều kiệnON. Bạn cũng có thể viếtJOINthay choINNER JOIN.- Bí danh giúp chỉ rõ nguồn của mỗi cột và không đổi bảng gốc.
- Bảng liên kết dẫn từ người sang lớp bằng hai khóa ngoại riêng.
CROSS JOINtạo mọi cặp lựa chọn, kể cả cặp chưa đăng ký.