Database/SQL

[Database] SQL 쿼리 안의 쿼리 subquery(MySQL)

SQL 쿼리 안의 쿼리 subquery(MySQL)

쿼리 안의 쿼리란 SQL 쿼리문 안에서 또 다른 SQL 쿼리문을 사용하는 것을 의미한다. 이는 서브쿼리(Subquery) , 중첩 쿼리(Nested Query) 또는 inner query라고도 불린다.

서브쿼리는 주로 다음과 같은 상황에서 사용된다.

조건에 따른 필터링 - 다른 쿼리의 결과에 따라 현재 쿼리에서 가져올 데이터를 조건적으로 필터링할 때 사용된다.
계산된 값 사용 - 다른 쿼리의 결과를 계산에 활용하거나, 계산된 값을 이용해 현재 쿼리에서 원하는 결과를 얻을 때 사용된다.
서브쿼리의 결과에 따라 정렬 및 그룹화 - 서브쿼리의 결과에 따라 정렬하거나 그룹화할 때 사용된다.

서브쿼리를 사용하는 이유는 주로 복잡한 데이터베이스 상황에서 원하는 결과를 얻기 위해 다양한 조건과 계산을 수행하기 위함이다. 또한, 서브쿼리를 통해 쿼리를 논리적으로 세분 환하고 가독성을 높일 수 있다.

이번 시간에는 서브쿼리를 다양하게 사용하는 방법들에 대해 한 번 알아보자.

(지난 시간에 만들었던 테이블을 기준으로 한다.)

subquery

앞서 언급했다시피 subquery는 쿼리 안에 또 다른 쿼리를 넣는 것이다. 이것이 무슨 뜻인지 아래 문제를 풀어가며 천천히 살펴보자.

문제 : ID가 14인 임직원보다 생일이 빠른 임직원의 ID, 생일, 이름을 조회하시오.

위의 문제를 subquery로 풀기 위해선 먼저 ID가 14인 임직원의 정보를 가져와야 한다.

SELECT birth_date FROM employee WHERE id = '14';

해당 쿼리를 실행하게 된다면 아이디가 14인 임직원의 생일이 반환되게 된다.

1995-02-01

실행 결과

만약 이 글을 읽고 따라 하시는 분이 있다면 데이터가 없더라도 위의 결과를 반환했다고 가정하면 된다.

subquery를 사용하지 않고 해당 문제를 풀려면 다음과 같이 쿼리를 작성할 수 있다. 하지만 우리는 subquery를 이용하여 문제를 해결해야 한다.

SELECT id, name, birth_date FROM employee
WHERE birth_date < '1995-02-01';

subquery를 이용하지 않을 때

그래서 subquery를 이용하지 않고 해당 문제를 풀려면 총 2번의 쿼리를 나눠서 실행해야 했다. 하지만 subquery를 사용하면 해당 문제를 단 1개의 쿼리를 이용해서 풀 수 있다. 그럼 이제 subquery를 사용해서 해당 문제를 풀어보자.

SELECT id, name, birth_date FROM employee
WHERE birth_date < (
    SELECT birth_date FROM employee WHERE id = '14' -- 첫 번째로 실행
);

생각보다 간단한다, 안에 들어가 있는 쿼리가 먼저 실행되고 그 결괏값을 바깥에 있는 쿼리의 조건문과 비교하여 실행 결과값을 반환하게 된다. 여기서 알 수 있는 점은 서브쿼리가 바깥 쿼리보다 먼저 실행된다는 점이다.

이제 여기서 간단하게 용어와 개념정리를 잠깐 하고 가자.

subquery(nested query or inner query)

SELECT, INSERT, UPDATE, DELETE에 포함된 query

outer query(main query)

subquery를 포함하는 query

subquery는 () 안에 기술된다.

() 안에 기술되는 만큼 subquery는 outer query보다 먼저 실행된다.

문제 : ID가 1인 임직원과 같은 부서, 같은 성별인 임직원들의 ID와 이름과 직군을 조회하시오.

SELECT id, name, position
FROM employee
WHERE (dept_id, sex) = (
    SELECT dept_id, sex -- 먼저 ID가 1인 임직원의 부서명, 성별을 조회
    FROM employee
    WHERE id = 1
);

서브쿼리 안에서 먼저 ID가 1인 임직원의 부서명과 성별을 조회한 후 outer query의 WHERE 조건으로 dept_id와 sex로 조건을 걸어준다.

문제 : ID가 5인 임직원과 같은 프로젝트에 참여한 임직원들의 ID를 조회하시오.

해당 문제를 subquery로 풀기 전 먼저 단일 쿼리로 어떻게 문제를 해결해야 하는지 먼저 살펴보자.

SELECT proj_id FROM works_on WHERE empl_id = 5;

먼저 아이디가 5인 임직원이 참여하고 있는 프로젝트를 조회한다.

A001
A002

결과가 위와 같이 나왔다고 가정해 보자. 그렇다면 다음 쿼드는 아래와 같이 작성할 수 있을 것이다.

SELECT DISTINCT empl_id
FROM works_on
WHERE empl_id != 5 AND (proj_id = 'A001' or proj_id = 'A002');

아이디가 5인 임직원과 같은 프로젝트에 참여한 다른 임직원들의 ID를 알고 싶은 것이니까 ID가 5인 임직원 그 자체는 먼저 제외시켜 준다. 그리고 A001과 A002 프로젝트에 or 연산자를 통해 한 번이라도 참여했던 임직원들의 ID를 조회한다. DISTINCT 같은 경우는 중복을 제거해 주는 키워드인데, 어떤 임직원 같은 경우는 A001과 A002 프로젝트에 모두 참여했을 수도 있다. 그래서 중복된 아이디를 1개로만 출력하기 위함이다.

그런데 (proj_id = 'A001' or proj_id = 'A002'); 이 부분을 보면 번거롭게 proj_id를 2번씩이나 작성해줘야 한다. 물론 기능이 작동하기에는 아무런 문제가 없지만 보다 효율적으로 작성하는 방법은 바로 IN 키워드를 사용하는 것이다.

proj_id IN ('A001','A002');

위처럼 IN 키워드를 사용하면 키워드의 의미 그대로 proj_id가 () 안에 있는 요소 중 1개라도 해당이 된다면 조회 대상으로 인식하게 된다.

SELECT DISTINCT empl_id
FROM works_on
WHERE empl_id != 5 AND proj_id IN ('A001','A002');

하지만 여기서 IN 키워드 안에 'A001', 'A002'를 잘 살펴보자, 저 결과 값은 아까 처음에 아이디가 5인 임직원이 참여한 프로젝트를 조회했을 때 사용했던 쿼리의 결과 값이다. 그렇다면, 그 쿼리 자체를 저 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
          );

이렇게 하면 2개의 쿼리였던 걸 1개로 묶고 같은 결과 값을 얻을 수 있다.

IN - v가 (v1, v2, v3...) 중에 하나와 값이 같다면 TRUE를 return 한다.
(v1, v2, v3...)는 명시적인 값들의 집합일 수도 있고 subquery의 결과 (set of multiset) 일 수도 있다.
NOT IN - v NOT IN (v1, v2, v3)의 모든 값과 값이 다르다면 TRUE를 return 한다.

unqualified attribute가 참조하는 table은 해당 attribute가 사용된 query를 포함하여 그 query의 바깥쪽으로 존재하는 모든 queires 중에 해당 attribute 이름을 가지는 가장 가까이에 있는 table을 참조한다.

문제 : ID가 7 혹은 12인 임직원이 참여한 프로젝트의 ID와 이름을 조회하시오.

SELECT P.id, P.name
FROM project P
WHERE EXISTS (
    SELECT *
    FROM works_on W
    WHERE W.proj_id = P.id AND W.empl_id IN (7,12)
);

이번에는 각 테이블에 대한 별칭을 부여하였다. project 테이블은 P로, works_on 테이블은 W. 별칭을 적용하면 서브쿼리에서도 outer query의 테이블에도 쉽게 접근할 수 있다.

correlated query - subquery가 바깥쪽 query의 attribute를 참조할 때, correlated subquery라 부름.
EXISTS - subquery의 결과가 최소 하나의 row라도 있다면 TRUE를 반환.
NOT EXISTS - subquery의 결과가 단 하나의 row도 없다면 TRUE를 반환.

문제 : 2000년대생이 없는 부서의 ID와 이름을 조회하시오.

SELECT D.id, D.name
FROM department AS D
WHERE NOT EXISTS(
    SELECT *
    FROM employee E
    WHERE E.dept_id = D.id AND E.birth_date >= '2000-01-01'
);

이번에도 테이블에 각각 별칭을 붙여주었고, 서브쿼리를 살펴보면 생일이 2000년 이후에 태어난 임직원들을 조회한다. 만약 반환된 row가 없으면 NOT EXISTS는 TRUE를 반환하게 된다.

문제 : 리더보다 높은 연봉을 받는 부서원을 가진 리더의 ID와 이름과 연봉을 조회하시오.

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
);

employee 테이블에서 리더의 아이디만 조회해야 하기 때문에 D.leader_id = E.id와 같이 조인 컨디션을 적어주고 이제 리더보다 더 많은 연봉을 받는 부서원을 찾아야 한다. 서브쿼리 부분에서 리더와 같은 부서에 있는 임직원을 먼저 찾는 쿼리는 dept_id = E.dept_id 부분이다. 그리고 employee의 id가 D.leader_id는 같지 않아야 하기 때문에 <> (같지 않다의 연산자,!=로도 사용가능) 연산자를 통해 비교한다.

v 비교연산자 ANY (subquery) - subquery가 반환한 결과들 중 단 하나라도 v와의 비교 연산이 TRUE라면 TRUE를 반환한다.
SOME도 ANY와 같은 역할을 한다.

문제 : ID가 13인 임직원과 한 번도 같은 프로젝트를 참여하지 못한 임직원들의 ID, 이름, 직군을 조회하시오.

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
);

먼저 subquery에서 아이디가 13인 임직원이 참여한 프로젝트 아이디들을 가져온다, 그 후, works_on 테이블의 proj_id가 subquery의 결과와 단 하나라도 겹치는 게 있으면 안 되기 때문에 ALL 연산자를 이용해서 비교한다. W.proj_id <> ALL (subquery)는 "그 어떤 것과도 같지 않다면"이라는 의미를 가진다. 그리고 중복된 결과가 있을 수 있기 때문에 DISTINCT를 사용해서 중복값을 제거한다.

728x90

'Database > SQL' 카테고리의 다른 글

[Database] SQL 테이블 JOIN (1)	2023.12.20
[Database] SQL three valued logic (1)	2023.12.08
[Database] SQL의 기본 개념(MySQL) (0)	2023.12.04

Contents

새소식

인기 검색어