서브쿼리 (SubQuery)
목차
📍 서브쿼리 (Subquery)
📍 서브쿼리 (SubQuery)
– 쿼리문을 보조하는 역할의 쿼리문.
– 바로 조회할 수 없는 값을 별도의 쿼리구문을 통해서 메인쿼리에 전달해주는 역할을 수행함.
– 서브쿼리는 메인쿼리의 실행 순서보다 우선시 되며 괄호로 묶어서 작성.
– Group By 절을 제외한 쿼리 구문에 사용 가능.
– 연산에 사용된 서브쿼리의 위치에 따라서 적절하게 서브쿼리가 돌려줄 데이터타입, 컬럼 수, 행 수가 맞아야 함.
1 ) 데이터타입이 같은 지
2 ) 서브쿼리가 돌려줄 컬럼의 수가 같은지
3 ) 중복값 유무 (=행 수)
– 서브쿼리는 괄호로 묶어서 작성함.
– where절 또는 having절에 사용된 경우 가독성을 위해 연산자의 오른쪽에 배치함.
▸ 단일행 서브쿼리 (Single-row SubQuery)
– 서브쿼리로부터 메인쿼리로 한 행만 반환되는 유형.
– 단일행 서브쿼리인 경우, 메인쿼리에 단일행 비교연산자를 사용해야 함.
| 연산자 | 의미 |
| = | 같음 |
| > | 보다 큼 |
| >= | 보다 크거나 같음 |
| < | 보다 작음 |
| <= | 보다 작거나 같음 |
| <> | 같지 않음 |
▸ 다중행 서브쿼리 (Multiple-row subquery)
– 서브쿼리로부터 메인쿼리로 두 개 이상의 행이 반환되는 유형.
– 다중행 서브쿼리인 경우, 메인쿼리에 다중행 비교연산자를 사용해야 함.
| 연산자 | 의미 |
| IN | 리스트의 임의 멤버와 같음. |
| ANY | =, !=, >, <, <=, >= 연산자가 앞에 있어야 함. 관계가 True인 SubQuery의 결과 집합에 요소가 1개 이상 있는 경우, True 반환. |
| ALL | =, !=, >, <, <=, >= 연산자가 앞에 있어야 함. SubQuery 결과 집합의 모든 요소에 대한 관계가 True일 경우, True 반환. |
| EX1 ) |
• Abel 사원보다 더 많은 급여를 받는 사원 출력
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE last_name = 'Abel');– Abel의 급여가 바뀔수도 있으므로 직접적으로 리터럴값을 넣으면 안됨.
– Abel의 급여를 구하는 쿼리를 조건식에 넣어주기. 즉, 서브쿼리로 사용하기.
– WHERE절이나 HAVING절을 사용할 경우 가독성을 위해 연산자의 오른쪽에 배치함.
✓ 실행 결과
| EX2 ) |
• Davies 사원보다 늦게 입사한 사원들 출력
SELECT employee_id, last_name, hire_date
FROM employees
WHERE hire_date > (SELECT hire_date
FROM employees
WHERE last_name = 'Davies');– Davies의 입사일을 먼저 구해야 그 보다 늦게 입사한 사원들을 알 수 있음.
→ Davies의 입사일 구하는 쿼리를 서브쿼리로 작성함.
→ 하나의 행만 돌려주므로 단일행 서브쿼리.
– Davies의 입사일 '보다 늦게' 입사한 사원들을 구하는 쿼리를 hire_date로 조건식을 작성하여 메인쿼리로 작성함.
✓ 실행 결과
| EX3 ) |
• 141번 사원이 부서장으로 근무하는 부서의 사원을 출력
SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 141);– '141번 사원이 부서장으로 근무하는 부서'가 서브쿼리.
– 서브쿼리에 해당되는 부서의 사원을 모두 출력하기 위해 WHERE절에 job_id가 같다는 조건식을 걸어줌.
✓ 실행 결과
| EX4 ) |
• 전 직원 중, 가장 적은 급여값과 동일한 급여를 받는 직원 출력
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees);– 가장 적은 급여값이 서브쿼리에 해당.
→ 그룹함수 MIN()을 사용하여 최소 급여값을 구해줌.
– 동일한 급여를 받는 직원이니 WHERE절에는 salary가 같음을 조건으로 작함.
✓ 실행 결과
| EX5 ) |
• Lee와 동일한 직무로 일하면서 더 많은 급여를 받는 사원 출력
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id
FROM employees
WHERE last_name = 'Lee')
AND salary > (SELECT salary
FROM employees
WHERE last_name = 'Lee')
ORDER BY salary;– WHERE절에 AND연산자를 사용하여 각 각 서브쿼리를 넣어 조건을 설정함.
→ Lee와 동일한 직무 (job_id)인게 조건1, 더 많은 급여(salary)가 조건2에 해당.
→ 조건 2개를 모두 만족해야 함.
– 데이터타입, 행 수 맞춰서 넣음.
✓ 실행 결과
| EX6 ) |
• 부서들 중, 30번 부서의 최소 급여 보다 큰 급여를 받는 부서를 출력
SELECT department_id, min(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING min(salary) >
(SELECT min(salary)
FROM employees
WHERE department_id = 30)
ORDER BY min(salary);– 30번 부서 직원들 중 최소 급여를 서브쿼리에 작성하고, 그룹함수끼리 비교하기 위해 HAVING절 사용함.
→ 30번 부서의 최소 급여보다 큰 급여를 알 수 있음.
– 부서를 출력해야 하는데 아직 부서 배정을 받지 못한 사람도 있을 수 있으니
WHERE절에 department_id IS NOT NULL 조건을 걸어줌.
– 각 부서에 대한 결과이므로 GROUP BY절에 department_id 컬럼을 사용하여 부서별로 그룹화 함.
→ department_id 컬럼에서 중복값들을 하나의 그룹으로 묶는 것.
– 서브쿼리의 문법은 리터럴 값이 들어갈 수 있는 위치면 대부분 사용 가능함.
✓ 실행 결과
🚨 에러 1
SELECT employee_id, last_name
FROM employees
WHERE salary = (SELECT min(salary)
FROM employees
GROUP BY department_id);– 서브 쿼리가 돌려주는 값이 행 하나가 넘어가므로 (= 여러 행) 비교 연산 불가.
– 서브 쿼리가 모든 그룹의 최소 급여를 다 넘겨주기 때문에 행의 개수가 달라 비교할 수 없음.
SELECT employee_id, last_name
FROM employees
WHERE salary IN (SELECT min(salary)
FROM employees
GROUP BY department_id);– 비교 연산자 ( = )가 아닌 IN 연산자를 사용하면, 각 부서의 최소 급여와 동일하게 받는 직원들의 정보가 출력됨.
✓ 실행 결과
🚨 에러 2
SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id
FROM employees
WHERE last_name = 'Haas');– 에러라기보다는 데이터가 하나도 출력 안됨.
– 서브쿼리만 따로 실행해봐도 데이터가 출력 안됨.
서브쿼리가 실행안되면 메인쿼리도 아무런 값을 돌려주지 않으므로 서브쿼리 조건 자체를 잘못 설정한 것.
| TEST ) |
Q1 ) employee테이블에서 Abel과 동일한 부서에 소속된 사원들의
last_name과 hire_date를 출력하되, 비교의 대상인 Abel은 제외하시오.
SELECT last_name, hire_date, department_id
FROM employees
WHERE last_name != 'Abel'
AND department_id = (SELECT department_id
FROM employees
WHERE last_name = 'Abel');– 문제에서 내건 조건은 Abel과 동일한 부서일 것, Abel은 제외할 것.
– Abel의 부서를 알아내기 위해 서브쿼리를 사용하여 조건을 Abel의 이름으로 설정하고 부서id를 출력함.
– Abel의 부서id와 같은 부서id (조건 1)를 지닌 사원들이며
이름이 Abel이 아닌 사람들만 출력 (조건 2)되도록 메인 WHERE절에 AND 연산자로 작성함.
✓ 실행 결과
Q2 ) employees 테이블에서 평균 이상의 급여를 받는 사원들의
employee_id, last_name, salary를 출력하되 급여를 기준으로 오름차순 하시오.
SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= (SELECT avg(salary)
FROM employees)
ORDER BY salary;– 평균 이상의 급여를 받는 사원들을 출력해야하므로 서브쿼리로 '평균 급여'를 구해서 메인쿼리의 조건으로 사용함.
– ORDER BY절에 컬럼명을 쓰고 뒤에 키워드를 생략하면 자동으로 오름차순 (ASC)이 적용됨.
✓ 실행 결과
Q3 ) employees 테이블에서 last_name에 'u'가 포함된 사원과 같은 부서에 근무하는
모든 사원의 employee_id, last_name을 출력하시오.
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%');– 한 부서안에 u가 포함된 사람이 여러명일 경우 부서id가 중복됨.
→ 중복값 제거를 위해 SELECT문에 DISTINCT 키워드 사용.
– last_name에 'u'가 포함된 사원의 부서id를 서브쿼리에 작성,
해당 사원과 같은 부서에 일하는 직원을 메인쿼리에 작성함.
– 서브쿼리의 조건이 메인쿼리의 조건에 '포함' 되야 하므로 IN 연산자를 사용하여 이어줌.
– 행의 수가 맞지 않다면 IN 연산자 한번 떠올려 보기.
✓ 실행 결과
Q4 ) employees 테이블과 departments 테이블을 사용하여 구문을 작성하시오.
location_id가 1700인 부서에 소속된 사원들의
employee_id, last_name, department_id, job_id를 출력하시오.
SELECT employee_id, last_name, department_id, job_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = 1700);– location_id가 1700인 부서를 서브쿼리에 작성함.
– 메인쿼리의 조건절과 서브쿼리의 조건절 행의 수가 맞지 않음.
→ 서브쿼리의 결과값을 메인쿼리에 '포함' 시켜야 함.
→ IN 연산자 사용
– 서브쿼리에 departments 테이블을 사용했지만,
출력하는 SELECT절에 departments 테이블의 컬럼이 없으므로 굳이 JOIN 사용 안함.
✓ 실행 결과
Q5 ) employees 테이블에서 평균 이상의 급여를 받으면서,
last_name에 'u'가 포함된 사원과 동일한 부서에 소속된 사원들의
employee_id, last_name, salary를 출력하시오.
SELECT employee_id, last_name, salary
FROM employees
WHERE salary >=
(SELECT avg(salary)
FROM employees)
AND department_id IN
(SELECT department_id
FROM employees
WHERE last_name LIKE '%u%');– 평균 이상의 급여 (조건1) 를 받으며, last_name에 'u'가 포함된 사원 (조건2)과 동일한 부서.
✓ 실행 결과
Q6 ) employees 테이블에서,
본인이 매니저의 역할을 하는 사원들의 employee_id, last_name을 출력하시오.
SELECT employee_id, last_name
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees);
– 서브쿼리의 매니저id 정보를 메인쿼리의 사번에 포함하기 위해 IN 연산자를 사용함.
– 총 18명의 사람들이 매니저로 있음을 알 수 있음.
✓ 실행 결과
