Tech

Diary

Lecture

About Me

개발중

SQL 서브쿼리로 조회

JeongSeulho

2025년 01월 05일

준비중...
클립보드로 복사

서브 쿼리로 데이터 조회

1개의 attribute 조건에 대한 서브 쿼리

  • id가 14인 직원보다 생일이 빠른 직원의 id, 이름, 생일 조회
  • id가 14인 직원의 생일 조회 + 조회한 생일보다 빠른 생일을 가진 직원 찾기로 분리
copy 
SELECT id, name, birth_date
FROM employee
WHERE birth_date < (
    SELECT birth_date
    FROM employee
    WHERE id = 14
);
  • ()안의 쿼리를 subquery라고 함
  • ()외부의 쿼리를 outer query라고 함

여러개의 attribute 조건에 대한 서브 쿼리

  • id가 1인 직원과 같은부서 AND 같은 성별인 직원의 id와 이름, 직군 조회
copy 
SELECT id, name, position
FROM employee
WHERE (dept_id, sex) = (
    SELECT dept_id, sex
    FROM employee
    WHERE id = 1
);

IN 활용 서브 쿼리

  • id가 5인 직원과 같은 프로젝트에 참여한 직원들의 id 조회
copy 
SELECT DISTINCT empl_id -- 직원 A와 2001, 2002 프로젝트 둘다 같이 한다면 중복되므로
FROM works_on
WHERE empl_id != 5 -- 본인 제외
AND proj_id IN ( -- id가 5인 직원이 참여한 프로젝트 id들
    SELECT proj_id
    FROM works_on
    WHERE empl_id = 5
);
  • v IN(v1, v2, ...) : v가 v1, v2, … 중 같은게 있으면 TRUE
  • v NOT IN(v1, v2, ...) : v가 v1, v2, … 중 같은게 있으면 FALSE

unqualified attribute : 테이블이 명시되지 않은 attribute
unqualified attribute는 가장 가까이 있는 테이블을 참조, 즉 서브 쿼리 내부의 attribute는 서브 쿼리 내부의 테이블을 참조

2 depth 이상의 서브 쿼리

  • id가 5인 임직원과 같은 프로젝트에 참여한 직원들의 id와 이름 조회
copy 
SELECT id, name
FROM employee -- 이름을 알기 위해 employee 테이블 사용
WHERE id IN (
    SELECT DISTINCT empl_id
    FROM works_on
    WHERE empl_id != 5
    AND proj_id IN (
        SELECT proj_id
        FROM works_on
        WHERE empl_id = 5
    )
);

FROM 절에 서브 쿼리 사용

  • id가 5인 임직원과 같은 프로젝트에 참여한 직원들의 id와 이름 조회
  • 위와 같은 쿼리를 다른 방법으로 작성
copy 
SELECT id, name
FROM employee, (
  SELECT DISTINCT empl_id
  FROM works_on
  WHERE empl_id != 5
  AND proj_id IN (
    SELECT proj_id
    FROM works_on
    WHERE empl_id = 5
  )
) AS temp_table -- 서브쿼리로 같은 프로젝트 참여한 직원들의 id를 조회, 임시 테이블로 사용
WHERE id = temp_table.empl_id; -- 임시 테이블 결과에 있는 직원들의 id로 employee 테이블에서 id, name 조회

EXISTS 활용 서브 쿼리

  • id가 7 또는 12인 직원이 참여한 프로젝트의 id와 이름 조회
copy 
SELECT P.id, P.name
FROM project P
WHERE EXISTS (
    SELECT *
    FROM works_on W
    WHERE W.empl_id IN (7, 12)
    AND W.proj_id = P.id
);
  • EXISTS : subquery의 결과로 나오는 tuple이 존재하면 TRUE => TRUE이면 결과에 포함
  • NOT EXISTS : subquery의 결과로 나오는 tuple이 존재하지 않으면 TRUE => TRUE이면 결과에 포함

correlated subquery : 서브쿼리가 외부 쿼리의 attribute를 참조하는 서브쿼리

  • EXISTS를 IN으로 변환

Image

  • 서브쿼리에서 외부 테이블 P를 참조하는 것을 외부로 빼내고 IN으로 변환

NOT EXISTS 활용 서브 쿼리

  • 2000년대생이 없는 부서의 id와 이름 조회
copy 
SELECT D.id, D.name
FROM department D
WHERE NOT EXISTS (
    SELECT *
    FROM employee E
    WHERE E.dept_id = D.id
    AND E.birth_date >= '2000-01-01'
);
  • NOT EXISTS를 NOT IN으로 변환

Image

ANY 활용 서브 쿼리

  • 부서의 리더보다 높은 연봉을 받는 부서원을 가진 리더의 id와 이름, 연봉 조회
copy 
SELECT E.id, E.name, E.salary
FROM department D, employee E
WHERE D.leader_id = E.id AND E.salary < ANY (
    SELECT salary
    FROM employee
    WHERE id != D.leader_id AND dept_id = E.dept_id
);
  • v comparison_operator ANY(subquery) : 서브쿼리의 결과 중 하나라도 v와 비교 연산자를 만족하면 TRUE

ANY 활용과 SELECT 절에 서브쿼리 사용

  • 부서의 리더보다 높은 연봉을 받는 부서원을 가진 리더의 id와 이름, 연봉 조회 그리고 해당 부서의 최고 연봉을 조회
copy 
SELECT E.id, E.name, E.salary, (
    SELECT MAX(salary)
    FROM employee
    WHERE dept_id = E.dept_id
) AS dept_max_salary
FROM department D, employee E
WHERE D.leader_id = E.id AND E.salary < ANY (
    SELECT salary
    FROM employee
    WHERE id != D.leader_id AND dept_id = E.dept_id
);

ALL 활용 서브 쿼리

  • id가 13인 직원과 같이 진행하는 프로젝트가 하나도 없는 직원들의 id, 이름, 직군 조회
copy 
SELECT DISTINCT E.id, E.name, E.position
FROM employee E, works_on W
WHERE E.id = W.empl_id AND W.proj_id != ALL (
    SELECT proj_id
    FROM works_on
    WHERE empl_id = 13
);
  • v comparison_operator ALL(subquery) : 서브쿼리의 결과 모두 v와 비교 연산자를 만족하면 TRUE
CS