6. SQL로 데이터 조회하기 2

subquery를 이용한 데이터 조회

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

SELECT birth_date FROM employee WHERE id = 14;
+------------+
| birth_date |
+------------+
| 1992-08-04 |
+------------+
1 row in set (0.02 sec)
mysql> SELECT id,name,birth_date FROM employee
    -> WHERE birth_date < "1992-08-04";
+----+--------+------------+
| id | name   | birth_date |
+----+--------+------------+
|  1 | MESSI  | 1987-02-01 |
|  5 | DINGYO | 1990-11-05 |
|  6 | JULIA  | 1986-12-11 |
|  9 | HENRY  | 1982-05-20 |
| 10 | NICOLE | 1991-03-26 |
| 13 | JISUNG | 1989-07-07 |
+----+--------+------------+
6 rows in set (0.00 sec)

한 문장으로 표현

mysql> SELECT id,name,birth_date FROM employee  // outer query
    -> WHERE birth_date < (
    ->                  SELECT birth_date FROM employee WHERE id =14 // subquery
    -> );
+----+--------+------------+
| id | name   | birth_date |
+----+--------+------------+
|  1 | MESSI  | 1987-02-01 |
|  5 | DINGYO | 1990-11-05 |
|  6 | JULIA  | 1986-12-11 |
|  9 | HENRY  | 1982-05-20 |
| 10 | NICOLE | 1991-03-26 |
| 13 | JISUNG | 1989-07-07 |
+----+--------+------------+
6 rows in set (0.00 sec)

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

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

mysql> SELECT id,name,position FROM employee
-> WHERE (dept_id,sex) = (
-> SELECT dept_id, sex FROM employee WHERE id=1
-> );
+----+-------+----------+
| id | name | position |
+----+-------+----------+
| 1 | MESSI | DEV_BACK |
+----+-------+----------+
1 row in set (0.00 sec)

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

 SELECT proj_id FROM works_on WHERE empl_id=5;
+---------+
| proj_id |
+---------+
|    2001 |
|    2002 |
+---------+
2 rows in set (0.00 sec)
mysql> SELECT DISTINCT empl_id FROM works_on //id 5와 같이 2001,2002 모두 참여한 id의 중복 제거
    -> WHERE empl_id !=5 AND (proj_id= 2001 OR proj_id=2002); //id 5를 제외
    //(proj_id= 2001 OR proj_id=2002) 를 proj_id IN (2001, 2002)로 표현할 수 있다.
+---------+
| empl_id |
+---------+
|       1 |
|       9 |
|      13 |
|      10 |
|      11 |
|      14 |
+---------+
6 rows in set (0.00 sec)

한 문장으로 표현

mysql> 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
    -> );
+---------+
| empl_id |
+---------+
|       1 |
|       9 |
|      13 |
|      10 |
|      11 |
|      14 |
+---------+
6 rows in set (0.00 sec)

• x IN (x1, x2, x3…): x가 (x1, x2, x3,…) 중 하나와 같이 같다면 TRUE를 return한다.
• (x1, x2, x3…)는 명시적인 값들의 집합일 수 있고 subquery의 결과 (set or multiset)일 수 있다.
• x NOT IN (x1, x2, x3…): x가 (x1, x2, x3,…)의 모든 값과 값이 다르면 TRUE를 return한다.

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

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

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
    ->  );
+---------+
| empl_id |
+---------+
|       1 |
|       9 |
|      13 |
|      10 |
|      11 |
|      14 |
+---------+
6 rows in set (0.00 sec)

결과 값을 id와 name을 알 수 있는 employee에서 조건으로 조회할 수 있다.

mysql> SELECT id, name
    -> FROM 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
    ->                  )
    ->  );
+----+---------+
| id | name    |
+----+---------+
|  1 | MESSI   |
|  9 | HENRY   |
| 13 | JISUNG  |
| 10 | NICOLE  |
| 11 | SUZANNE |
| 14 | SAM     |
+----+---------+
6 rows in set (0.00 sec)

mysql> 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 DSTNCT_E
    -> WHERE id =DSTNCT_E.empl_id;
+----+---------+
| id | name    |
+----+---------+
|  1 | MESSI   |
|  9 | HENRY   |
| 13 | JISUNG  |
| 10 | NICOLE  |
| 11 | SUZANNE |
| 14 | SAM     |
+----+---------+
6 rows in set (0.00 sec)

FROM 위치에도 subquery가 들어 갈 수 있는데 위는 subquery를 가상의 테이블 DSTNCT_E를 생성하여 결과를 저장하여 FROM에 2개의 테이블을 명시 한 후 아래 WHERE로 조건(employee의 id와 가상의 테이블 DSTNCT_E의 empl_id의 값이 같다면)을 설정 후 id와 name를 조회 하였다.

ID가 7 또는 11인 임직원이 참여한 프로젝트의 ID와 이름을 알고 싶다.

mysql> SELECT id, name FROM project
    ->          WHERE id IN (
    ->          SELECT proj_id
    ->          FROM works_on
    ->          WHERE empl_id IN (7,11)
    -> );
+------+-----------------------------+
| id   | name                        |
+------+-----------------------------+
| 2002 | 확장성 있게 백엔드 리팩토링 |
| 2003 | 홈페이지 UI 개선            |
+------+-----------------------------+
2 rows in set (0.00 sec)

위와 같이 작성 할 수 있지만 EXISTS를 사용하여 아래와 같이 조회 할 수 있다.

mysql> 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,11)
    ->  );
+------+-----------------------------+
| id   | name                        |
+------+-----------------------------+
| 2002 | 확장성 있게 백엔드 리팩토링 |
| 2003 | 홈페이지 UI 개선            |
+------+-----------------------------+
2 rows in set (0.00 sec)

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

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

mysql> 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년대 이후 출생자가 속해있으면 제외 되도록 조건 작성
+------+-------------+
| id   | name        |
+------+-------------+
| 1004 | design      |
| 1001 | headquarter |
| 1002 | HR          |
| 1005 | product     |
+------+-------------+
4 rows in set (0.00 sec)

아래와 같이 NOT IN을 사용하여 동일하게 조회할 수 있다.

mysql> 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'
    -> );//2000년대 이후 출생자가 속해있지 않다면 조회 되도록 조건 작성
+------+-------------+
| id   | name        |
+------+-------------+
| 1004 | design      |
| 1001 | headquarter |
| 1002 | HR          |
| 1005 | product     |
+------+-------------+
4 rows in set (0.00 sec)

리더보다 높은 연봉을 받는 부서원을 가진 리더의 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
-> );
+----+-------+-----------+
| id | name | salary |
+----+-------+-----------+
| 1 | MESSI | 100000000 |
| 3 | JENNY | 50000000 |
+----+-------+-----------+
2 rows in set (0.00 sec)

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

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

mysql> SELECT E.id, E.name, E.salary,
    ->  (
    ->          SELECT max(salary)
    ->          FROM employee
    ->          WHERE dept_id = E.dept_id
    ->  )AS dept_max_salary//최고 연봉을 찾아서 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
    ->  );
+------+-------+-----------+-----------------+
| id   | name  | salary    | dept_max_salary |
+------+-------+-----------+-----------------+
|    1 | MESSI | 100000000 |       180000000 |
|    3 | JENNY |  50000000 |       180000000 |
+------+-------+-----------+-----------------+
2 rows in set (0.00 sec)

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

mysql> 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
    ->  ); // 조건에 하나도 해당 되지않는 값을 조회하여 출력 한다.
+----+---------+-----------+
| id | name    | position  |
+----+---------+-----------+
|  2 | JANE    | DSGN      |
|  3 | JENNY   | DEV_BACK  |
|  4 | BROWN   | CEO       |
|  5 | DINGYO  | CTO       |
|  6 | JULIA   | CFO       |
|  7 | MINA    | DSGN      |
| 10 | NICOLE  | DEV_FRONT |
| 11 | SUZANNE | PO        |
| 12 | CURRY   | PLN       |
| 14 | SAM     | DEV_INFRA |
+----+---------+-----------+
10 rows in set (0.00 sec)

v comparison_operator(비교 연산자) ALL (subquery): subquery가 반환한 결과들과 모두 v와의 비교 연산이 TRUE라면 TRUE를 반환 한다.