SQL Part 2 — Predicates & JOIN

SELECT * FROM emp_demo
WHERE  name LIKE 'J%'
  AND  hire_date < DATE_SUB(CURDATE(), INTERVAL 5 YEAR);

หรือใช้ DATEDIFF(CURDATE(), hire_date) > 365*5 ก็ได้ แต่แบบ INTERVAL อ่านง่ายและถูกต้องเรื่องปีอธิกสุรทินมากกว่า

SELECT * FROM prod_demo
WHERE  LOWER(name) LIKE '%phone%'
  AND  stock = 0;

ทำไมต้อง LOWER() — pattern ของ LIKE เป็น case-sensitive จึงต้องแปลงให้เป็นตัวพิมพ์เล็กทั้งคู่ก่อนเทียบ (บาง collation ของ MySQL ไม่สนตัวพิมพ์อยู่แล้ว แต่เขียนแบบนี้ปลอดภัยกว่า)

SELECT * FROM order_demo
WHERE  order_date >= DATE_SUB(CURDATE(), INTERVAL 400 DAY)
  AND  total_amount > 1000;

SELECT * FROM order_demo
WHERE  DAYOFWEEK(order_date) IN (1, 7);   -- MySQL: 1 = อาทิตย์, 7 = เสาร์

หรือ WHERE WEEKDAY(order_date) IN (5, 6) (MySQL: 0 = จันทร์ … 6 = อาทิตย์)

-- 1. เงินเดือนเกิน 70,000
SELECT * FROM employees WHERE salary > 70000;

-- 2. เข้าทำงานระหว่าง 1 ม.ค. 2019 ถึง 31 ธ.ค. 2020
SELECT * FROM employees
WHERE hire_date BETWEEN '2019-01-01' AND '2020-12-31';

-- 3. อยู่แผนก IT หรือ Finance
SELECT * FROM employees WHERE department IN ('IT','Finance');

-- 4. นามสกุลลงท้ายด้วย 'son'
SELECT * FROM employees WHERE last_name LIKE '%son';

-- 5. IT ที่เงินเดือน > 70000 หรือ HR ที่เงินเดือน < 65000
SELECT * FROM employees
WHERE (department = 'IT' AND salary > 70000)
   OR (department = 'HR' AND salary < 65000);

ข้อ 5 ต้องมีวงเล็บ — ถ้าเขียน department='IT' AND salary>70000 OR department='HR' AND salary<65000 โดยไม่ใส่วงเล็บ ก็ยังได้ผลถูกเพราะ AND มี precedence สูงกว่า OR แต่ใส่วงเล็บเสมอเพื่อความชัดเจนและกันพลาด

-- 6. พนักงานพร้อมสถานที่ตั้งของแผนก
SELECT e.employee_id, e.first_name, e.last_name,
       d.department_name, d.location
FROM   employees e
INNER JOIN departments d ON e.department = d.department_name;

-- 7. ทุกแผนกพร้อมพนักงานในแผนก (ถ้ามี)
SELECT d.department_name, e.first_name, e.last_name
FROM   departments d
LEFT JOIN employees e ON d.department_name = e.department;

-- 8. พนักงานทุกคน + สถานที่แผนก + โปรเจกต์ที่ทำ (ถ้ามี)
SELECT e.first_name, e.last_name, d.location, p.project_name
FROM   employees e
LEFT JOIN departments d        ON e.department    = d.department_name
LEFT JOIN employee_projects ep ON e.employee_id   = ep.employee_id
LEFT JOIN projects p           ON ep.project_id   = p.project_id;

ข้อ 7 ทำไมต้องเป็น LEFT JOIN จาก departments — โจทย์บอกว่า “ทุกแผนก … (ถ้ามี)” แปลว่าแผนกที่ไม่มีพนักงานก็ต้องแสดง ถ้าใช้ INNER JOIN แผนก Legal จะหายไป

ข้อ 8 ต้องเป็น LEFT JOIN ทั้งสามที่ — โจทย์บอก “พนักงานทุกคน” จึงห้ามมีใครหายไปเลย
• LEFT JOIN departments — เพราะ Nina อยู่แผนก Research ซึ่งไม่มีในตาราง departments ถ้าใช้ INNER JOIN เธอจะหายไปทันที ทั้งที่โจทย์ขอ “ทุกคน”
• LEFT JOIN employee_projects / projects — เพราะโจทย์บอก “ถ้ามี” คนที่ไม่มีโปรเจกต์ก็ต้องแสดง
ลำดับสำคัญ: เมื่อ LEFT JOIN ตารางแรกแล้ว ต้อง LEFT JOIN ตารางถัดไปด้วย ไม่งั้น INNER JOIN ตัวหลังจะไปตัดแถว NULL ทิ้งจนความพยายามข้างหน้าสูญเปล่า

-- 9. พนักงานที่เงินเดือนสูงกว่าหัวหน้าตัวเอง
SELECT e1.first_name, e1.last_name, e1.salary AS employee_salary,
       e2.first_name AS manager_first_name, e2.salary AS manager_salary
FROM   employees e1
JOIN   employees e2 ON e1.manager_id = e2.employee_id
WHERE  e1.salary > e2.salary;

-- 10. พนักงานที่ไม่อยู่ IT และไม่ได้เข้าทำงานก่อนปี 2019
SELECT * FROM employees
WHERE NOT (department = 'IT' OR hire_date < '2019-01-01');

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

-- 12. เงินเดือนสูงกว่าอย่างน้อยหนึ่งคนใน HR
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'HR');

-- 13. เงินเดือนสูงกว่าทุกคนใน HR
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'HR');

ข้อ 11 อ่านยังไง — EXISTS ทำงานทีละแถว
e คือชื่อเรียกแถวที่ query ข้างนอกกำลังพิจารณาอยู่ · สำหรับแต่ละแถว e ฐานข้อมูลจะรัน query ข้างในใหม่หนึ่งรอบ เพื่อถามว่า “มีพนักงานคนไหนที่ manager_id เท่ากับ e.employee_id ไหม”
ถ้าเจออย่างน้อยหนึ่งแถว EXISTS เป็นจริง → แถว e ถูกเก็บไว้ · ที่เขียน SELECT 1 เพราะสนแค่ว่า “มีไหม” ไม่สนว่าค่าคืออะไร
แบบนี้เรียกว่า correlated subquery (query ข้างในอ้างถึงตัวแปรจากข้างนอก) — เรียนเต็ม ๆ บทที่ 8

ข้อ 12 กับ 13 คือคู่ที่ออกสอบบ่อยที่สุด
HR มีเงินเดือน 65,000 (Jane) และ 62,000 (Sarah)
> ANY = มากกว่าค่าน้อยสุด = > 62,000 → ได้ทุกคนยกเว้น Sarah
> ALL = มากกว่าค่ามากสุด = > 65,000 → ได้ John, Mike, Emily, David, Chris, Lisa
จำง่าย ๆ: ANY → MIN · ALL → MAX

ข้อ 10 กระจาย NOT ด้วยกฎเดอมอร์แกน: NOT (A OR B) ≡ NOT A AND NOT B = department <> 'IT' AND hire_date >= '2019-01-01'
แต่ระวัง! ถ้ามีแถวที่ department เป็น NULL ทั้งสองรูปแบบจะตัดแถวนั้นทิ้ง เพราะได้ผลเป็น UNKNOWN (ดูบทที่ 7)