오늘은 서브쿼리와 그에 활용되는 여러 가지 문법들에 대해 배웠다.
서브쿼리(Subquery)
: SQL문 안의 SELECT문을 서브쿼리라고 한다. SELECT문의 서브쿼리는 괄호()로 묶어야 한다. 서브쿼리에는 중첩 서브쿼리, 상호연관 서브쿼리, 단일행 서브쿼리(=, >, >=, <, <=, <>, !=, ^=), 여러행 서브쿼리(IN, ANY, ALL), HAVING 서브쿼리 등이 있다. 순서대로 차근차근 살펴보도록 하자.
중첩 서브쿼리(Nested subquery)
- inner query(subquery)를 먼저 수행
- 그 값을 가지고 main query(outer query)를 수행
예를 들면, 110번 사원의 급여보다 더 많은 급여를 받는 사원을 구한다고 해보자. 쿼리문의 기본적인 형태는,
SELECT *
FROM employees
WHERE salary > 110번 사원의 급여;이럴 때는 110번 사원의 급여를 구하는 쿼리문을 하나 만들고 기본형태로 만들어 놓은 쿼리문에 넣어서 종속시키면 된다. 우선 110번 사원의 급여를 구하는 쿼리문을 만들어보자.
SELECT salary
FROM employees
WHERE employee_id = 110;서브쿼리는 괄호로 묶는다고 했으니까 다음과 같이 표현된다.
SELECT *
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE employee_id = 110);중첩 서브쿼리의 기본적인 형태는 다음과 같다.
main query(outer query)
--------------
SELECT *
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE employee_id = 110); -- 110은 상수값
-------------------------
inner query(subquery)상수값은 변하지 않는 고정된 값으로 숫자, 문자, 날짜 전부 가능하다. 쿼리문에서 상수값이 키핑 되어있으면 서브쿼리 선행 후 메인쿼리 수행하여 비교하도록 한다.
또 다른 예시로, 관리자들의 정보를 추출한다고 가정하자.
1) self join 이용할 때
SELECT w.*, m.*
FROM employees w, employees m
WHERE w.manager_id = m.employee_id2) join을 쓰지 않았을 때(서브쿼리)
SELECT *
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees);3) join, inline view 이용할 때
SELECT m.*
FROM employees w, employees m
WHERE w.manager_id = m.employee_id;
m쪽 집합 1쪽 집합 = m개위의 쿼리문을 출력하면 m쪽 집합의 중복성이 제거되지 않아서 m개로 나온다. m쪽 집합을 1쪽 집합처럼 만들기 위해서는 INLINE VIEW를 이용해서 distinct를 쓸 수 있다.
SELECT m.*
FROM (SELECT distinct manager_id -- INLINE VIEW
FROM employees) w, employees m
WHERE w.manager_id = m.employee_id; 그런데 inline view sql문을 이용하면 1쪽 집합처럼 만들기 위해서 내부적으로 sort가 발생하고, 대용량 데이터는 sorting을 하면 부하가 심해져서 성능 상에 문제가 발생한다.
상호연관 서브쿼리(Correlation subquery)
- main query(outer query)를 먼저 수행
- 첫번째 행을 후보행으로 잡고 후보행 값을 서브쿼리에 전달
- 후보행 값을 사용해서 서브쿼리를 수행
- 서브쿼리 결과값을 사용해서 후보행과 비교했을 때 참이면 별도의 메모리에 후보행 정보를 저장, 거짓이면 버린다.
- 다음 행을 후보행으로 잡고 2,3,4번을 반복적으로 수행한다.
위의 과정이 상호연관 서브쿼리이다. 상호연관 서브쿼리의 기본적인 형태를 살펴보자.
SELECT employee_id, salary, department_id
FROM employees e
WHERE salary > (SELECT avg(salary)
FROM employees
WHERE department_id = e.department_id);
----------------
미지수(변수), 후보행 값서브쿼리의 WHERE 절에 들어가는 값이 상수가 아니라 미지수(or 후보행 값)일 때, 미지수를 설명(FROM employees e)해주는 메인쿼리를 선행하고 서브쿼리가 끝날 때까지 서브쿼리를 반복 수행하여 비교한다.
서브쿼리가 끝날 때까지 반복 수행한다는 점은 상호연관 서브쿼리의 문제점이기도 하다. 상호연관 서브쿼리는 어찌보면 "반복문"이기 때문에 똑같은 쿼리를 던지고 그에 대한 결과를 얻고 또다시 똑같은 쿼리를 던지고 결과를 받고 하는 식의 과정을 계속해서 반복한다. 즉, 똑같은 후보행 값이 입력되더라도 무조건 서브쿼리는 수행해야 한다.
물론 상호연관 서브쿼리를 사용해도 문제는 없지만 보다 복잡한 쿼리문을 실행해야할 때 똑같은 절차를 계속 반복하는 것은 비효율적일 수 있다. 따라서 이 문제점을 해결할 수 있는 방법으로 INLINE VIEW라는 가상 테이블을 이용할 수 있다. 이에 대한 내용은 아래에 후술하겠다.
단일행 서브쿼리
: 서브쿼리의 결과가 단일값이 나오는 서브쿼리이다. 단일행 비교연산자(=, >, >=, <, <=, <>, !=, ^=)를 사용한다. 단일행 서브쿼리는 다음과 같이 표현된다.
SELECT *
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE last_name = 'King');last_name이 King인 salary 값은 단일하기 때문에 단일행 비교연산자인 >를 써서 값을 비교해줄 수 있다.
여러행 서브쿼리
: 서브쿼리의 결과가 여러 개의 값이 나오는 서브쿼리이다. 여러행 비교연산자(IN, ANY, ALL)를 사용한다.
IN
: 여러행에서 같다(=)라는 값으로 비교할 때는 IN 연산자를 사용한다. IN 연산자는 = OR의 의미를 지닌다.
SELECT *
FROM employees
WHERE salary IN (SELECT min(salary)
FROM employees
GROUP BY department_id);employees 테이블에서 부서 id별 최소 연봉을 받는 사원들의 정보를 출력하라는 뜻이다. 부서 id별 최소 연봉이 6900, 2500, 7000, ... 이라면 아래에 기술된 형태와 같은 의미라고 볼 수 있다.
SELECT *
FROM employees
WHERE salary IN (6900, 2500, 7000, ...);
SELECT *
FROM employees
WHERE salary = 6900
OR salary = 2500
...;특히 IN 연산자는 = OR 이기 때문에 위의 두 가지 쿼리문은 동일하다고 볼 수 있다.
ANY
: ANY는 OR 범주를 가진다.
1) > ANY
SELECT *
FROM employees
WHERE salary > ANY(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');employees 테이블에서 job id가 'IT_PROG'인 사원의 연봉보다 큰 연봉을 받는 사원의 정보를 출력하라는 뜻이다. 이 예제의 서브쿼리는 결과값이 여러 개로 나타나는 여러행 서브쿼리이다. 따라서 >라는 비교연산자를 쓸 수 없기 때문에, OR의 속성을 지닌 ANY를 서브쿼리에 붙인다. ANY는 OR의 속성을 지녔기 때문에 위의 예제를 OR로 풀어보면 아래와 같은 형태로 나타난다.
SELECT *
FROM employees
WHERE salary > 9000
OR salary > 6000
OR salary > 4800
OR salary > 4800
OR salary > 4200;연봉이 9000보다 크고, 6000보다 크고, 4800보다 크고, 4200보다 큰 사원의 정보를 출력하라는 것인데, 위 조건을 모두 합치면 결론적으로 4200보다 큰 데이터는 전부 출력하겠다는 의미이다.
즉, > ANY는 '최소값보다 크다'는 의미이다. 이것을 응용하여 서브쿼리에 ANY를 붙이지 않고 표현할 수 있는 방법이 있을까?
SELECT *
FROM employees
WHERE salary > (SELECT min(salary)
FROM employees
WHERE job_id = 'IT_PROG')ANY 대신 salary에 최소값을 구하는 min을 써서 쿼리문을 작성할 수도 있다. 반대로도 생각해볼 수 있다.
2) < ANY
SELECT *
FROM employees
WHERE salary < ANY(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
SELECT *
FROM employees
WHERE salary < 9000
OR salary < 6000
OR salary < 4800
OR salary < 4800
OR salary < 4200;연봉이 9000보다 작고, 6000보다 작고, 4800보다 작고, 4200보다 작은 사원의 정보를 출력하라는 것인데, 위 조건을 모두 합치면 결론적으로 9000보다 작은 데이터는 전부 출력하겠다는 의미이다.
즉, < ANY는 '최대값보다 작다'는 의미이다. 이것을 응용하여 서브쿼리에 ANY를 붙이지 않고 표현해보자.
SELECT *
FROM employees
WHERE salary < (SELECT max(salary)
FROM employees
WHERE job_id = 'IT_PROG');ANY 대신 salary에 최대값을 구하는 max를 써서 쿼리문을 작성할 수도 있다.
3) = ANY
SELECT *
FROM employees
WHERE salary = ANY(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
SELECT *
FROM employees
WHERE salary = 9000
OR salary = 6000
OR salary = 4800
OR salary = 4800
OR salary = 4200; 연봉이 9000이거나, 6000이거나, 4800이거나, 4200인 사원의 정보를 출력하라는 것인데, 위 조건을 모두 합치면 결론적으로 각 조건에 일치하는 데이터는 전부 출력하겠다는 의미이다.
즉, = ANY는 'IN'의 의미이다. 이것을 응용하여 서브쿼리에 ANY를 붙이지 않고 표현해보자.
SELECT *
FROM employees
WHERE salary IN (SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');ALL
: ALL은 AND 범주를 가진다.
1) > ALL
SELECT *
FROM employees
WHERE salary > ALL(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');employees 테이블에서 job id가 'IT_PROG'인 사원의 연봉보다 큰 연봉을 받는 사원의 정보를 출력하라는 뜻이다. 이 예제의 서브쿼리는 결과값이 여러 개로 나타나는 여러행 서브쿼리이다. 따라서 >라는 비교연산자를 쓸 수 없기 때문에, AND의 속성을 지닌 ALL을 서브쿼리에 붙인다. ALL은 AND의 속성을 지녔기 때문에 위의 예제를 AND로 풀어보면 아래와 같은 형태로 나타난다.
SELECT *
FROM employees
WHERE salary > 9000
AND salary > 6000
AND salary > 4800
AND salary > 4800
AND salary > 4200;연봉이 9000보다 크고, 6000보다 크고, 4800보다 크고, 4200보다 큰 사원의 정보를 출력하라는 것인데, AND 조건은 교집합과 같아서 위 조건을 모두 합치면 결론적으로 9000보다 큰 데이터만 출력하겠다는 의미이다.
즉, > ALL은 '최대값보다 크다'는 의미이다. 이것을 응용하여 서브쿼리에 ALL을 붙이지 않고 표현할 수 있는 방법이 있을까?
SELECT *
FROM employees
WHERE salary > (SELECT max(salary)
FROM employees
WHERE job_id = 'IT_PROG')ALL 대신 salary에 최대값을 구하는 max를 써서 쿼리문을 작성할 수 있다. 반대로도 생각해볼 수 있다.
2) < ALL
SELECT *
FROM employees
WHERE salary < ALL(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
SELECT *
FROM employees
WHERE salary < 9000
AND salary < 6000
AND salary < 4800
AND salary < 4800
AND salary < 4200;연봉이 9000보다 작고, 6000보다 작고, 4800보다 작고, 4200보다 작은 사원의 정보를 출력하라는 것인데, 위 조건을 모두 합치면 결론적으로 4200보다 작은 데이터만 출력하겠다는 의미이다.
즉, < ALL은 '최소값보다 작다'는 의미이다. 이것을 응용하여 서브쿼리에 ALL을 붙이지 않고 표현해보자.
SELECT *
FROM employees
WHERE salary < (SELECT min(salary)
FROM employees
WHERE job_id = 'IT_PROG');ALL 대신 salary에 최소값을 구하는 min을 써서 쿼리문을 작성할 수 있다.
3) = ALL
SELECT *
FROM employees
WHERE salary = ALL(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG');
SELECT *
FROM employees
WHERE salary = 9000
AND salary = 6000
AND salary = 4800
AND salary = 4800
AND salary = 4200; 연봉이 9000이고, 6000이고, 4800이고, 4200인 사원의 정보를 출력하라는 것인데, 위 조건을 모두 합치면 논리적으로 말이 안 된다. 모든 조건을 만족하는 데이터는 없기 때문.
HAVING의 서브쿼리
: HAVING은 그룹함수의 결과를 제한하는 절. HAVING 절의 비교연산자 오른쪽에 괄호()로 묶어서 서브쿼리를 사용한다. 통상적인 서브쿼리 만드는 방법과 거의 유사하다.
SELECT department_id, sum(salary)
FROM employees
GROUP BY department_id
HAVING sum(salary) > (SELECT min(salary)
FROM employees
WHERE department_id = 40);OR 진리표
TRUE OR TRUE = TRUE
TRUE OR FALSE = TRUE
TRUE OR NULL = TRUE
FALSE OR NULL = NULL
예를 들어보자.
SELECT *
FROM employees
WHERE employee_id IN (NULL, 100, 101, 102);
SELECT *
FROM employees
WHERE employee_id = NULL
OR employee_id = 100
OR employee_id = 101
OR employee_id = 102;OR 진리표에서 TRUE OR NULL = TRUE이기 때문에 위 쿼리문은 오류가 나지 않고 결과가 출력된다.
여러행 서브쿼리를 공부할 때 IN은 = OR라고 했다. 이를 응용하면 NOT IN(~하지 않은)도 쉽게 이해할 수 있다.
NOT IN은 NOT = OR, <> AND(같지않다 AND)로 해석된다. 이것을 AND 진리표와 함께 연결해서 이해해보도록 하자.
AND 진리표
TRUE AND TRUE = TRUE
TRUE AND FALSE = FALSE
TRUE AND NULL = NULL
FALSE AND NULL = FALSE
예를 들어보자.
SELECT *
FROM employees
WHERE employee_id NOT IN (NULL, 100, 101, 102);
SELECT *
FROM employees
WHERE employee_id <> NULL
AND employee_id <> 100
AND employee_id <> 101
AND employee_id <> 102;AND 진리표에서 TRUE AND NULL = NULL 이기 때문에 위 쿼리문의 결과가 출력되지 않고 빈 칸으로 나타나게 된다.
또 다른 예시를 들어보자.
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees);employees 테이블에서 관리자가 아닌 사원의 정보를 추출하라는 뜻인다. 그런데 위와 같이 쿼리문을 짜면 오류가 발생한다. 왜냐하면 서브쿼리에 NULL 값이 존재하기 때문이다. 즉, NOT IN 연산자를 이용할 때는 서브쿼리에 NULL 값이 있으면 조회가 안된다. 그래서 서브쿼리의 NULL 값을 제외한 후 수행해보면,
SELECT *
FROM employees
WHERE employee_id NOT IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL); 위와 같이 서브쿼리 안에서 NULL 값을 제외한 뒤 관리자가 아닌 사원의 정보를 추출하게 되면 알맞은 결과값이 나타난다.
INLINE VIEW
: 가상테이블. FROM절에 괄호 안의 SELECT문(서브쿼리)을 INLINE VIEW라고 한다.
위에서 상호연관 서브쿼리에서 똑같은 절차를 계속 반복하는 것은 비효율적이므로 INLINE VIEW를 이용한다고 했었다. INLINE VIEW라는 가상의 테이블을 만들어서 다른 테이블과 조인하는 식으로 사용한다. INLINE VIEW에서 그룹함수 같이 긴 표현식으로 나타나는 컬럼 이름들은 꼭! 별칭으로 묶어줘야 한다. 만약 별칭으로 묶지 않는다면, 표현식이 그냥 컬럼 이름인지 함수값인지 알 수가 없기 때문에 오류가 발생하게 된다.
SELECT e2.*, round(e1.avgsal) 부서평균
FROM (SELECT department_id as dept_id, avg(salary) as avgsal
FROM employees
GROUP BY department_id) e1, employees e2
WHERE e1.dept_id = e2.department_id
AND e2.salary > e1.avgsal;employees 테이블에서 부서 id 별로 군집한 뒤, 그 부서 id와 평균 연봉을 나타낸 테이블을 e1, 그냥 일반 employees 테이블을 e2라고 하자. e1의 부서 id와 e2의 부서 id가 같고 e2의 연봉이 e1의 평균연봉보다 싼 데이터 중에서 e2 테이블 전체와 e1의 평균연봉을 출력하라는 뜻이다.
