SQL 조회 2

JEREGIM·2023년 4월 5일
db
0

데이터베이스

목록 보기
6/6

📌subquery

  • subquery란 SELECT, INSERT, UPDATE, DELETE에 포함된 query를 말한다.

  • outer query : subquery를 포함하는 query를 말한다.

  • subquery는 ()안에 기술된다.

| id가 14인 임직원보다 생일이 빠른 임직원의 id,이름,생일을 출력하라

mysql> SELECT birth_date FROM employee WHERE id = 14;
+------------+
| birth_date |
+------------+
| 1992-08-04 |
+------------+
1 row in set (0.04 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 |
+----+--------+------------+
  • 위의 두 쿼리를 한 문장으로 합치면 다음과 같다.
mysql> SELECT id, name, birth_date FROM employee
    -> WHERE birth_date < (SELECT birth_date FROM employee WHERE id = 14);
+----+--------+------------+
| 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 |
+----+--------+------------+

| 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  |
| 14 | SAM   | DEV_INFRA |
+----+-------+-----------+

IN

  • v IN (v1, v2, v3 ...) : v가 (v1, v2, v3 ...) 중 하나라도 값이 같다면 true를 리턴한다.

  • (v1, v2, v3 ...) 안에 subquery의 결과가 들어갈 수 있다.

  • v NOT IN (v1, v2, v3 ...) : v가 (v1, v2, v3 ...) 의 모든 값과 일치하지 않을 때 true를 리턴한다.

id가 5인 임직원과 같은 프로젝트에 참여한 임직원들의 id를 출력하라

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 |
|       3 |
|      10 |
|      13 |
|      14 |
+---------+

| id가 5인 임직원과 같은 프로젝트에 참여한 임직원들의 id, 이름을 출력하라
이름은 works_on 테이블만으로는 알 수 없다. employee 테이블에서 위에서 구한 id와 대응하는 이름들을 조회해야한다.

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  |
|  3 | JENNY  |
| 10 | NICOLE |
| 13 | JISUNG |
| 14 | SAM    |
+----+--------+

같은 조건을 반환하지만 subquery를 FROM 절에서도 쓸 수 있다.

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 E
    -> WHERE id = E.empl_id;
+----+--------+
| id | name   |
+----+--------+
|  1 | MESSI  |
|  3 | JENNY  |
| 10 | NICOLE |
| 13 | JISUNG |
| 14 | SAM    |
+----+--------+
  • subquery를 FROM에 적어서 가상의 테이블로 만들고 가상의 테이블의 별칭을 E라고 정해준다.
    그 후 WHERE 절에서 E.empl_id와 employ테이블의 id를 통해 join condition을 만들어준다.

EXISTS

  • EXISTS : subquery의 결과가 최소 하나의 row라도 있다면 true 반환

  • NOT EXISTS : subquery의 결과가 단 하나의 row도 없다면 true 반환

| id가 7 혹은 12인 임직원이 참여한 프로젝트의 id, 이름을 출력하라

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, 12)
    -> );
+------+------------------+
| id   | name             |
+------+------------------+
| 2003 | 홈페이지 UI 개선   |
+------+------------------+
  • EXISTS와 IN은 서로 바꿔가면서 쓸 수 있다. 위의 쿼리문을 IN으로 바꾸면 다음과 같다.
mysql> SELECT P.id, P.name
    -> FROM project P
    -> WHERE P.id IN (
    -> SELECT W.proj_id
    -> FROM works_on W
    -> WHERE W.empl_id IN (7, 12)
    -> );
+------+------------------+
| id   | name             |
+------+------------------+
| 2003 | 홈페이지 UI 개선   |
+------+------------------+

| 2000년대생이 없는 부서의 id와 이름을 출력하라
NOT EXISTS를 사용하면 다음과 같다.

mysql> 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');
+------+-------------+
| id   | name        |
+------+-------------+
| 1003 | development |
| 1001 | headquater  |
| 1002 | hr          |
| 1005 | product     |
+------+-------------+
  • NOT EXISTS 또한 NOT IN으로 바꿔줄 수 있다.
mysql> SELECT D.id, D.name
    -> FROM department D
    -> WHERE D.id NOT IN (
    -> SELECT E.dept_id
    -> FROM employee E
    -> WHERE E.birth_date >= '2000-01-01');
+------+-------------+
| id   | name        |
+------+-------------+
| 1003 | development |
| 1001 | headquater  |
| 1002 | hr          |
| 1005 | product     |
+------+-------------+

ANY

  • v 비교연산자 ANY (subquery) : subquery가 반환하는 결과들 중에서 단 하나라도 v와 비교 연산한 값이 true라면 true를 반환한다.

  • SOME도 ANY와 같은 역할을 한다.

| 리더보다 높은 연봉을 받는 부서원을 가진 리더의 id,이름,연봉을 출력하라

mysql> 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   |
+----+--------+----------+
|  3 | JENNY  | 50000000 |
| 13 | JISUNG | 90000000 |
+----+--------+----------+

  • ANY 다음에 오는 subquery의 결과와 E.salary를 비교한다. 비교연산자에 만족하는 결과값이 하나라도 있다면 true를 반환한다.

  • 위의 결과는 JENNY, JISUNG이 이끄는 부서에서 JENNY, JISUNG보다 연봉이 높은 부서원이 있다는 뜻이 된다. 그렇다면 그 부서원의 연봉이 얼마인지도 출력하려면 다음과 같다.

mysql> 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);
+------+--------+----------+-----------------+
| id   | name   | salary   | dept_max_salary |
+------+--------+----------+-----------------+
|    3 | JENNY  | 50000000 |       180000000 |
|   13 | JISUNG | 90000000 |       170000000 |
+------+--------+----------+-----------------+
  • select 절에도 subquery를 쓸 수 있다.

ALL

  • v 비교연산자 ALL (subquery) : subquery의 결과들과 v를 비교해서 모두 참이라면 true를 반환한다.

| 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 |
+----+---------+----------+
|  1 | MESSI   | DEV_BACK |
|  2 | JANE    | DSGN     |
|  3 | JENNY   | DEV_BACK |
|  6 | JULIA   | CFO      |
|  7 | MINA    | DSGN     |
|  9 | HENRY   | HR       |
| 11 | SUZANNE | PO       |
| 12 | CURRY   | PLN      |
+----+---------+----------+
profile
JEREGIM
이전 포스트

SQL 조회 1

0개의 댓글