Bài 6. JOIN: kết nối nhiều bảng

Khái niệm và lý thuyết

A JOIN B ON A.x = B.y tạo dòng mới từ mỗi cặp dòng A, B thỏa điều kiện. INNER JOIN chỉ giữ cặp khớp; LEFT JOIN giữ mọi dòng của bảng trái, bảng phải không khớp thì NULL (dùng để tìm “sinh viên chưa có điểm”); RIGHT JOIN ngược lại. Luôn đặt bí danh ngắn cho bảng (sv, l, d) và ghi rõ bảng.cột khi trùng tên.

INNER JOIN

SELECT sv.ma_sv, sv.ho_ten, l.ten_lop
FROM sinhvien sv
JOIN lop l ON l.id = sv.lop_id;                -- JOIN = INNER JOIN

-- Ba bảng: bảng điểm đầy đủ
SELECT sv.ho_ten, m.ten_mon, d.diem, d.hoc_ky
FROM diem d
JOIN sinhvien sv ON sv.id = d.sinhvien_id
JOIN monhoc m    ON m.id  = d.monhoc_id
ORDER BY sv.ho_ten, m.ten_mon;

-- JOIN + GROUP BY: điểm TB theo lớp
SELECT l.ten_lop, ROUND(AVG(d.diem), 2) AS diem_tb, COUNT(DISTINCT sv.id) AS so_sv
FROM lop l
JOIN sinhvien sv ON sv.lop_id = l.id
JOIN diem d ON d.sinhvien_id = sv.id
GROUP BY l.id, l.ten_lop;

LEFT JOIN

-- Mọi sinh viên, kể cả chưa có điểm
SELECT sv.ho_ten, d.diem
FROM sinhvien sv
LEFT JOIN diem d ON d.sinhvien_id = sv.id;

-- Sinh viên CHƯA có điểm môn nào
SELECT sv.ho_ten FROM sinhvien sv
LEFT JOIN diem d ON d.sinhvien_id = sv.id
WHERE d.id IS NULL;

-- Số sinh viên mỗi lớp, kể cả lớp 0 sinh viên
SELECT l.ten_lop, COUNT(sv.id) AS so_sv
FROM lop l LEFT JOIN sinhvien sv ON sv.lop_id = l.id
GROUP BY l.id;

Tự nối và truy vấn con

-- Tự nối: cặp sinh viên cùng quê
SELECT a.ho_ten, b.ho_ten, a.que_quan
FROM sinhvien a JOIN sinhvien b ON a.que_quan = b.que_quan AND a.id < b.id;

-- Truy vấn con trong WHERE
SELECT ho_ten FROM sinhvien
WHERE id IN (SELECT sinhvien_id FROM diem WHERE diem >= 9);
SELECT ho_ten FROM sinhvien sv
WHERE EXISTS (SELECT 1 FROM diem d WHERE d.sinhvien_id = sv.id AND d.diem < 5);
-- Truy vấn con trong SELECT và FROM
SELECT ho_ten, (SELECT AVG(diem) FROM diem d WHERE d.sinhvien_id = sv.id) AS diem_tb FROM sinhvien sv;
SELECT * FROM (SELECT sinhvien_id, AVG(diem) AS tb FROM diem GROUP BY sinhvien_id) t WHERE t.tb >= 8;
-- Điểm cao hơn trung bình chung
SELECT * FROM diem WHERE diem > (SELECT AVG(diem) FROM diem);

UNION

SELECT ho_ten AS ten, 'Sinh viên' AS loai FROM sinhvien WHERE lop_id = 1
UNION
SELECT ten_lop, 'Lớp' FROM lop;            -- cùng số cột, bỏ trùng
-- UNION ALL giữ dòng trùng, nhanh hơn