서브쿼리 예제

--부서명이 'IT'인 부서에 근무하는 사원들의 사번, 이름을 출력하시오

결과)
103      Alexander
104      Bruce
105      David
106      Valli
107      Diana

--답. 한 테이블에 자료 있으니 굳이 JOIN 안써도 된다
SELECT employee_id, first_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name='IT');

--내가 푼 것
SELECT e.employee_id, first_name
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
WHERE department_name = 'IT';

--이름이 'Bruce'인 사원과 같은 부서에 근무하는 사원들의 사번, 이름을 출력하시오

결과)
103      Alexander
105      David
106      Valli
107      Diana

--답. 같지않다의 표준비교연산자는 !=대신 <>
SELECT employee_id, first_name
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE first_name = 'Bruce')
AND first_name <> 'Bruce';

--내가 푼 것
SELECT employee_id, first_name
FROM (SELECT employee_id, first_name
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE first_name = 'Bruce'))
WHERE first_name != 'Bruce';

--이름이 'Bruce'인 사원과 같은 부서에 근무하면서 부서평균급여보다 많은 급여를 받는 사원들의 사번, 이름, 급여를 출력하시오

결과)

103       Alexander       9000
104       Bruce              6000

--답
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id = (SELECT department_id FROM employees
                                       WHERE first_name = 'Bruce') --5760
                )
AND department_id = (SELECT department_id FROM employees WHERE first_name = 'Bruce');

위의 코드 줄이기

where절 SELECT AVG(salary) FROM employees ~ 사람들 중에서 이름이 'Bruce'인 사원과 같은 부서에 근무하는 사람 찾기

사람들 중에서 이름이 'Bruce'인 사원과 같은 부서에 근무하는 사람
department_id = (SELECT department_id FROM employees WHERE first_name = 'Bruce')
이 사람들과 department_id가 같은 사람을 찾음 salary > WHERE department_id = e.department_id,
이걸 상호연관 서브쿼리, sql 구문이 줄어드는 효과
(SELECT department_id FROM employees WHERE first_name = 'Bruce' )= e.department_id

복잡한 서브쿼리를 AND 절 뒷쪽으로, 간단한 서브쿼리 AND 절 앞쪽으로 똑같은 결과
상호연관 서브쿼리는 메인쿼리의 행을 서브쿼리에서 사용하는것 서브쿼리의 결과값을 메인쿼리 조건비교로 사용
메인 > 서브쿼리 > 서브쿼리 결과 > 메인에 다시
안쓰는게 좋다. 퍼포먼스가 떨어진다.

SELECT employee_id, first_name, salary
FROM employees e
WHERE department_id = (SELECT department_id FROM employees WHERE first_name = 'Bruce')
AND	  salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id = e.department_id
                );

상호연관 서브쿼리 처리순서

--내가 푼 것
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
                FROM employees
                GROUP BY department_id
                HAVING department_id = (SELECT department_id
                                        FROM employees
                                        WHERE first_name = 'Bruce'))
AND department_id IN (SELECT department_id
                      FROM employees
                      WHERE department_id = (SELECT department_id
                                             FROM employees
                                             WHERE first_name = 'Bruce'));