Intermediate SQL — Join Expressions & Views

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

9.1Joined Relations

Join operation รับ relation สองตัวเข้าไป แล้วคืน relation ออกมาอีกตัวหนึ่ง

join คือ Cartesian product ที่บังคับให้ tuple จากทั้งสอง relation ต้องตรงกัน ภายใต้เงื่อนไขบางอย่าง และยังกำหนดด้วยว่า attribute ตัวไหนจะปรากฏในผลลัพธ์ · join operation มักถูกใช้เป็น subquery expression ใน from clause

Join มี 3 กลุ่ม: Natural Join · Inner Join · Outer Join

instructor (ID, name, dept_name, salary)
department (dept_name, building, budget)
course     (course_id, title, dept_name, credits)
section    (course_id, sec_id, semester, year, building, room_number)   ← 2 คอลัมน์ท้ายเพิ่มมาในบทนี้
teaches    (ID, course_id, sec_id, semester, year)

9.2Natural Join

Natural join จับคู่ tuple ที่มีค่าเท่ากันใน attribute ร่วมทุกตัว และเก็บสำเนาของคอลัมน์ร่วมไว้เพียงชุดเดียว

“attribute ร่วม” = คอลัมน์ที่ชื่อเหมือนกันในทั้งสองตาราง — ระบบหาให้เองโดยเราไม่ต้องเขียน ON

-- แสดงชื่ออาจารย์พร้อมตึกของภาควิชา
-- คอลัมน์ร่วมคือ dept_name → ระบบจับคู่ให้เองโดยไม่ต้องเขียน ON
SELECT name, dept_name, building
FROM   instructor NATURAL JOIN department;

dept_name ปรากฏในผลลัพธ์แค่คอลัมน์เดียว ทั้งที่มาจากสองตาราง — นี่คือสิ่งที่ทำให้ natural join ต่างจาก inner join

ต่อกันหลายตารางได้ โดยเขียนเรียงกันไปเรื่อย ๆ:

SELECT A₁, A₂, … Aₙ
FROM   r₁ NATURAL JOIN r₂ NATURAL JOIN … NATURAL JOIN rₙ
WHERE  P;

-- ชื่ออาจารย์ + ตึกภาค + วิชาที่สอน (คอลัมน์ร่วมกับ teaches คือ ID)
SELECT name, building, course_id
FROM   instructor NATURAL JOIN department NATURAL JOIN teaches;

SELECT name, building, course_id
FROM   instructor NATURAL JOIN department JOIN teaches;

SELECT name, building, course_id
FROM   instructor NATURAL JOIN department
       JOIN teaches ON instructor.ID = teaches.ID;

ข้อมูล course / section / teaches ที่แบบฝึกหัด 9.6 ใช้

โจทย์ข้อ 2, 4, 5, 7 ถามถึงตึก Taylor และภาระงานสอน — ใช้ข้อมูลชุดนี้ตรวจคำตอบได้

ตอบข้อ 2 ได้ทันที: section ที่ building='Taylor' คือ CS-101 และ CS-347 ทั้งคู่สอนโดย Srinivasan → ผลลัพธ์ = (Intro to CS, Srinivasan), (Database Sys., Srinivasan)
ข้อ 7: อาจารย์ที่สอนที่ Taylor มีคนเดียวคือ Srinivasan (65,000) → AVG = 65,000 · ถ้าลืมใส่ DISTINCT เงินเดือนเขาจะถูกนับ 2 ครั้ง (เพราะสอน 2 วิชา) — ค่าเฉลี่ยยังได้ 65,000 ในกรณีนี้ แต่ถ้ามีอาจารย์คนที่สองที่สอนวิชาเดียว ค่าเฉลี่ยจะเพี้ยนทันที

9.3Inner Join

Inner join คืนเฉพาะแถวที่ตรงกันจากทั้งสองตารางตาม join condition

SELECT * FROM table1 INNER JOIN table2
       ON table1.column = table2.column;

• ใช้เมื่อ ต้องการดึงเฉพาะข้อมูลที่มีคู่ที่ตรงกันในทั้งสองตาราง
• ประเด็นสำคัญ แถวที่ไม่มีคู่ในตารางใดตารางหนึ่งจะถูกตัดออกจากผลลัพธ์

9.4Outer Join

Outer join เป็นส่วนขยายของ join ที่เลี่ยงการสูญเสียข้อมูล — คำนวณ join ตามปกติ แล้วเติม tuple จาก relation หนึ่งที่จับคู่กับ relation อีกตัวไม่ได้ กลับเข้าไปในผลลัพธ์ โดยใช้ค่า null เติมช่องว่าง

9.5Views — มุมมองเสมือน

logical model / conceptual model = ภาพของตารางจริงทั้งหมดที่เก็บอยู่ในฐานข้อมูล พร้อมคอลัมน์และความสัมพันธ์ — เป็นคำที่ใช้แทนกันได้ในบทนี้ (ทบทวนสามระดับ physical / logical / view ได้ที่ บทที่ 1)

บางกรณีเราไม่ต้องการให้ผู้ใช้ทุกคนเห็น logical model ทั้งหมด ลองนึกถึงคนที่จำเป็นต้องรู้ชื่อและภาควิชาของอาจารย์ แต่ไม่ควรเห็นเงินเดือน คนคนนี้ควรเห็นแค่

SELECT ID, name, dept_name FROM instructor;

View คือกลไกที่ซ่อนข้อมูลบางส่วนจากสายตาผู้ใช้บางกลุ่ม · relation ใดก็ตามที่ไม่ได้อยู่ใน conceptual model แต่ถูกทำให้ผู้ใช้มองเห็นในฐานะ “virtual relation” เรียกว่า view

การนิยาม view

CREATE VIEW v AS <query expression>;

-- view ของอาจารย์ที่ไม่มีเงินเดือน
CREATE VIEW faculty AS
    SELECT ID, name, dept_name FROM instructor;

-- ใช้ชื่อ view เหมือนตารางธรรมดา
SELECT name FROM faculty WHERE dept_name = 'Biology';

-- view ที่มีการรวมยอด และตั้งชื่อคอลัมน์เอง
CREATE VIEW departments_total_salary(dept_name, total_salary) AS
    SELECT dept_name, SUM(salary)
    FROM   instructor
    GROUP BY dept_name;

View ที่นิยามจาก view อื่น

view หนึ่งสามารถถูกใช้ในนิพจน์ที่นิยาม view อีกตัวได้ — เหมือนต่อเลโก้ทีละชั้น

• view v1 depends directly บน v2 ถ้า v2 ถูกใช้ในนิพจน์ที่นิยาม v1
• view v1 depends บน v2 ถ้า depends directly หรือมีเส้นทางของการพึ่งพาจาก v1 ไป v2
• view v เป็น recursive ถ้ามันขึ้นกับตัวมันเอง

CREATE VIEW physics_fall_2017 AS
    SELECT course.course_id, sec_id, building, room_number
    FROM   course, section
    WHERE  course.course_id = section.course_id
      AND  course.dept_name = 'Physics'
      AND  section.semester = 'Fall'
      AND  section.year     = '2017';

CREATE VIEW physics_fall_2017_watson AS
    SELECT course_id, room_number
    FROM   physics_fall_2017          -- ใช้ view ตัวแรก
    WHERE  building = 'Watson';

การอัปเดตผ่าน view

view เป็นแค่ “หน้าต่าง” ที่มองไปยังตารางจริง — คำถามคือ ถ้าเราเขียนข้อมูลผ่านหน้าต่างนั้น ระบบจะเอาไปเขียนลงตารางจริงยังไง

CREATE VIEW instructor_names AS
    SELECT ID, name FROM instructor;   -- เห็นแค่ 2 คอลัมน์

ทีนี้สมมติผู้ใช้ที่เห็นแค่ view นี้ เพิ่มอาจารย์คนใหม่:

INSERT INTO instructor_names VALUES ('106', 'Manee Jaidee');

ระบบต้องแปลงคำสั่งนี้เป็นการ insert ลงตารางจริง instructor ซึ่งมี 4 คอลัมน์ — แต่ผู้ใช้ให้มาแค่ 2 · แล้ว dept_name กับ salary จะเอาค่าอะไร มีสองทางเลือก:

• ปฏิเสธการ insert ไปเลย — ปลอดภัยที่สุด และเป็นสิ่งที่เกิดขึ้นจริงที่นี่ เพราะ dept_name เป็น NOT NULL
• insert โดยเติมค่าที่เหลือเป็น NULL → ('106','Manee Jaidee', NULL, NULL) — ทำได้ก็ต่อเมื่อคอลัมน์ที่ขาดหายยอมรับ NULL ได้

นี่คือที่มาของเงื่อนไขข้อ 3 ในกล่องด้านล่าง — “attribute ที่ไม่ได้อยู่ใน SELECT clause ต้องตั้งเป็น null ได้” ถ้า dept_name ยอมรับ NULL ได้ การ insert ผ่าน view นี้ก็จะสำเร็จ

CREATE VIEW instructor_info AS
    SELECT ID, name, building
    FROM   instructor, department
    WHERE  instructor.dept_name = department.dept_name;

INSERT INTO instructor_info VALUES ('69987','White','Taylor');

Materialized View

Materializing a view คือการสร้างตารางกายภาพจริงที่บรรจุ tuple ทั้งหมดของผลลัพธ์ของ query ที่นิยาม view นั้น

• สำเนาเชิงกายภาพถูกสร้างตอนที่นิยาม view
• ถ้า relation ที่ใช้ใน query ถูกอัปเดต ผลลัพธ์ใน materialized view จะล้าสมัย (out of date)
• จึงต้อง maintain view ด้วยการอัปเดตทุกครั้งที่ relation ต้นทางเปลี่ยน

หมายเหตุ: MySQL ไม่มี MATERIALIZED VIEW ในตัว ต้องจำลองด้วยการ CREATE TABLE … AS SELECT … แล้วรีเฟรชเอง (ต่างจาก PostgreSQL และ Oracle ที่รองรับโดยตรง)

การลบ view

DROP VIEW faculty;
DROP VIEW IF EXISTS faculty;
DROP VIEW IF EXISTS department_salary_totals;

9.6โจทย์แบบฝึกหัด Intermediate SQL

-- 1. ชื่ออาจารย์พร้อมชื่อภาควิชา ใช้ natural join
SELECT name, dept_name
FROM   instructor NATURAL JOIN department;

-- 2. รายวิชาที่สอนในตึก Taylor พร้อมชื่ออาจารย์
SELECT DISTINCT c.title, i.name
FROM   course c
JOIN   section  s ON c.course_id = s.course_id
JOIN   teaches  t ON s.course_id = t.course_id
                 AND s.sec_id    = t.sec_id
                 AND s.semester  = t.semester
                 AND s.year      = t.year
JOIN   instructor i ON t.ID = i.ID
WHERE  s.building = 'Taylor';

CREATE VIEW departments_total_salary(dept_name, total_salary) AS
    SELECT dept_name, SUM(salary)
    FROM   instructor
    GROUP BY dept_name;

SELECT dept_name, total_salary
FROM   departments_total_salary
ORDER BY total_salary DESC
LIMIT 1;

SELECT * FROM departments_total_salary
WHERE  total_salary = (SELECT MAX(total_salary) FROM departments_total_salary);

-- 4. view ของวิชาที่เปิด Fall 2023 แล้วใช้หาวิชาในตึก Watson
CREATE VIEW courses_fall_2023 AS
    SELECT c.course_id, c.title, s.sec_id, s.building, s.room_number
    FROM   course c
    JOIN   section s ON c.course_id = s.course_id
    WHERE  s.semester = 'Fall' AND s.year = 2023;

SELECT course_id, title, room_number
FROM   courses_fall_2023
WHERE  building = 'Watson';

-- 5. view ภาระงานสอนของอาจารย์ แล้วเรียงตามเงินเดือนมากไปน้อย
CREATE VIEW instructor_load AS
    SELECT i.ID, i.name, i.dept_name, i.salary,
           COUNT(t.course_id) AS course_count
    FROM   instructor i
    LEFT JOIN teaches t ON i.ID = t.ID
    GROUP BY i.ID, i.name, i.dept_name, i.salary;

SELECT * FROM instructor_load ORDER BY salary DESC;

-- MySQL ไม่มี MATERIALIZED VIEW จึงจำลองด้วยตารางจริง
CREATE TABLE mv_avg_credits AS
    SELECT dept_name, AVG(credits) AS avg_credits
    FROM   course
    GROUP BY dept_name;

-- หาภาคที่หน่วยกิตเฉลี่ยสูงกว่าค่าเฉลี่ยรวม
SELECT * FROM mv_avg_credits
WHERE  avg_credits > (SELECT AVG(avg_credits) FROM mv_avg_credits);

-- ต้อง refresh เองเมื่อ course เปลี่ยน
DROP TABLE mv_avg_credits;
CREATE TABLE mv_avg_credits AS SELECT …;

-- 7. เงินเดือนเฉลี่ยของอาจารย์ที่สอนในตึก Taylor
SELECT AVG(salary) AS avg_taylor_salary
FROM   (SELECT DISTINCT i.ID, i.salary
        FROM   instructor i
        JOIN   teaches t ON i.ID = t.ID
        JOIN   section s ON t.course_id = s.course_id
                        AND t.sec_id   = s.sec_id
                        AND t.semester = s.semester
                        AND t.year     = s.year
        WHERE  s.building = 'Taylor') AS taylor_instructors;

-- 8. WITH: อาจารย์ที่เงินเดือนสูงกว่าค่าเฉลี่ย แล้ว join กับ department
WITH above_avg AS
     (SELECT ID, name, dept_name, salary
      FROM   instructor
      WHERE  salary > (SELECT AVG(salary) FROM instructor))
SELECT a.name, a.salary, d.dept_name, d.building
FROM   above_avg a
JOIN   department d ON a.dept_name = d.dept_name
ORDER BY a.salary DESC;

9.7สรุปบทนี้

• Join expression เป็นเครื่องมือทรงพลังในการรวมข้อมูลจากหลายตาราง
• join แต่ละชนิด (Natural, Inner, Outer) ตอบโจทย์การดึงข้อมูลต่างกัน
• View สร้างตารางเสมือน ช่วยเรื่องความปลอดภัยของข้อมูลและทำให้ query ซับซ้อนดูง่ายขึ้น
• เข้าใจการอัปเดตผ่าน view และ materialized view เป็นเรื่องสำคัญในการบริหารฐานข้อมูล