SQL-5.3 Subqueries & CTE

เมื่อ query เดียวไม่พอ — คุณต้อง query ซ้อน query เช่น “หานักเรียนที่ได้คะแนนสูงกว่าค่าเฉลี่ย” — ค่าเฉลี่ยต้อง query ก่อน แล้วค่อยเอามาเปรียบเทียบ

Subquery ใน WHERE

SQL

-- สร้างตาราง scores
CREATE TABLE scores (
    score_id SERIAL PRIMARY KEY,
    student_id VARCHAR(6),
    course_id VARCHAR(6),
    score NUMERIC(5,2)
);

INSERT INTO scores VALUES (1,'STD001','CS101',85.50);
INSERT INTO scores VALUES (2,'STD001','CS102',72.00);
INSERT INTO scores VALUES (3,'STD002','CS101',92.00);
INSERT INTO scores VALUES (4,'STD002','CS103',88.50);

-- หานักเรียนที่ได้คะแนนสูงกว่าค่าเฉลี่ย
SELECT s.name, sc.score
FROM students s
INNER JOIN scores sc ON s.student_id = sc.student_id
WHERE sc.score > (
    SELECT AVG(score) FROM scores   -- subquery: คำนวณค่าเฉลี่ย
);
-- ค่าเฉลี่ย = 84.50
-- ได้: สมชาย (85.50), สมหญิง (92.00, 88.50)

-- หานักเรียนที่ลงวิชา CS101
SELECT name FROM students
WHERE student_id IN (
    SELECT student_id FROM enrollments
    WHERE course_id = 'CS101'
);
-- ได้: สมชาย, สมหญิง

Tip

Subquery ใน WHERE ทำงานเหมือน “กรอง 2 ชั้น” — ชั้นในคำนวณค่า, ชั้นนอกใช้ค่านั้นเป็นเงื่อนไข

Google Sheets

Sheets ไม่มี subquery — แต่สามารถซ้อน function ได้:

=FILTER(B:B, C:C > AVERAGE(C:C))

AVERAGE(C:C) ทำหน้าที่เหมือน subquery — คำนวณค่าเฉลี่ยก่อน แล้วใช้เป็นเงื่อนไข

Python

avg_score = df['score'].mean()
above_avg = df[df['score'] > avg_score]
print(above_avg)

C

// คำนวณค่าเฉลี่ยก่อน (subquery)
double scores[] = {85.5, 72.0, 92.0, 88.5};
double sum = 0;
for (int i = 0; i < 4; i++) sum += scores[i];
double avg = sum / 4;

// แล้วใช้เป็นเงื่อนไข (outer query)
for (int i = 0; i < 4; i++) {
    if (scores[i] > avg)
        printf("Score %.1f > avg %.1f\n", scores[i], avg);
}

Subquery ใน FROM (Derived Table)

SQL

-- นับจำนวนวิชาที่แต่ละคนลง แล้วดึงเฉพาะคนที่ลง >= 2 วิชา
SELECT name, course_count
FROM (
    SELECT s.name, COUNT(e.course_id) AS course_count
    FROM students s
    LEFT JOIN enrollments e ON s.student_id = e.student_id
    GROUP BY s.name
) AS student_courses          -- ต้องตั้งชื่อ alias
WHERE course_count >= 2;
-- ได้: สมชาย (2), สมหญิง (2)

Caution

Subquery ใน FROM ต้องมี alias (ชื่อ) เสมอ — AS student_courses ถ้าลืมจะ error

Google Sheets

ใช้ QUERY function ซ้อน QUERY function (ยากมาก — ไม่แนะนำ)

Python

# chain operations — Python อ่านง่ายกว่า SQL ตรงนี้
result = (df.groupby('name')['course_id']
          .count()
          .reset_index(name='course_count')
          .query('course_count >= 2'))

C

// ใน C ต้อง 2 loops:
// loop 1: สร้าง intermediate result (subquery)
// loop 2: กรอง intermediate result

CTE — Common Table Expression (WITH Clause)

SQL

CTE ทำสิ่งเดียวกับ subquery แต่ อ่านง่ายกว่ามาก:

-- แบบ subquery (อ่านยาก):
SELECT name, course_count
FROM (
    SELECT s.name, COUNT(e.course_id) AS course_count
    FROM students s
    LEFT JOIN enrollments e ON s.student_id = e.student_id
    GROUP BY s.name
) AS student_courses
WHERE course_count >= 2;

-- แบบ CTE (อ่านง่าย):
WITH student_courses AS (
    SELECT s.name, COUNT(e.course_id) AS course_count
    FROM students s
    LEFT JOIN enrollments e ON s.student_id = e.student_id
    GROUP BY s.name
)
SELECT name, course_count
FROM student_courses
WHERE course_count >= 2;

CTE หลายชั้น — pipeline style:

WITH
-- ขั้น 1: รวมคะแนน
student_scores AS (
    SELECT student_id, AVG(score) AS avg_score
    FROM scores
    GROUP BY student_id
),
-- ขั้น 2: เพิ่มชื่อ
scored_students AS (
    SELECT s.name, ss.avg_score
    FROM students s
    INNER JOIN student_scores ss ON s.student_id = ss.student_id
)
-- ขั้น 3: กรองเฉพาะคนที่เกิน 80
SELECT name, ROUND(avg_score, 2) AS avg_score
FROM scored_students
WHERE avg_score > 80
ORDER BY avg_score DESC;

Tip

CTE อ่านจากบนลงล่างเหมือน pipeline — แต่ละ WITH block ทำงานทีละขั้น ดีกว่า subquery ที่ต้องอ่านจากในสุดออกนอก

Google Sheets

Sheets ไม่มี CTE — ต้องใช้ helper columns แยกขั้นตอนแทน ซึ่งก็คือแนวคิดเดียวกัน: ทำทีละขั้น ไม่ยัดทุกอย่างในสูตรเดียว

Python

# Python pipeline = method chaining
# เทียบเท่า CTE:
step1 = scores.groupby('student_id')['score'].mean().reset_index()
step1.columns = ['student_id', 'avg_score']

step2 = students.merge(step1, on='student_id')

result = step2[step2['avg_score'] > 80].sort_values('avg_score', ascending=False)
print(result)

C

// CTE ใน C = intermediate variables
// step 1: คำนวณ avg
double avg_scores[MAX_STUDENTS];
// step 2: merge กับ names
// step 3: filter
// C บังคับให้ทำทีละขั้นอยู่แล้ว

เมื่อไหร่ใช้อะไร

สถานการณ์	ใช้
ค่าเดียวสำหรับ comparison	Subquery ใน WHERE
list ค่าสำหรับ IN	Subquery ใน WHERE
ผลลัพธ์ intermediate ที่ต้อง query ต่อ	Subquery ใน FROM หรือ CTE
Query ยาวๆ ที่ต้องอ่านง่าย	CTE (WITH) — แนะนำเสมอ
ใช้ intermediate result หลายครั้ง	CTE — ประกาศครั้งเดียว ใช้ซ้ำได้