[데이터베이스] 6장 - SQL 로 데이터 조회하기! SubQuery, IN, EXISTS, ANY, ALL

컴퓨터 공부/📗 CS

[데이터베이스] 6장 - SQL 로 데이터 조회하기! SubQuery, IN, EXISTS, ANY, ALL

letzgorats 2024. 1. 5. 20:26

✅ SubQuery

statement 는 아래와 같다.

- ID가 14인 임직원보다 생일이 빠른 임직원의 ID, 이름, 생일을 알고 싶다.

: 여기서 임직원의 ID 와 이름과 생일을 알고 싶다고 했으니까, employee 테이블이 필요한 건 확실해 보인다.

: 그럼, 우선 ID 가 14인 직원을 뽑아낸 뒤, 그 직원의 생일을 알아내고, 다시 조건에 그 생일보다 빠른 직원들을 찾으면 될 것 같다.

: 어떻게 하면 좋을까?

먼저, 현재 EMPLOYEE 테이블은 아래와 같다.

그럼, 먼저 ID 가 14인 직원의 생일(birth_date)을 뽑아내보면, 아래와 같다.

SELECT birth_date
FROM EMPLOYEE
WHERE id = 14;

ID가 14인 직원의 생일이 '1992-08-04' 라는 것을 알았으니, 이 생일을 기준으로 이보다 빠른 생일을 가진 임직원들의 ID, 이름, 생일을 뽑아내면 쿼리를 다음과 같이 짤 수 있다.

SELECT id, name, birth_date
FROM EMPLOYEE
WHERE birth_date < '1992-08-04';

그러면 직원들 중 생일이 '1992-08-04'보다 빠른 직원들이 출력된다.

지금 쿼리를 2번 거쳐서 진행을 했는데, 이런 과정을 한 번의 쿼리로 압축해서 짤 순 없을까?

...

이 때 필요한 개념이 바로 'SubQuery(서브쿼리)' 이다!

두 번째로 진행한 쿼리 중에서 아래 부분의 '1992-08-04' 는 처음 진행한 쿼리의 결과이다.

WHERE birth_date < '1992-08-04';

즉, '1992-08-04' 를 처음 진행했던 쿼리로 대체하면 될 것 같다.

그렇게, 쿼리를 작성해보면, 아래와 같다.

SELECT id, name, birth_date
FROM EMPLOYEE
WHERE birth_date < (
    SELECT birth_date
    FROM EMPLOYEE
    WHERE id = 14
);

결과는 동일하게, 5명의 직원이 나오는 것을 확인할 수 있다.

그럼 용어를 짚고 넘어가보자!

※ subquery : (nested query or inner query) : SELECT, INSERT, UPDATE, DELETE에 포함된 쿼리
※ outer query : (main query) : subquery를 포함하는 쿼리
※ subquery는 ( ) 안에 기술된다.

→ subquery(서브쿼리) 는 말 그대로, 안 쪽에 있는 쿼리를 말한다. 부수적인 쿼리라는 뜻이다. 이는 (괄호)안에서 기술되어야 한다.

→ outerquery(외부쿼리) 는 이런 내부쿼리를 포함하고 있는 바깥쪽의 메인 쿼리를 의미한다.

다른 statement 는 아래와 같다.

- ID가 1인 임직원과 같은 부서, 같은 성별인 임직원들의 ID와 이름과 직군을 알고 싶다.

: 그럼, 우선 ID 가 1인 임직원을 뽑아낸 뒤, 그 직원의 "부서, 성별" 알아내고, 그 "부서와 성별"이 같은 직원들의 ID 와 이름, 직군을 찾으면 될 것 같다.

다시 현재 EMPLOYEE 테이블을 확인해보자.

여기서, ID 가 1인 직원의 '부서, 성별'을 뽑아내보자.

SELECT dept_id, sex
FROM EMPLOYEE
WHERE id = 1;

그럼, 위와 같이 ID가 1인 직원의 부서는 1003 이고, 성별이 M(남자) 인것을 알 수 있다.

우리가 이제 바라봐야 할 것은 이 정보이다. 즉, (부서가 1003이고, 성별이 남자) 인 직원의 'id, name, dept_id' 를 또 뽑아내면 되는 것이다.

쿼리를 작성하면 아래와 같다.

SELECT id,name,dept_id
FROM EMPLOYEE
WHERE (dept_id,sex) = (1003,'M');

그러면, 이제 이 진행과정을 한 번의 쿼리로 압축하려면,

로직은 간단하다. (1003,'M') 의 정보가 뽑아내어진 첫 번째 쿼리를 두 번째 쿼리의 조건절에 있는 (1003,'M') 대신 써주면 되는 것이다.

최종 쿼리는 아래와 같다.

SELECT id,name,dept_id
FROM EMPLOYEE
WHERE (dept_id,sex) = (
    SELECT dept_id, sex
    FROM EMPLOYEE
    WHERE id = 1
);

역시 결과는 동일하다.

이처럼, 서브쿼리의 결과는 하나 이상의 attribute 도 반환할 수 있다. 지금은 dept_id(부서)와 sex(성별) 속성을 뽑아낸 것이다.

다른 statement 는 아래와 같다.

- ID가 7인 임직원과 같은 프로젝트에 참여한 임직원들의 ID 를 알고 싶다.

우선, 우리가 찾아야 하는 것은 ID 가 7 인 임직원이 참여한 프로젝트가 무엇인지 알아야 한다. WORKS_ON 테이블에는 임직원의 id 와 프로젝트 id 가 저장되어 있으므로, 쿼리로 나타내면 아래와 같다.

SELECT proj_id
FROM WORKS_ON
WHERE empl_id = 7;

SELECT proj_id FROM WORKS_ON WHERE empl_id = 5;

그럼 이제 프로젝트 ID 가 2002,2003 인 프로젝트를 참여하는 임직원의 ID 를 뽑아내야 하니까 아래와 같이 작성할 수 있다.

SELECT empl_id
FROM WORKS_ON
WHERE empl_id != 7 and (proj_id = 2002 OR proj_id = 2003);

SELECT empl_id FROM WORKS_ON WHERE empl_id != 7 and (proj_id = 2002 OR proj_id = 2003);

7번 직원과 같은 프로젝트를 하는 임직원들을 추려야하므로, 우선 직원ID가 7번인 직원은 당연히 제외하고, 프로젝트 ID 가 2002, 2003 인 직원ID를 WOKRS_ON 테이블에서 뽑아내면 되는 것이다.

근데 왼쪽 결과에서는 중복이 나타난다. 어떤 임직원 같은 경우에는(여기서는 4번 직원) 두 프로젝트 모두에 참여한 경우도 있기 때문이다. 따라서 이 중복을 제거하기 위해 empl_id 를 DISTINCT 해준 것이 오른쪽 결과이다.

SELECT DISTINCT empl_id
FROM WORKS_ON
WHERE empl_id != 7 and (proj_id = 2002 OR proj_id = 2003);

근데, 지금은 (proj_id = 2002 OR proj_id = 2003); 처럼 뽑아낸 프로젝트ID 가 1개가 아니라 다중항목이다.

이 때, 바로 IN 키워드를 사용하면 편하다.

proj_id 라는 공통된 속성을 (괄호) 밖으로 빼서 IN 이라는 키워드를 활용하면 다음과 같이 작성할 수 있다.

SELECT DISTINCT empl_id
FROM WORKS_ON
WHERE empl_id != 7 and proj_id IN (2002,2003);

IN 을 사용하고 (괄호)안에 해당하는 값들을 나열해주면, OR 속성과 동일한 의미가 된다.

그렇다면, 최종적으로 이런 두 번의 과정을 한 번의 쿼리로, 즉 서브쿼리를 활용해서 쿼리를 작성하면 어떻게 하면 될까?

지금 (2002,2003) 이라는 결과는 첫 번째 진행했던 쿼리의 결과이기 때문에 그냥 해당 부분에 대체해주면 끝난다.

SELECT DISTINCT empl_id
FROM WORKS_ON
WHERE empl_id != 7 and proj_id IN (
    SELECT proj_id
    FROM WORKS_ON
    WHERE empl_id = 7
);

SELECT DISTINCT empl_id FROM WORKS_ON WHERE empl_id != 7 and proj_id IN ( SELECT proj_id FROM WORKS_ON WHERE empl_id = 7 );

결과는 동일하게 출력한다.

✅ IN

★ 정리하면, IN 은 다음과 같이 정리할 수 있다.

value IN( value1, value2, value3, ...) → value 가 (value1, value2, value3, ... ) 중 하나와 값이 같다면 True 를 반환한다.
(value1, value2, value3, ... ) 는 명시적인 값들의 집합일 수도 있고 서브쿼리의 결과 (set or multiset) 일 수도 있다.
value NOT IN (value1, value2, value3 ) → value 가 (value1, value2, value3, ... ) 의 모든 값과 값이 다르다면, True 를 반환한다.
unqualified attribute 가 참조하는 테이블은
- 해당 attribute가 사용된 쿼리를 포함하여 그 쿼리의 바깥쪽으로 존재하는 모든 쿼리들 중에
- 해당 attribute 이름을 가지는 가까이에 있는 테이블을 참조한다.

마지막 특징이 무슨말인지, 예시를 들어서 다시 살펴보자.

SELECT DISTINCT empl_id
FROM WORKS_ON
WHERE empl_id != 7 and proj_id IN (
    SELECT proj_id
    FROM WORKS_ON
    WHERE empl_id = 7
);

여기서,empl_id , proj_id 와 같은 attribute 들이 어떤 테이블에 속하는지 명시를 하지 않았다.

outer 쿼리와 inner(sub) 쿼리가 둘 다 WORKS_ON 테이블을 참조하기 때문에, 각 attribute가 outer 쿼리의 WORKS_ON 테이블에 해당하는 것인지, inner(sub) 쿼리의 WORKS_ON 테이블에 해당하는 것인지 헷갈릴 수 있을 것이다.

이런식으로, unqualified attribute 일 경우에는 해당 attribute 가 사용된 쿼리를 포함하여 바깥쪽으로 존재하는 모든 쿼리들 중에 해당 attribute 를 가지는 테이블 중 가장 가까운 테이블을 참조한다고 보면 된다.

위 쿼리에서 예를 들자면, 내부 쿼리에 있는 proj_id 와 empl_id 는 현재 바라보고 있는 서브쿼리에 WOKRS_ON 테이블에 있기 때문에 그 attribute는 안쪽의 WORKS_ON 테이블을 참조하는 것이고, 바깥쪽의 empl_id 와 proj_id 는 현재 바라보고 있는 쿼리인 바깥쪽WORKS_ON 테이블을 참조한다고 이해하면 된다.

그럼 statement 가 아래와 같다고 해보자. 임직원들의 ID 뿐만이 아닌 name 도 알고 싶다고 가정해보자.

- ID가 7인 임직원과 같은 프로젝트에 참여한 임직원들의 ID 와 이름을 알고 싶다.

아까 임직원의 ID 까지만 안 결과가 아래와 같았다.

SELECT DISTINCT empl_id
FROM WORKS_ON
WHERE empl_id != 7 and proj_id IN (
    SELECT proj_id
    FROM WORKS_ON
    WHERE empl_id = 7
);

여기서 임직원의 이름을 알고 싶지만, WORKS_ON 테이블에는 직원 이름에 대한 attribute 는 없다.

직원이름은 EMPLOYEE 테이블에 있는데, 우리는 저 테이블을 활용해서 EMPLOYEE 테이블에 접근하면 된다. 우리가 지금까지 알고 있는 정보는 구하고자 했던 직원들의 ID 들이다.

그럼 이를 활용해서 또 서브쿼리를 작성하면 아래와 같다.

SELECT id, name
FROM EMPLOYEE
WHERE id IN (
    SELECT DISTINCT empl_id
    FROM WORKS_ON
    WHERE empl_id != 7 and proj_id IN (
        SELECT proj_id
        FROM WORKS_ON
        WHERE empl_id = 7
    )
);

이전에 구했던 ID 들의 집합을 또 WHERE id IN (이전 결과) 를 통해 조건을 걸고 EMPLOYEE 테이블에 있는 id 와 name을 뽑아낸 것이다.

이렇게 서브쿼리를 계속해서 만들면서 임시 테이블을 만들고 그 임시테이블에서 원하는 값을 추출할 수 있다는 것이다.

여기서 서브쿼리의 결과도 하나의 테이블이기 때문에 FROM (서브쿼리의 결과 테이블) 로 인식할 수 있다.

그럼, 이 (서브쿼리의 결과 테이블) 도 하나의 새로운 테이블이라고 생각하고 AS 를 사용해서 별칭을 지어줄 수 있지 않을까?

우리가 5장에서 AS 에 대해 배웠는데, AS는 FROM 뒤에 나오는 테이블에 대해 별칭을 만들고 싶을 때 사용하는 키워드라고 했다.

그럼 똑같이 이 원리를 적용해보면 위 쿼리는 아래와 같이 작성할 수도 있겠다.

SELECT id, name
FROM EMPLOYEE,
   (
    SELECT DISTINCT empl_id
    FROM WORKS_ON
    WHERE empl_id != 7 and proj_id IN (
        SELECT proj_id
        FROM WORKS_ON
        WHERE empl_id = 7   
        )
   ) AS ANOTHER_E
WHERE id = ANOTHER_E.empl_id;

역시 결과는 똑같이 나왔다. AS 라는 키워드를 사용해서 서브쿼리의 결과 테이블을 ANOTHER_E 라는 별칭으로 지어주고, 그 ANOTHER_E 테이블의 empl_id 값과 같은 id 값을 가지는 직원들을 EMPLOYEE 테이블에서 찾아낸 후, 직원의 ID와 이름과 출력해준 것이다. (AS는 생략가능하므로 여기서도 AS ANOTHER_E 대신에 서브쿼리를 괄호로 묶고 바로 ANOTHER_E 를 써주는 것도 가능하다!)

SELECT id, name
FROM EMPLOYEE,
   (
    SELECT DISTINCT empl_id
    FROM WORKS_ON
    WHERE empl_id != 7 and proj_id IN (
        SELECT proj_id
        FROM WORKS_ON
        WHERE empl_id = 7   
        )
   ) ANOTHER_E
WHERE id = ANOTHER_E.empl_id;

이렇게, 서브쿼리는 WHERE 절에서 뿐만이 아니라 FROM 절에서도 사용이 가능하다는 것을 알 수 있다.

✅ EXISTS

statement 는 아래와 같다.

- ID가 7 혹은 12인 임직원이 참여한 프로젝트의 ID 와 이름을 알고 싶다.

우선, 우리가 찾아야 하는 것은 ID 가 7 과 12 인 임직원이 참여한 프로젝트가 무엇인지 알아야 한다. WORKS_ON 테이블에는 임직원의 id 와 프로젝트 id 가 저장되어 있으므로, 쿼리로 나타내면 아래와 같다.

SELECT DISTINCT proj_id
FROM WORKS_ON
WHERE empl_id IN (7,12);

SELECT DISTINCT proj_id FROM WORKS_ON WHERE empl_id IN (7,12);

역시 DISTINCT 를 통해서 중복된 proj_id 를 걸러줬다. 이제, 7번과 12번 직원이 참여한 프로젝트가 2002번, 2003번 인 것을 알았으니까 이 프로젝트의 이름을 알기 위해서 PROJECT 테이블에 접근한다.

SELECT id, name
FROM PROJECT
WHERE id IN
(
SELECT DISTINCT proj_id
FROM WORKS_ON
WHERE empl_id IN (7,12)
);

이렇게 IN 을 통해서 쿼리를 작성할 수도 있다. 하지만, 이번에는 EXIST 키워드를 사용해서 접근해보겠다.

▶ EXISTS 를 사용해서 이렇게 작성하면 될까?

SELECT id, name
FROM PROJECT
WHERE EXISTS
(
    SELECT DISTINCT proj_id
    FROM WORKS_ON
    WHERE empl_id IN (7,12)
);

EXISTS 의 특징은 IN 과는 다르게 EXISTS 키워드 앞에 타겟팅하는 attribute 가 없다. 위 쿼리처럼 작성하면 아래 결과가 나온다.

분명, IN 키워드를 사용한 결과랑 같게 나와야 하는데 다른 결과가 출력됐다.

해당 쿼리는 PROJECT 테이블에서 모든 프로젝트의 id 와 name 을 선택하는 쿼리이다. 그러나, WHERE EXISTS 절을 통해 WORKS_ON 테이블에 empl_id 가 7 이거나 12인 행이 존재하는지를 확인한다.

중요한 점은, 여기서 WORKS_ON 테이블의 porj_id 가 PROJECT 테이블의 id 와 연결되지 않았다는 것이다. 즉, 이 쿼리는 WORKS_ON 테이블에 empl_id 가 7 또는 12 인 어떤 행이 존재하기만 하면, PROJECT 테이블의 모든 행을 반환한다는 의미이다.

그래서, WORKS_ON 테이블에 7번 12번인 empl_id 가 존재하기에 PROJECT 테이블의 모든 값들이 다 나오게 된 것이다.

▶ 그럼 다시 두 테이블을 연결해서 제대로 작성해보자.

SELECT P.id, P.name
FROM PROJECT P
WHERE EXISTS
(
SELECT DISTINCT proj_id
FROM WORKS_ON W
WHERE W.proj_id = P.id and W.empl_id IN (7,12)
);

이 쿼리는 PROJECT 테이블에서 id 와 name 을 선택하되, WHERE EXISTS 절 내부의 서브쿼리가 바뀌었다.

WHERE EXISTS 절은

WORKS_ON 테이블에서 empl_id 가 7이나 12인 특정 행이 존재하며,

그 행의 proj_id 가 현재 PROJECT 테이블의 id와 일치하는 경우에만 해당 PROJECT 행을 반환한다.

즉, 이 쿼리는 empl_id 가 7번,12 번인 직원이 작업하는 프로젝트만 출력하는 것이다.

★ 정리하면, EXISTS 는 다음과 같이 정리할 수 있다

EXISTS 는 적어도 하나의 행이 존재하는지를 판별하는 데 사용하는 키워드이다.
적어도 하나의 행을 반환하면, True 를 반환하고 그렇지 않으면 False 를 반환한다.
- EXISTS → 서브쿼리의 결과가 최소 하나의 row 라도 있다면 TRUE를 반환
- NOT EXISTS → 서브쿼리의 결과가 단 하나의 row도 없다면 TRUE를 반환
상관 서브쿼리(correlated query) 와 함께 사용 → 서브쿼리가 바깥쪽 쿼리의 attribute 를 참조할 때, 그 서브쿼리를 correlated subquery 라고 부른다. 즉, 상관서브쿼리는 외부 쿼리의 테이블에 있는 값에 따라 결과가 달라지는 서브쿼리이다.
즉, EXISTS는 데이터의 존재여부만을 확인하고, 실제 데이터 값을 반환하지는 않는다. 최소한의 특정 조건을 만족하는 데이터가 있는지의 여부를 판단할 때 유용하게 쓰인다.
주의할 점은 NULL 값을 포함하는 행을 반환하더라도, EXISTS는 유효한 행으로 간주한다는 점을 알고있자.

그럼 다른 statement 를 봐보자.

- 2000년대생 이상이 없는 부서의 ID와 이름을 알고 싶다.

우선, 필요로 하는 테이블은 DEPARTMENT 테이블과 EMPLOYEE 테이블 인 것 같다. 두 테이블을 연관시켜서 원하는 데이터를 뽑아보자.

2000년대생 이상이 없는 직원들의 부서Id 와 부서 name 을 알고 싶은 것이므로, 2000년대 생이 없는 EMPLOYEE 테이블을 추려줘야 할 것인데, 이 때 "없는" 이라는 워딩에 집중해본다면, NOT EXIST 키워드를 활용하면 될 것 같다.

그리고 뽑아낸 2000년대 생 미만의 직원들의 부서ID 와 부서 name 을 뽑아내기 위해선 DEPARTMENT 테이블을 활용하면 될 것이다.

최종 쿼리는 아래와 같다.

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

서브쿼리에서 birth_date가 '2000-01-01' (2000년대) 이상인 직원들을 가져오는 SELECT 문을 구현해서 SELECT 문이 반환하는 결과가 아무것도 없다면, 2000년대생이 없다는 것이니까 NOT EXISTS가 TRUE 가 되면서 결국 2000년대생이 없는 부서의 id,name을 가져올 수 있게 되는 로직이다.

테이블에서 직관적으로 확인할 수 있듯이, 'ALLU' 와 'Jay'만 생년월일이 2000년대 이상이므로, 이들이 속한 부서 1003번을 제외한 모든 부서들이 결과로 잘 출력된 것을 확인할 수 있다.

※ 해당 statement를 NOT IN 으로도 구현가능하다.

SELECT D.id, D.name
FROM DEPARTMENT AS D
WHERE D.id NOT IN(
    SELECT E.dept_id
    FROM EMPLOYEE E
    WHERE E.birth_date >= '2000-01-01'
 );

→ 이렇게, IN 과 EXISTS 는 다른 프로그래밍 언어로 굳이 비교하자면, for과 while 과 같이 매우 비슷한 성격을 띈 키워드이기 때문에 서로 바꿔가면서도 동일한 결과를 도출 할 수 있다.

✅ ANY

statement 는 아래와 같다.

- 리더보다 높은 연봉을 받는 부서원을 가진 리더의 ID와 이름과 연봉을 알고 싶다.

이 때 필요한 테이블도 EMPLOYEE 테이블과 DEPARTMENT 테이블 처럼 보인다.

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

여기선 WHERE 절이 중요한데, 한 번 분석해보자.

우선, 최종적을 (리더의 ID와 이름과 연봉)을 알고 싶은 것이므로 우린 어떤 직원이 리더인지 아닌지부터 알아야 한다.

특정 직원이 리더인지 아닌지 알려면 DEPARTMENT 테이블에서 leader_id 를 참조해야 한다.

그 후, EMPLOYEE 테이블에서 어떤 직원이 DEPARTMENT 테이블에서의 리더인지 보면 되는 것이다.

때문에, 즉 EMPLOYEE id 와 DEPARTMENT의 leader_id 는 서로 참조를 하고 있으므로 join condition 을 적어준다면, DEPARTMENT 의 leader_id = EMPLOYEE 의 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
);

먼저, 리더와 같은 부서에 있는 임직원을 찾아야 한다. 그 조건이 바로 서브쿼리 WHERE 절에 있는 dept_id = E.dept_id 조건이다.

E.dept_id 에서의 E는 서브쿼리 밖에 있는 외부쿼리 FROM 절의 EMPLOYEE 테이블을 가리키는 반면,

dept_id 는 서브쿼리 내부에 있는 FROM 절의 EMPLOYEE 를 가리킨다.

즉, 외부의 EMPLOYEE 테이블은 결국 leader가 있는 EMPLOYEE 테이블이고 내부의 EMPLOYEE 테이블은 기존 EMPLOYEE 테이블이므로 dept_id 를 동일시 해주는 작업은 리더와 같은 부서에 있는 직원을 찾는다는 의미이다.

이 때, id <> leader_id 의 의미는 리더이외의 부서원을 찾아야 하므로 리더는 제외시켜주는 작업이다.

(이 조건은 id != leader_id 로 적어줘도 된다.)

그렇게 리더와 같은 부서에 있는 다른 직원들을 잘 찾았으면, 그 직원들을 대상으로 연봉을 찾아줘야 한다.

그 작업이 서브쿼리에서 SELECT salary 를 해주는 작업이다.

그렇게 연봉이 추출되면 이 연봉이 결국 리더의 연봉보다 높은지의 여부를 찾아야 하므로 ANY 키워드를 사용해서 E.salary (리더의 연봉)이 그 부서원들의 연봉 중에서 단 하나라도 적은 경우가 있다면, WHERE 절은 TRUE 가 돼서 해당되는 리더의 id, name, salary 를 뽑아낼 수 있는 것이다.

다시 한번 결과를 확인하면, 아래와 같은 리더가 나온다.

HYEWON 은 Product 부서의 리더인데, Product 부서에 속한 직원 SUZANNE 과 CURRY 가 HYEWON 보다 연봉이 높은 것을 확인할 수 있다. 나머지 부서에서는 리더의 연봉이 다른 부서원들의 연봉보다 높거나 같다.

★ 정리하면, ANY 는 다음과 같이 정리할 수 있다

"value 비교 연산자 ANY (서브쿼리)" → 서브쿼리가 반환한 결과들 중에 단 하나라도 value와의 비교 연산이 TRUE 라면, TRUE를 반환한다.
여기서 비교 연산자(comparison operator) 는 ( <= , < , = , > , >= , <> , != 등등) 이다.
SOME 도 ANY 와 동일한 역할을 한다.

Q. 그럼, 최종 결과에서 HYEWON 이 속한 부서에서 최고 연봉까지 알고 싶다면, 어떻게 쿼리를 수정하면 좋을까?

HYEWON 은 Product 부서의 리더인데, Product 부서에 속한 직원 SUZANNE 과 CURRY 가 HYEWON 보다 연봉이 높다. 이들 중에서 최고 연봉을 가진 직원은 CURRY 인 것을 EMPLOYEE 테이블에서 직관적으로 확인할 수 있다. 때문에, 아마 CURRY 의 연봉인 340000000 이 출력이 될텐데, 이 값을 어떻게 뽑아낼 수 있을까?

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

아까 쿼리에서 바뀐 것은 없고 SELECT에서 뽑아낼 열을 추가만 해줬다.

SELECT 절의 서브쿼리 밖에 있는 테이블의 dept_id 는 결국 리더만 속한 dept_id 이므로 그 리더의 dept_id 와 같은 dept_id 즉, 그 리더와 같은 부서에 속한 임직원들 중에서 가장 높은 연봉을 추출하는 서브쿼리이다.

마지막으로, 그 서브쿼리의 결과를 dept_max_salary 라는 별칭으로 컬럼을 만들어 준 것이다.

결과는 아래와 같이 나온다.

의도한대로 HYEWON이 속한 부서의 직원들 중 최고연봉자인 CURRY의 연봉인 340000000이 잘 나왔다.

이렇게, 서브쿼리는 SELECT 절에서도 활용될 수 있다.

✅ ALL

statement 는 다음과 같다.

- ID가 13인 임직원과 한번도 같은 프로젝트에 참여하지 못한 임직원들의 ID, 이름, 직군을 알고 싶다.

우선, 필요로 하는 테이블은 WORKS_ON 테이블과 EMPLOYEE 테이블 인 것 같다. 두 테이블을 연관시켜서 원하는 데이터를 뽑아보자.

WORKS_ON 테이블을 보면, Id 가 13인 임직원은 현재 2001번 프로젝트에 참여중이다.

그럼, 우린 2001번 프로젝트를 진행하고 있지 않은 모든 직원들의 id,name,position을 뽑아내야 한다.

그럼 먼저, WORKS_ON 테이블에서 empl_id = 13 인 proj_id 를 뽑아내는 쿼리부터 작성해보자.

SELECT proj_id
FROM WORKS_ON
WHERE empl_id = 13

그럼, 2001 번 프로젝트가 뽑힌 것이다. 그럼 우린, 이 2001번 프로젝트를 제외한 모든 프로젝트를 원하는 것이므로, 이 때 ALL 키워드를 사용하면 된다.

이제 WORKS_ON 테이블에서의 proj_id 가 위에 있는 쿼리 결과(id가 13인 직원이 참여하고 있는 프로젝트)에 단 하나라도 겹치는 것이 있으면 안되니까 <> ALL 을 써야 한다. (물론, NOT EXISTS 를 활용해도 좋다.)

※ ANY 나 ALL 앞에는 비교연산자가 와야 한다.

즉 value가 서브쿼리 결과의 모든 값과 비교하는 것은 (비교연산자 ALL)

즉 value가 서브쿼리 결과의 적어도 하나의 값과 비교하는 것은 (비교연산자 ANY)

이를 계산하고 판별하는 로직이다.

최종 쿼리는 아래와 같다.

SELECT E.id, E.name, E.position
FROM EMPLOYEE E
WHERE E.id <> ALL
(
    SELECT W.empl_id
    FROM WORKS_ON W
    WHERE W.proj_id IN
    (
        SELECT W2.proj_id
        FROM WORKS_ON W2
        WHERE W2.empl_id = 13
    )
);

※ NOT EXISTS 를 사용한다면, 아래와 같이 작성할 수 있다.

SELECT E.id, E.name, E.position
FROM EMPLOYEE E
WHERE NOT EXISTS (
    SELECT 1
    FROM WORKS_ON W1
    WHERE W1.empl_id = E.id
    AND W1.proj_id IN (
        SELECT W2.proj_id
        FROM WORKS_ON W2
        WHERE W2.empl_id = 13
    )
);

★ 정리하면, ALL 은 다음과 같이 정리할 수 있다

"value 비교 연산자 ALL (서브쿼리)" → 서브쿼리가 반환한 결과들과 value와의 비교 연산이 모두 TRUE 라면, TRUE를 반환한다.
여기서 비교 연산자(comparison operator) 는 ( <= , < , = , > , >= , <> , != 등등) 이다.

💡성능 비교 : IN vs EXISTS

→ RDBMS 의 종류와 버전에 따라 다르며, 최근 버전은 많은 개선이 이루어져서 IN 과 EXISTS 의 성능 차이가 거의 없다고 한다.

💡위 내용은 MySQL 기준이다. 다른 RDBMS 의 SQL 문법은 조금씩 다를 수 있다.

💡위 내용은 SubQuery 와 관련하여 대표적으로 중요한 '기본기'들을 담고 있다. 디테일한 내용들은 각자 공부해보자!

※ 번외 - ~~쉬운코드 유튜브 영상~~을 보고 오류인 부분이라고 생각해서 댓글을 길게 달았는데, 몇 분 안돼서 바로 삭제당했다...계속 댓글을 써도 이젠 내 댓글이 써지지도 않았다. 이건 뭔 경우일까...?

오류인 내용은 같이 바로잡아가면 되는 것이고, 생각을 서로 말해보면서 개선해나가는 것이 중요한데, 커뮤니티의 순기능을 막아버린다면, 잘못된 정보는 고립될 여지가 있을 터인데,,, 이해가 가지 않는다. 서로 의견을 나누고 정보공유를 하는 선순환이 자리잡은 줄 알았는데, 아직도 이런 경우가 남아있는 것을 보면 좀 현타가 온다...

(해당 영상에서 ALL 파트는 다른 스터디 조원들과 상의한 끝에 틀린 부분이 맞는 것 같다.)

참고자료

- 유튜브 쉬운코드

저작자표시

'컴퓨터 공부 > 📗 CS' 카테고리의 다른 글

[데이터베이스] 8장 - SQL 로 데이터 조회하기! JOIN (4)	2024.01.09
[데이터베이스] 7장 - SQL 로 데이터 조회하기! NULL 의 의미와 three-valued logic (0)	2024.01.09
[데이터베이스] 5장 - SQL 로 데이터 조회하기! SELECT 를 활용해서 데이터를 읽어오는 기본적인 문법과 관련 키워드들 (4)	2024.01.04
[데이터베이스] 4장 - SQL로 DB에 데이터를 추가(insert)하고 수정(update)하고 삭제(delete)하는 방법 (4)	2024.01.02
[데이터베이스] 3장 - SQL 개념과 SQL로 데이터베이스를 정의하는 법 (4)	2024.01.02

현재글[데이터베이스] 6장 - SQL 로 데이터 조회하기! SubQuery, IN, EXISTS, ANY, ALL

알루 코딩