Division, Grouping & Outer Joins

01 · DIVISION IN SQL

What Is Division?

Keywords to Watch For

When a problem contains phrases like all, every, the entirety of, "bought all products", "participated in all projects", "registered for all courses" - think of division right away.

A Visual Example

Suppose we have 3 tables:

We need to find: customers who bought all products.

Result: KH01. This is exactly the way of thinking behind division.

The Core Idea

The problem "Find A that has done all of B" can be understood as:

In SQL, this idea is typically implemented with GROUP BY + HAVING COUNT(DISTINCT ...).

02 · BASIC DIVISION

Basic Division

Model: R(A, B, C, D, E) and S(D, E). Division R / S finds the values A, B, C of R that relate to all values D, E in S.

The GROUP BY + HAVING Formula

SELECT B1.<Column 1>, ...
FROM <Table 1> B1
JOIN <Table 3> B3
    ON B1.<Column 1> = B3.<Column 1>
GROUP BY B1.<Column 1>
HAVING COUNT(DISTINCT B3.<Column 2>) =
(
    SELECT COUNT(DISTINCT B2.<Column 2>)
    FROM <Table 2> B2
);

Example 1 - Customers Who Bought All Products

Tables: KhachHang(MaKH, TenKH), SanPham(MaSP, TenSP), MuaHang(MaKH, MaSP).

SELECT KH.MaKH
FROM KhachHang KH
JOIN MuaHang MH
    ON KH.MaKH = MH.MaKH
GROUP BY KH.MaKH
HAVING COUNT(DISTINCT MH.MaSP) =
(
    SELECT COUNT(*)
    FROM SanPham
);

Breaking Down the SQL

Step 1 - Join customers to their purchase history: FROM KhachHang KH JOIN MuaHang MH ON KH.MaKH = MH.MaKH

Step 2 - Group by each customer with GROUP BY KH.MaKH: KH01 ➜ SP01,SP02,SP03, KH02 ➜ SP01,SP02, KH03 ➜ SP01,SP03.

Step 3 - Count distinct products purchased with COUNT(DISTINCT MH.MaSP): KH01 ➜ 3, KH02 ➜ 2, KH03 ➜ 2.

Step 4 - Count the total number of products: SELECT COUNT(*) FROM SanPham ➜ 3.

Step 5 - Compare: number of products the customer bought = total number of products that exist ➜ the customer bought all of them.

Example 2 - Students Registered for All Required Courses

Tables: SinhVien(MaSV, TenSV), MonHoc(MaMH, TenMH), DangKy(MaSV, MaMH).

SELECT SV.MaSV
FROM SinhVien SV
JOIN DangKy DK
    ON SV.MaSV = DK.MaSV
GROUP BY SV.MaSV
HAVING COUNT(DISTINCT DK.MaMH) =
(
    SELECT COUNT(*)
    FROM MonHoc
);

Student A registered for 5 courses, total required courses = 5 ➜ passes. Student B registered for 4 courses, total required courses = 5 ➜ fails.

Example 3 - Employees Who Participated in All Projects

Tables: NhanVien(MaNV, TenNV), DuAn(MaDA, TenDA), ThamGia(MaNV, MaDA).

SELECT NV.MaNV
FROM NhanVien NV
JOIN ThamGia TG
    ON NV.MaNV = TG.MaNV
GROUP BY NV.MaNV
HAVING COUNT(DISTINCT TG.MaDA) =
(
    SELECT COUNT(*)
    FROM DuAn
);

03 · CONDITIONAL DIVISION

Conditional Division

Example: "Find customers who bought all products in the Electronics category" - this is no longer "all products," but "all products WHERE DanhMuc = 'Điện tử'".

The set to check is only SP01, SP02, not all 4 products.

SELECT KH.MaKH
FROM KhachHang KH
JOIN MuaHang MH
    ON KH.MaKH = MH.MaKH
JOIN SanPham SP
    ON MH.MaSP = SP.MaSP
WHERE SP.DanhMuc = N'Điện tử'
GROUP BY KH.MaKH
HAVING COUNT(DISTINCT MH.MaSP) =
(
    SELECT COUNT(*)
    FROM SanPham
    WHERE DanhMuc = N'Điện tử'
);

The Thinking Formula

Example - Required Courses Worth More Than 3 Credits

Problem: "Find students who registered for all required courses worth more than 3 credits." First determine the set of courses to check using WHERE SoTinChi > 3, then check whether each student registered for the entire set.

SELECT SV.MaSV
FROM SinhVien SV
JOIN DangKy DK
    ON SV.MaSV = DK.MaSV
JOIN MonHoc MH
    ON MH.MaMH = DK.MaMH
WHERE MH.SoTinChi > 3
GROUP BY SV.MaSV
HAVING COUNT(DISTINCT DK.MaMH) =
(
    SELECT COUNT(*)
    FROM MonHoc
    WHERE SoTinChi > 3
);

04 · DIVISION WITH NOT EXISTS

Division with NOT EXISTS

This is the second way to solve "all" problems - using negation to check whether any value fails to satisfy the condition.

The Idea: "All" ➜ "No Missing Case Exists"

"The customer bought all electronics products" can be restated as "There does not exist an electronics product that the customer has not bought."

The Double NOT EXISTS Structure

SELECT ...
FROM A
WHERE NOT EXISTS
(
    SELECT *
    FROM B
    WHERE NOT EXISTS
    (
        SELECT *
        FROM C
        WHERE ...
    )
);

Customer Example

Problem: "Find customers who bought all products in the Electronics category."

SELECT KH.MaKH
FROM KhachHang KH
WHERE NOT EXISTS
(
    SELECT *
    FROM SanPham SP
    WHERE SP.DanhMuc = N'Điện tử'
      AND NOT EXISTS
      (
          SELECT *
          FROM MuaHang MH
          WHERE MH.MaKH = KH.MaKH
            AND MH.MaSP = SP.MaSP
      )
);

Reading the SQL in Plain English

Don't read SQL line by line. Instead, read: "Get customers for whom there does not exist any electronics product that the customer hasn't bought." If no such product exists ➜ the customer has bought all of them.

Student Example

Problem: "Find students who registered for all required courses worth more than 3 credits."

SELECT SV.MaSV
FROM SinhVien SV
WHERE NOT EXISTS
(
    SELECT *
    FROM MonHoc MH
    WHERE MH.SoTinChi > 3
      AND NOT EXISTS
      (
          SELECT *
          FROM DangKy DK
          WHERE DK.MaMH = MH.MaMH
            AND DK.MaSV = SV.MaSV
      )
);

Three Ways to Recognize Division

05 · AGGREGATE FUNCTIONS & GROUPING

Aggregate Functions

Aggregate functions let you perform calculations over a set of multiple rows.

Distinguishing the 3 Kinds of COUNT

SELECT COUNT(*) FROM NhanVien; ➜ 5 (counts all rows)

SELECT COUNT(Luong) FROM NhanVien; ➜ 4 (NV03 has Luong = NULL)

SELECT COUNT(DISTINCT ChucVu) FROM NhanVien; ➜ 2 (Dev, Tester - NULL doesn't count)

SUM, AVG, MAX, MIN

SELECT SUM(Luong) AS TongLuong
FROM NhanVien;

SELECT AVG(Luong) AS LuongTrungBinh
FROM NhanVien;

Note: AVG ignores NULL values.

SELECT
    MAX(Luong) AS LuongCaoNhat,
    MIN(Luong) AS LuongThapNhat
FROM NhanVien;

How Does GROUP BY Work?

Example data:

GROUP BY Phong splits the data into groups P01: NV01, NV02 and P02: NV03, NV04, and only then runs MAX/MIN/AVG/SUM/COUNT on each group.

SELECT
    Phong,
    MAX(Luong) AS LuongCaoNhat,
    MIN(Luong) AS LuongThapNhat,
    AVG(Luong) AS LuongTrungBinh
FROM NhanVien
GROUP BY Phong;

Counting Employees per Department

LEFT JOIN keeps a department visible even when it has no employees yet:

SELECT
    PB.MaPH,
    PB.TenPH,
    COUNT(NV.MaNV) AS SLNV
FROM PHONGBAN PB
LEFT JOIN NHANVIEN NV
    ON NV.Phong = PB.MaPH
GROUP BY
    PB.MaPH,
    PB.TenPH;

HAVING - Filtering Groups

Problem: "Find departments with more than 10 employees, showing department code, name, and employee count, sorted in descending order."

SELECT
    PB.MaPH,
    PB.TenPH,
    COUNT(NV.MaNV) AS SLNV
FROM PHONGBAN PB
LEFT JOIN NHANVIEN NV
    ON NV.Phong = PB.MaPH
GROUP BY
    PB.MaPH,
    PB.TenPH
HAVING COUNT(NV.MaNV) > 10
ORDER BY
    COUNT(NV.MaNV) DESC;

WHERE vs. HAVING - Know the Difference

WHERE filters rows before grouping (WHERE Luong > 10000000). HAVING filters groups after grouping (HAVING COUNT(*) > 10).

06 · SELECT TOP

SELECT TOP

TOP is used to limit the number of rows returned.

SELECT TOP 4
    HoTen,
    Luong
FROM NhanVien;

TOP + ORDER BY

To get the 4 highest-paid employees:

SELECT TOP 4
    HoTen,
    Luong
FROM NhanVien
ORDER BY Luong DESC;

TOP ... WITH TIES

If the 4th-highest salary is 15 million and someone else also earns 15 million, WITH TIES will include them too:

SELECT TOP 4 WITH TIES
    HoTen,
    Luong
FROM NhanVien
ORDER BY Luong DESC;

DISTINCT TOP

Get the first 4 distinct job titles:

SELECT DISTINCT TOP 4
    ChucVu
FROM NhanVien;

TOP + GROUP BY + HAVING

Problem: "Get the 4 departments with the largest total payroll, considering only departments with a total salary above 5 million."

SELECT TOP 4
    MaPB,
    SUM(Luong) AS TongLuong
FROM NhanVien
GROUP BY MaPB
HAVING SUM(Luong) > 5000000
ORDER BY SUM(Luong) DESC;

07 · OUTER JOINS

Outer Joins

INNER JOIN only returns rows with matching data. But sometimes you need to know about objects with no related data - for example, "show employees who aren't assigned to any project." With INNER JOIN, employees with no project would be excluded since there's no corresponding record. That's why LEFT JOIN is needed.

Finding Employees With No Project Assignment

SELECT
    NV.MaNV,
    NV.HoTen
FROM NHANVIEN NV
LEFT OUTER JOIN PHANCONG PC
    ON NV.MaNV = PC.MaNV
WHERE PC.MaDA IS NULL;

The Three Types of OUTER JOIN

08 · QUICK REFERENCE TABLE

SQL Week 3 Cheat Sheet

Reading a Problem ➜ Recognizing the Pattern

Week 3 Summary

Week 1 was about designing & manipulating data, Week 2 was about querying & combining data, and Week 3 is advanced querying: division ("all" ➜ GROUP BY+HAVING COUNT or NOT EXISTS×2), grouping (Aggregates, TOP/HAVING), and outer joins ("none" ➜ LEFT JOIN+IS NULL).

09 · PRACTICE EXERCISES

Comprehensive Practice

Beyond the official questions 26-53, here are extra practice problems to deepen your understanding of Week 3's way of thinking - not part of the BTTH3 submission. Each has a hidden hint - click to reveal.

This is a great exercise for understanding that a single SQL problem can have multiple valid solutions.