MySQL) WITH절을 이용한 비재귀적 CTE

WITH절 (비재귀적 CTE)

Non Recursive CTE(Common Table Expression)

---

  설명 

  아래와 같이 테이블들이 생성되고 데이터들이 삽입되었다고 하자.

 

<department TABLE>

CREATE TABLE department (
	dept_num INT NOT NULL PRIMARY KEY,
	name VARCHAR(20) NOT NULL
);

INSERT INTO department VALUES
(1, 'A'),
(2, 'B'),
(3, 'C');

  

<employee TABLE>

CREATE TABLE employee (
      emp_id INT NOT NULL PRIMARY KEY,
      dept_num INT NOT NULL,
      name VARCHAR(20),
      salary INT NULL,
      FOREIGN KEY (dept_num) REFERENCES department(dept_num)
        ON UPDATE CASCADE
);

INSERT INTO employee VALUES
(11111, 2, 'David', 524),
(22222, 1, 'Jiyeon', 412),
(33333, 3, 'Hong', 511),
(44444, 1, 'Melissa', 392),
(55555, 2, 'John', 402),
(66666, 3, 'Wallace', 500),
(77777, 1, 'Jasmin', 380);

 

  위 두 테이블을 가지고 아래와 같은 문제를 풀어야 한다.

 

  'A' 부서에서 근무하는 사원들 중 가장 큰 연봉을 받는 사원의 id를 출력하라.

 

  위와 같은 문제는 이와 같이 서브 쿼리를 활용하여 풀 수 있다.

 

SELECT e2.emp_id FROM (
			SELECT e.emp_id, e.name, e.salary
			FROM employee AS e
			LEFT JOIN department AS d
				ON e.dept_num = d.dept_num
			WHERE d.name = 'A'
			) AS e2
ORDER BY e2.salary DESC
LIMIT 0, 1;

 

  서브 쿼리에서 'A' 부서에서 근무하는 사원들의 id, name, salary 를 출력하며 그 테이블을 salary COLUMN 으로 내림차순 정렬을 한 뒤 가장 첫 번째 사원의 id 만 출력을 한다. 그리고, 이 쿼리문은 WITH절을 이용한 CTE 를 통해 더 보기 쉽게 정리할 수 있다.

 

[WITH절을 이용한 비재귀적 CTE를 활용]

 

WITH CTE_dept_1 (emp_id, name, salary)
AS (
	SELECT e.emp_id, e.name, e.salary
	FROM employee AS e
	LEFT JOIN department AS d
		ON e.dept_num = d.dept_num
	WHERE d.name = 'A'
)
SELECT emp_id FROM CTE_dept_1 
ORDER BY salary DESC
LIMIT 0, 1;

 

  위 쿼리는 WITH 절을 통해 'A' 부서에 속한 사원들의 id, name, salary 로 CTE_dept_1 라는 테이블 임시로 만든 것 이다.  WITH 절을 이용하여 비재귀적 CTE를 만드는 형식은 아래와 같다.

 

WITH <Table name> (Columns)
AS (
	<쿼리문>
)
SELECT ~ FROM <Table name>
...

  원하는 서브 쿼리를 만들기 위한 쿼리문은 위 AS 안에 작성을 하게 되는 것 이다. 즉, 위 그림과 같이 아래 쿼리에서 사용한 작은 네모상자 안 <Table name> 은 위 큰 네모상자 안의 테이블과 같은 테이블 이라고 생각하면 된다.