คำค้นย่อยที่สัมพันธ์กัน

คิวรีย่อยที่สัมพันธ์กันคืออะไร

คิวรีย่อยทั่วไปเปรียบเทียบกับคิวรีย่อยที่สัมพันธ์กัน

-- Regular subquery (runs once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery (runs once per outer row)
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

การเตรียมตารางตัวอย่าง

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50),
  department_id INT REFERENCES departments(id),
  salary        NUMERIC(10,2),
  hire_date     DATE
);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR');

INSERT INTO employees VALUES
  (1,'Alice',  1, 90000, '2020-03-01'),
  (2,'Bob',    1, 75000, '2021-06-15'),
  (3,'Carol',  2, 60000, '2019-01-10'),
  (4,'David',  2, 68000, '2022-09-01'),
  (5,'Eve',    3, 55000, '2020-07-20'),
  (6,'Frank',  1, 95000, '2018-11-05'),
  (7,'Grace',  3, 52000, '2023-02-28'),
  (8,'Henry',  2, 71000, '2021-04-12');

พนักงานที่มีรายได้สูงกว่าค่าเฉลี่ยของแผนก

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id, e1.salary DESC;

การใช้คิวรีย่อยที่สัมพันธ์กันใน SELECT

SELECT
  e.name,
  e.salary,
  (
    SELECT ROUND(AVG(e2.salary), 2)
    FROM employees e2
    WHERE e2.department_id = e.department_id
  ) AS dept_avg_salary
FROM employees e
ORDER BY e.department_id, e.name;

การค้นหาพนักงานเงินเดือนสูงสุดของแต่ละแผนก

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id;

EXISTS กับคิวรีย่อยที่สัมพันธ์กัน

SELECT d.name AS department
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
    AND e.hire_date < '2021-01-01'
);

NOT EXISTS กับคิวรีย่อยที่สัมพันธ์กัน

SELECT d.name AS department_without_employees
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
);

คิวรีย่อยที่สัมพันธ์กันใน UPDATE

ALTER TABLE employees ADD COLUMN dept_avg NUMERIC(10,2);

UPDATE employees e1
SET dept_avg = (
  SELECT ROUND(AVG(e2.salary), 2)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, dept_avg FROM employees ORDER BY department_id, name;

คิวรีย่อยที่สัมพันธ์กันใน DELETE

DELETE FROM employees e1
WHERE e1.salary < (
  SELECT AVG(e2.salary) * 0.60
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, department_id FROM employees ORDER BY department_id;

เคล็ดลับด้านประสิทธิภาพ: คิวรีย่อยที่สัมพันธ์กันเปรียบเทียบกับ JOIN

-- Correlated version (potentially slower)
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- Equivalent JOIN + derived table (often faster)
SELECT e.name, e.salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) dept_avg ON dept_avg.department_id = e.department_id
WHERE e.salary > dept_avg.avg_sal;

ตรวจสอบความเข้าใจอย่างรวดเร็ว

สรุป: คิวรีย่อยที่สัมพันธ์กัน