마리아DB (3) _ 서브쿼리 작성하기

이번 게시글에서는 데이터모델링을 정리하기 전에 간단하게 서브쿼리에 대해 정리해보려고 한다.

 

서브쿼리란 ?

서브쿼리는 하나의 sql문 안에 포함된 또 다른 sql문을 의미한다. 서브쿼리문은 생각보다 자주 사용하게 되고, 여러개의 쿼리문을 작성할 수도 있다.

 

아래 코드는 간단한 예시이다.

 

홍길동 사원과 같은 부서인 부서원을 조회하는 코드이다.

SELECT name,
	   code
FROM employee
WHERE code = (
	SELECT code FROM employee WHERE name = '홍길동'
);

 

홍길동사원과 같은 부서인 사람을 구하려면, 우선 홍길동사원의 부서가 어디인지를 파악해야 한다. 그 후, 찾아낸 부서를 바탕으로 같은 부서인 사람을 구해주면 되는 것이다. 그래서 쿼리문을 다음과 같이 두 개 작성한다.

• 홍길동씨의 부서코드를 찾는 쿼리
• 홍길동씨와 부서코드가 같은 사람을 찾는 쿼리

여기서는 홍길동씨의 부서코드를 찾는 쿼리문이 서브쿼리가 된다.

 

서브쿼리의 종류

서브쿼리 반환값의 행과 열 개수에 따라 단일행 서브쿼리, 다중행 서브쿼리, 다중열 서브쿼리로 나눌 수 있다.

 

단일행 서브쿼리

단일행 서브쿼리는 서브쿼리의 조회 결과 값의 개수가 1개인 서브쿼리를 의미한다. 위 예시 코드에서 서브쿼리는 홍길동의 부서코드 단 한 개만 반환하기 때문에 단일행 서브쿼리라고 볼 수 있다.

 

 

다중행 서브쿼리

다중행 서브쿼리는  서브쿼리의 조회 결과 값의 개수가 여러 개인 서브쿼리를 의미한다. 즉, 열은 1개이지만 여러개의 행이 나오는 구조가 된다. 

 

예제는 다음 코드가 있다.

SELECT emp_name,
		 job_code,
		 dept_code,
		 salary
FROM employee
WHERE salary IN (
	SELECT max(salary) FROM employee
	GROUP BY dept_code
)
ORDER BY dept_code
;

 

위 코드는 각 부서별 최고 급여를 받는 직원의 이름, 직급 코드, 부서 코드, 급여를 조회 한 것이다. 이를 조회하려면, 각 부서의 최고급여를 조회한 후, 그 안에 있는 급여들과 같은 사람들 뽑아내면 된다. 

 

그래서 단일행 서브쿼리와 달리 WHERE절에서 =를 쓰지 않고, IN을 사용한다.

 

그런데, 이 문제에서는 각 부서의 최고 급여를 받는 사람과 같은 급여를 받는 사람이라면 모두 반환될 것이다. 이를 방지하기 위해서는 부서코드도 받을 수 있으면 좋은데, 다중행 다중열 서브쿼리를 사용하면 된다. 이는 아래에서 설명할 예정이다.

 

SELECT emp_id,
		 emp_name,
		 dept_code,
		 case when emp_id IN (
		 	SELECT DISTINCT manager_id
			FROM employee
			WHERE manager_id IS NOT NULL) then '사수'
		 		ELSE '사원'
		 	END AS '구분'
FROM employee;

 

위 코드는 사수에 해당되는 사번을 조회하는 코드로, '구분'이라는 새로운 열을 만들어, 조건을 만족하면 '사수', 만족하지 않으면 '사원'을 넣어주는 코드이다. 

 

 

행이 여러개다 보니 부등호를 사용할 수 없는데, 이때 ANY 명령어를 사용할 수 있다. 

ANY : 서브쿼리의 결과 중 하나라도 조건을 만족하면 TRUE를 반환한다. 즉, IN 안에 있는 수를 대상으로 한 개라도 만족하면 되는 명령어이다.

 

다른 명령어로는 ALL이 있고, 이는 예상가능하게도 IN안에 있는 모든 수를 대상으로 조건을 만족시켜야 TRUE를 반환한다.

 

 

다중열 서브쿼리

다중 열 서브쿼리는 서브쿼리 결과가 한 행이지만 열의 수가 여러개인 서브쿼리이다. 열이 여러개이기 때문에, 열을 직접 구분해주어야 한다. 아래 코드를 보면 이해가 될 것이다.

SELECT emp_name,
		 dept_code,
		 job_code
FROM employee
WHERE (dept_code, job_code) = (
	SELECT dept_code, 
		 	 job_code
	FROM employee
	WHERE emp_name = '홍길동'
);

 

위 코드는 하이유 사원과 같은 부서 코드, 같은 직급 코드에 해당하는 사원을 조회하는 것이고, 1명이라고 가정한다. 행이 1개이기 때문에, 단일행 서브쿼리처럼 =을 써줄 수 있다.

 

대신 열이 여러개이기 때문에 WHERE (dept_code, job_code) 이런 식으로 열의 위치를 지정해주어야 한다.

 

 

지금껏 한 것을 보면, 행이 여러 개일 때에는 = 대신 IN을 사용하고, 열이 여러개일 때는 열의 위치를 지정했다. 

다중행 다중열 서브쿼리는 이 두개를 동시에 사용하면 된다.

 

 

다중행 다중열 서브쿼리

다중행 다중열 서브쿼리는 행과 열이 여러개인 쿼리문이다. 

SELECT emp_no,
		 emp_name,
		 ifnull(dept_code, '부서없음'),
		 salary
FROM employee
WHERE (IFNULL(dept_code, '부서없음'), salary) IN (
	SELECT ifnull(dept_code, '부서없음'), MAX(salary) 
	FROM employee
	GROUP BY dept_code
)
ORDER BY 3
;

 

이렇게 IN과 열 위치 지정을 동시에 사용한다.

 

IFNULL 함수를 이용하여 해당 열이 NULL일 때 넣을 값을 설정해 줄 수 있다 !

 

 

이런 서브쿼리들은 WHERE절 뿐만 아니라 FROM에도 사용할 수 있다고 한다.