SQL Part 3 — Subqueries & Database Modification

วิดีโอที่อาจารย์ให้ดูก่อนเรียนบทนี้:

8.1Nested Subqueries ภาพรวม

Subquery คือ query ที่ซ้อนอยู่ภายใน query อื่น ใช้ได้ใน 3 ที่:

นอกจากนี้ WITH clause ยังให้เรานิยามตารางชั่วคราวที่ใช้ได้เฉพาะใน query นั้น

8.2Subqueries ใน WHERE clause

Set membership — IN / NOT IN

-- วิชาที่เปิดสอนทั้ง Fall 2009 และ Spring 2010
SELECT DISTINCT course_id
FROM   section
WHERE  semester = 'Fall' AND year = 2009
  AND  course_id IN (SELECT course_id FROM section
                     WHERE semester = 'Spring' AND year = 2010);

-- วิชาที่เปิด Fall 2009 แต่ไม่เปิด Spring 2010
SELECT DISTINCT course_id
FROM   section
WHERE  semester = 'Fall' AND year = 2009
  AND  course_id NOT IN (SELECT course_id FROM section
                         WHERE semester = 'Spring' AND year = 2010);

-- จำนวนนักศึกษา (ไม่ซ้ำ) ที่เคยลง section ที่สอนโดยอาจารย์ ID 10101
SELECT COUNT(DISTINCT ID)
FROM   takes
WHERE  (course_id, sec_id, semester, year) IN
       (SELECT course_id, sec_id, semester, year
        FROM   teaches WHERE teaches.ID = 10101);

-- สมมติ subquery คืน 2 แถวนี้ (วิชาที่อาจารย์ 10101 สอน)
   ('CS-101','1','Fall',2017)
   ('CS-347','1','Fall',2017)

-- แถว takes ที่ผ่าน:
   ('CS-101','1','Fall',2017)   ตรงครบ 4 ตำแหน่ง
-- แถว takes ที่ไม่ผ่าน:
   ('CS-101','2','Fall',2017)   sec_id ต่างกัน (คนละกลุ่มเรียน)
   ('CS-101','1','Fall',2018)   คนละปี

Set comparison — SOME / ANY และ ALL

SELECT first_name, salary FROM employees
WHERE salary > SOME (SELECT salary FROM employees
                    WHERE department = 'Marketing');

SELECT first_name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees
                   WHERE department = 'Marketing');

EXISTS และ Correlated Subquery

-- วิชาที่เปิดสอน Fall 2009 แต่ไม่เปิด Spring 2010 — เขียนด้วย EXISTS
SELECT course_id
FROM   section AS S1
WHERE  semester = 'Fall' AND year = 2009
  AND  NOT EXISTS (SELECT * FROM section AS S2
                   WHERE S2.semester = 'Spring' AND S2.year = 2010
                     AND S1.course_id = S2.course_id);

• Correlation name = ตัวแปร S1 ใน outer query
• Correlated subquery = inner query ที่อ้างถึงตัวแปรจาก outer query
• เพราะ inner query ต้องใช้ค่าจากแต่ละแถวของ outer จึงถูกรันใหม่ทุกแถว — ช้ากว่า subquery ธรรมดา

-- โจทย์ข้อ 11: พนักงานที่เป็นหัวหน้า (มีอย่างน้อยหนึ่งคนรายงานตรง)
SELECT * FROM employees e
WHERE  EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.employee_id);

-- โจทย์ B3: พนักงานที่เงินเดือนสูงกว่าค่าเฉลี่ยของแผนกตัวเอง
SELECT * FROM employees e
WHERE  salary > (SELECT AVG(salary) FROM employees
                 WHERE department = e.department);

ทำไมนิยมเขียน SELECT 1 ใน EXISTS — เพราะ EXISTS สนใจแค่ว่า “มีแถวไหม” ไม่สนใจว่าค่าคืออะไร การเขียน SELECT 1 จึงสื่อเจตนาชัดกว่า SELECT * (ประสิทธิภาพเท่ากัน optimizer ฉลาดพอ)

8.3Subqueries ใน FROM clause

-- เงินเดือนเฉลี่ยของแผนกที่มีค่าเฉลี่ยเกิน 70,000
SELECT dept_name, avg_salary
FROM   (SELECT dept_name, AVG(salary) AS avg_salary
        FROM   instructor
        GROUP BY dept_name) AS dept_avg
WHERE  avg_salary > 70000;

วิธีนี้เป็นทางเลือกแทน HAVING — ทั้ง HAVING AVG(salary) > 70000 และการซ้อน subquery ใน FROM ให้ผลเหมือนกัน แต่แบบ FROM อ่านง่ายกว่าเมื่อเงื่อนไขซับซ้อนขึ้น

8.4WITH clause (Common Table Expression)

WITH clause ให้เรานิยาม relation ชั่วคราว ซึ่งใช้ได้เฉพาะภายใน query ที่ประกาศไว้เท่านั้น พอ query จบ ตารางชั่วคราวนั้นก็หายไป — ไม่ได้ถูกเก็บลงดิสก์เหมือน CREATE TABLE

WITH  ชื่อตารางชั่วคราว ( ชื่อคอลัมน์1, ชื่อคอลัมน์2 )  AS
      ( SELECT … )                 ← ตัว query ที่สร้างตารางนั้น
SELECT … FROM ชื่อตารางชั่วคราว     ← query หลักที่เอาไปใช้

WITH max_salary(value) AS (SELECT MAX(salary) FROM employees)         -- ตั้งชื่อที่หัว
WITH max_salary       AS (SELECT MAX(salary) AS value FROM employees) -- ตั้งชื่อข้างใน

-- หาพนักงานที่ได้เงินเดือนสูงสุด
WITH max_salary(value) AS
     (SELECT MAX(salary) FROM employees)
SELECT employee_id, first_name, salary
FROM   employees, max_salary
WHERE  employees.salary = max_salary.value;

-- โจทย์ B5: แผนกที่ค่าใช้จ่ายเงินเดือนรวมเกิน 150,000
WITH dept_total(dept_name, total_salary) AS
     (SELECT department, SUM(salary) FROM employees GROUP BY department)
SELECT * FROM dept_total WHERE total_salary > 150000;

ข้อดีของ WITH: อ่านง่ายกว่าการซ้อน subquery หลายชั้น · ใช้ตารางชั่วคราวเดิมซ้ำได้หลายที่ใน query เดียว · แบ่งตรรกะซับซ้อนออกเป็นขั้นตอน

8.5Scalar Subquery ใน SELECT clause

Scalar subquery คือ subquery ที่ถูกใช้ในตำแหน่งที่คาดหวังค่าเดียว (1 แถว 1 คอลัมน์)

-- แสดงทุกแผนกพร้อมจำนวนอาจารย์ในแผนกนั้น
SELECT dept_name,
       (SELECT COUNT(*) FROM instructor
        WHERE department.dept_name = instructor.dept_name) AS num_instructors
FROM   department;

8.6Database Modification

มี 3 อย่าง: ลบ tuple · เพิ่ม tuple ใหม่ · แก้ไขค่าใน tuple

DELETE

DELETE FROM instructor;                       -- ลบอาจารย์ทั้งหมด
DELETE FROM instructor WHERE dept_name = 'Finance';

-- ลบอาจารย์ที่สังกัดแผนกซึ่งตั้งอยู่ในตึก Watson
DELETE FROM instructor
WHERE  dept_name IN (SELECT dept_name FROM department
                     WHERE building = 'Watson');

INSERT

INSERT INTO course VALUES ('CS-437','Database Systems','Comp. Sci.', 4);

-- เขียนระบุชื่อคอลัมน์ ได้ผลเหมือนกันแต่ปลอดภัยกว่า
INSERT INTO course (course_id, title, dept_name, credits)
VALUES ('CS-437','Database Systems','Comp. Sci.', 4);

-- ใส่ค่า null ให้ tot_creds
INSERT INTO student VALUES ('3003','Green','Finance', NULL);

-- INSERT … SELECT: เพิ่มอาจารย์ทุกคนเข้าตาราง student โดยให้ tot_creds = 0
INSERT INTO student
    SELECT ID, name, dept_name, 0 FROM instructor;

UPDATE และ CASE

โจทย์: ขึ้นเงินเดือนอาจารย์ที่ได้เกิน $100,000 อีก 3% และคนอื่นอีก 5%

-- แบบที่มีปัญหา: เขียนสองคำสั่งแยกกัน — ลำดับสำคัญมาก!
UPDATE instructor SET salary = salary * 1.03 WHERE salary > 100000;
UPDATE instructor SET salary = salary * 1.05 WHERE salary <= 100000;

-- แบบที่ดีกว่า: คำสั่งเดียว ไม่มีปัญหาเรื่องลำดับ
UPDATE instructor
SET salary = CASE
                WHEN salary > 100000 THEN salary * 1.03
                ELSE salary * 1.05
             END;

-- โจทย์ B7: จัดกลุ่มระดับเงินเดือนด้วย CASE ใน SELECT
SELECT first_name, last_name, salary,
       CASE
           WHEN salary >= 78000 THEN 'high'
           WHEN salary >= 68000 THEN 'medium'
           ELSE 'low'
       END AS salary_band
FROM   employees;

CASE ประเมินเงื่อนไขจากบนลงล่าง แล้วหยุดที่เงื่อนไขแรกที่เป็นจริง จึงต้องเรียงจากเงื่อนไขที่เจาะจงที่สุดไปหากว้างที่สุด

COALESCE

COALESCE คืนค่าแรกที่ไม่เป็น NULL จากรายการนิพจน์ที่ให้มา เป็นวิธีจัดการ NULL อย่างสง่างาม

COALESCE(expression1, expression2, expression3, …)

-- ประเมินจากซ้ายไปขวา คืนตัวแรกที่ไม่เป็น NULL
-- ถ้าเป็น NULL ทั้งหมด → คืน NULL

-- ตาราง contacts สมมติ (ไม่ใช่ employees ในบทนี้)
SELECT contact_name,
       COALESCE(phone_number, 'No phone provided') AS contact_phone
FROM   contacts;

-- ใช้กับ employees จริงในบทนี้: manager_id ของ John เป็น NULL
SELECT first_name, COALESCE(manager_id, 0) AS mgr_code FROM employees;

SELECT COALESCE(preferred_name, first_name, 'Unknown') AS display_name
FROM   users;

SELECT product_name, price * COALESCE(discount_rate, 0) AS discount_amount
FROM   products;

ตัวอย่างสุดท้ายสำคัญ — ถ้าไม่มี COALESCE สินค้าที่ discount_rate เป็น NULL จะได้ discount_amount = NULL แทนที่จะเป็น 0 เพราะเลขคณิตกับ NULL ให้ผลเป็น NULL เสมอ

8.7ตะลุยโจทย์ Section B และ C

-- B1. พนักงานในแผนกที่ตั้งอยู่ New York หรือ Chicago
SELECT * FROM employees
WHERE  department IN (SELECT department_name FROM departments
                      WHERE location IN ('New York','Chicago'));

-- B2. พนักงานที่ไม่ได้ถูกมอบหมายโปรเจกต์ใดเลย
SELECT * FROM employees
WHERE  employee_id NOT IN (SELECT employee_id FROM employee_projects);

SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employee_projects ep
                  WHERE ep.employee_id = e.employee_id);

-- B4. แผนกที่เงินเดือนเฉลี่ยเกิน 68,000
SELECT dept, avg_salary FROM
  (SELECT department AS dept, AVG(salary) AS avg_salary
   FROM employees GROUP BY department) AS d
WHERE avg_salary > 68000;

-- B5. แผนกที่ค่าใช้จ่ายเงินเดือนรวมเกิน 150,000
WITH dept_total(dept_name, total_salary) AS
     (SELECT department, SUM(salary) FROM employees GROUP BY department)
SELECT * FROM dept_total WHERE total_salary > 150000;

-- B6. แต่ละแผนกพร้อมจำนวนพนักงาน (scalar subquery)
SELECT d.department_name,
       (SELECT COUNT(*) FROM employees e
        WHERE e.department = d.department_name) AS employee_count
FROM   departments d;

-- C1. ลบโปรเจกต์ของแผนกที่อยู่ที่ Boston
DELETE FROM projects
WHERE  department_id IN (SELECT department_id FROM departments
                        WHERE location = 'Boston');

-- C2. สร้างและเติมตาราง high_earners จากเงินเดือนปัจจุบัน
CREATE TABLE high_earners (
    employee_id INT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    salary      DECIMAL(10,2)
);
INSERT INTO high_earners (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM   employees WHERE salary >= 70000;

-- C3. ขึ้นเงินเดือน: ต่ำกว่า 65,000 ขึ้น 10% ที่เหลือขึ้น 5% ในคำสั่งเดียว
UPDATE employees
SET    salary = CASE WHEN salary < 65000 THEN salary * 1.10
                     ELSE salary * 1.05 END;

8.8สรุปบทนี้

• Nested subquery ใช้ได้ใน WHERE, FROM และ SELECT — เปิดทางให้เขียน query ซับซ้อนและเปรียบเทียบข้อมูลได้
• ประเภท: set membership (IN) · set comparison (SOME, ALL) · existence (EXISTS)
• WITH clause นิยาม relation ชั่วคราว · Scalar subquery คืนค่าเดียว
• Database modification: DELETE ลบแถว · INSERT เพิ่มแถว · UPDATE แก้ข้อมูลเดิม
• CASE statement ทำให้ update แบบมีเงื่อนไขเป็นคำสั่งเดียว · COALESCE จัดการ NULL
• ต้องคำนึงถึงผลด้านประสิทธิภาพเสมอเมื่อ query ซับซ้อนขึ้น — โดยเฉพาะ correlated subquery ที่รันซ้ำทุกแถว