Cơ sở dữ liệu

Thực hành LEFT JOIN để giữ lớp chưa có đăng ký

Đếm đăng ký theo lớp và giữ dòng kết quả bằng 0.

Thực hànhThực hành4 phút đọcPython

Lớp C còn một hàng ghép NULL; cần đếm cột id của phía đăng ký. Mở hình lớn ↗

1. Đề bài

Có ba lớp A, B, C. A có hai đăng ký, B có một, C chưa có đăng ký. Viết truy vấn trả đúng ba dòng và hiển thị 0 cho C. Dữ liệu có khóa riêng của từng đăng ký.

2. Giải thích hai lựa chọn SQL

Dùng LEFT JOIN từ lớp sang đăng ký. Sau đó dùng COUNT(d.id), vì khi lớp không khớp thì d.id là NULL. Nếu dùng COUNT(*), dòng giữ chỗ của lớp C vẫn bị đếm thành 1.

3. Đối chiếu lời giải

Mẫu tạo SQLite trong bộ nhớ và chạy truy vấn. Kết quả là A 2, B 1, C 0. Hãy đổi sang INNER JOIN rồi nhận xét dòng bị mất. Thử COUNT(*) để thấy C có số đếm sai.

Chạy ví dụ bằng python hoc_sql_giu_lop_chua_dang_ky.py.

Môi trường thực hành. Python 3.10 trở lên. Ví dụ dùng thư viện chuẩn Python.

Python · hoc_sql_giu_lop_chua_dang_ky.pyTải mã nguồn

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE lop(ma TEXT PRIMARY KEY);
        CREATE TABLE dang_ky(id INTEGER PRIMARY KEY, ma_lop TEXT);
        INSERT INTO lop VALUES ('A'), ('B'), ('C');
        INSERT INTO dang_ky VALUES (1, 'A'), (2, 'A'), (3, 'B');
    """)
    ket_qua = db.execute("""
        SELECT l.ma, COUNT(d.id)
        FROM lop AS l LEFT JOIN dang_ky AS d ON d.ma_lop = l.ma
        GROUP BY l.ma
        ORDER BY l.ma
    """).fetchall()
assert ket_qua == [("A", 2), ("B", 1), ("C", 0)]
print(ket_qua)

4. Bài tập mở rộng

Thêm trạng thái đã hủy và chỉ đếm đăng ký đang hiệu lực. Đặt điều kiện trạng thái vào điều kiện ghép để lớp không có bản ghi hiệu lực vẫn hiện số 0. So với cách đặt trong WHERE.

Đọc thêm Tài liệu truy vấn SQLite.