Sử dụng subquery trong SELECT

Mình muốn quay lại chủ đề subquery trong SELECT một lần nữa. Đặc biệt nhấn mạnh việc query bên trong có thể tham chiếu dữ liệu từ query bên ngoài. Nghe thì đơn giản, nhưng thực ra cũng không hẳn đâu. Cùng đào sâu thêm chút nữa nhé...

Subquery trong SELECT cho phép thêm các cột bổ sung với giá trị tính toán hoặc dữ liệu phụ thuộc vào bản ghi hay bảng khác. Ví dụ, bạn có thể liệt kê danh sách sinh viên kèm điểm trung bình, số lượng khóa học họ đăng ký, hoặc điểm cao nhất hiện tại trong nhóm. Cái này cực tiện khi cần phân tích dữ liệu "tại chỗ", tạo cột tổng hợp mà không cần xử lý trước.

Cơ bản về subquery trong SELECT

Trước khi vào ví dụ, cùng xem qua cú pháp tổng quát. Subquery trong SELECT sẽ như này:

SELECT column1,
       column2,
       (SELECT tổng_hợp_hoặc_điều_kiện FROM bang_khac WHERE dieu_kien) AS ten_cot_moi
FROM bang_chinh;

Lưu ý là subquery trả về một giá trị, xuất hiện trong kết quả như một cột mới. Và dieu_kien có thể tham chiếu đến cột của bang_chinh.

Ví dụ 1: Thêm điểm trung bình của sinh viên

Bắt đầu với một query đơn giản mà hữu ích: mình có bảng students và bảng grades, nơi lưu điểm của sinh viên.

Bảng students:

Bảng grades:

Bây giờ mình muốn lấy danh sách sinh viên với tên và điểm trung bình của họ. Dùng subquery trong SELECT như sau:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade) 
     FROM grades g 
     WHERE g.student_id = s.id) AS average_grade
FROM students s;

Kết quả:

Ở đây subquery (SELECT AVG(g.grade) FROM grades g WHERE g.student_id = s.id) tính điểm trung bình cho từng sinh viên. Nó trả về một giá trị cho mỗi dòng của bảng students, rất tiện khi không muốn JOIN hay tạo VIEW trước.

Ví dụ 2: Đếm số khóa học của từng sinh viên

Giờ mình thêm dữ liệu về sinh viên: họ học bao nhiêu khóa. Có thêm bảng như sau:

Bảng enrollments:

Liệt kê sinh viên với số lượng khóa học họ đăng ký:

SELECT
    s.id,
    s.name,
    (SELECT COUNT(*)
     FROM enrollments e
     WHERE e.student_id = s.id) AS course_count -- tham chiếu đến bảng students của query ngoài
FROM students s;

Kết quả:

Subquery (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) đếm số bản ghi trong bảng enrollments cho từng sinh viên.

Tổng hợp dữ liệu trong subquery

Rất hay dùng subquery trong SELECT để tính dữ liệu tổng hợp. Các hàm như AVG, SUM, COUNT, MAX, MIN giúp xử lý dữ liệu ngay trong query khác.

Ví dụ 3: Tổng điểm của sinh viên

Thêm tổng điểm cho từng sinh viên. Dùng subquery tính tổng tất cả điểm trong bảng grades:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Kết quả:

Subquery này (SELECT SUM(g.grade) FROM grades g WHERE g.student_id = s.id) cộng điểm của từng sinh viên. Nếu sinh viên không có điểm, kết quả sẽ là NULL vì SUM trả về NULL nếu không có giá trị nào.

Giới hạn và khuyến nghị

1. Hiệu năng. Subquery trong SELECT chạy riêng cho từng dòng của bảng chính. Điều này có thể làm chậm đáng kể nếu dữ liệu lớn. Nếu được, hãy thay bằng JOIN hoặc dùng dữ liệu tổng hợp chuẩn bị sẵn. Ví dụ:

SELECT
    s.id,
    s.name,
    g.total_grade
FROM students s
LEFT JOIN (
    SELECT student_id, SUM(grade) AS total_grade
    FROM grades
    GROUP BY student_id
) g ON s.id = g.student_id;

Cách này dùng JOIN tối ưu hơn, vì tổng hợp và đếm chỉ làm một lần.

2. Vấn đề với NULL.

Nếu subquery không có dữ liệu, kết quả sẽ là NULL. Có thể gây bất ngờ. Ví dụ:

SELECT
    s.id,
    s.name,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Nếu sinh viên không có bản ghi trong grades, total_grade sẽ là NULL. Để thay NULL bằng 0, dùng hàm COALESCE:

SELECT
    s.id,
    s.name,
    COALESCE((SELECT SUM(g.grade)
              FROM grades g
              WHERE g.student_id = s.id), 0) AS total_grade
FROM students s;

Đúng rồi, ở đây tham số đầu tiên của hàm COALESCE là

(
    SELECT SUM(g.grade)
    FROM grades g
    WHERE g.student_id = s.id
)

Tối ưu hóa subquery trong SELECT

Để tránh tính toán dư thừa và tăng hiệu năng:

1. Dùng index cho các cột tham gia subquery. Ví dụ, index student_id trong bảng grades sẽ tăng tốc lọc dữ liệu.
2. Thay subquery bằng dữ liệu tổng hợp chuẩn bị sẵn với JOIN nếu có thể.
3. Giới hạn lượng dữ liệu subquery xử lý bằng cách lọc (WHERE).

Ví dụ cuối: kết hợp nhiều subquery

Cùng gom hết kiến thức lại và tạo query hiển thị tên sinh viên, điểm trung bình, số khóa học và tổng điểm:

SELECT
    s.id,
    s.name,
    (SELECT AVG(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS average_grade,
    (SELECT COUNT(*) 
     FROM enrollments e 
     WHERE e.student_id = s.id) AS course_count,
    (SELECT SUM(g.grade)
     FROM grades g
     WHERE g.student_id = s.id) AS total_grade
FROM students s;

Query này trả về profile đầy đủ của sinh viên, tận dụng sức mạnh của subquery. Ta thấy điểm trung bình, tổng điểm và số khóa học mỗi sinh viên đăng ký. Kiểu này rất tiện để lấy thông tin tổng hợp nhanh mà không cần tạo VIEW riêng hay JOIN phức tạp.