[ SELECT with subquery ]
- ID가 14인 임직원보다 생일이 빠른 임직원의 ID, 이름, 생일을 알고 싶다.
(ID가 14인 임직원을 찾는 쿼리)
SELECT birth_date FROM employee WHERE id = 14;
SELECT id, name, birth_date FROM employee
WHERE birth_date < 1992-08-04;
두 개의 쿼리를 나눠서 실행했는데 한번에 실행해보면,
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 는 () 안에 기술된다.
- ID가 1인 임직원과 같은 부서 같은 성별인 임직원들의 ID, 이름, 직군을 알고 싶다.
SELECT id, name, position FROM employee
WHERE (dept_id, sex) = (SELECT dept_id, sex FROM employee WHERE id = 1);
=> attribute가 여러개인 경우 괄호&콤마로 연결
- ID가 5인 임직원과 같은 프로젝트에 참여한 임직원들의 ID를 알고 싶다.
( ID가 5인 임직원의 프로젝트 )
SELECT proj_id FROM works_on WHERE empl_id = 5;
SELECT DISTINCT empl_id FROM works_on
WHERE empl_id !=5 AND (proj_id = 2001 OR proj_id = 2002);
* DISTINCT => 중복된 튜플 제거 (두개의 proj_id에 모두 해당되는 empl_id가 있는 경우 중복 발생)
SELECT DISTINCT empl_id FROM works_on
WHERE empl_id !=5 AND proj_id IN (2001, 2002);
=> OR 대신에 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);
* v IN (v1, v2, v3, ...) : v가 (v1, v2, v3, ...) 중에 하나와 값이 같다면 TRUE를 return한다.
=> (v1, v2, v3, ...) 는 명시적인 값들의 집합일 수도 있고 subquery의 결과 (set of multiset)일 수도 있다.
중복불허 중복허용
* v NOT IN (v1, v2, v3, ...) : v가 (v1, v2, v3, ...) 의 모든 값과 값이 다르다면 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
);
works_on 테이블에서는 이름을 알 수 없다.
지금까지 구한 empl_id를 통해 employee 테이블과 연결.
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
)
);
다른 형태로도 만들어보면 (FROM 뒤에 subquery)
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 DISTINCT_E
WHERE id = DISTINCT_E.empl_id;
=> 서브쿼리로 가상의 테이블을 만들고 ' DISTINCT_E ' 라는 이름을 붙여줌.
두개의 테이블(employee테이블, 가상테이블) 에서 id와 name을 조회.
id DISTINCT_E.empl_id
=> subquery는 WHERE 절 / FROM 절 둘다 사용 가능.
- ID가 7 혹은 12인 임직원이 참여한 프로젝트의 ID와 이름을 알고 싶다.
( ID가 7 혹은 12인 임직원이 참여한 프로젝트 )
SELECT proj_id FROM works_on WHERE empl_id IN (7, 12);
SELECT P.id, P.name
FROM project P --- AS 생략
WHERE EXISTS(
SELECT *
FROM works_on W
WHERE W.proj_id = P.id AND W.empl_id IN (7, 12)
);
=> 서브쿼리의 WHERE 조건에 만족하는 튜플이 하나라도 있다면(EXISTS) 반환.
=> subquery가 project P라는 바깥쪽 쿼리 테이블을 참조.
(EXIST 대신 IN을 사용한다면)
SELECT P.id, P.name
FROM project P
WHERE id IN (
SELECT W.proj_id
FROM works_on W
WHERE W.empl_id IN (7, 12)
);
* 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 D
WHERE NOT EXISTS (
SELECT *
FROM employee E
WHERE E.dept_id = D.id AND E.birth_date >= '2000-01-01'
);
(EXIST 대신 IN을 사용한다면)
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'
);
=> IN 과 EXIST , NOT IN 과 NOT EXIST 는 대부분의 경우는 서로 바꿔가면서 사용 가능.
성능의 차이가 있지만 RDBMS의 종류와 버전에 따라 다르며,
최근 버전은 많은 개선이 이루어져서 IN과 EXIST의 성능 차이가 거의 없음.
- 리더보다 높은 연봉을 받는 부서원을 가진 리더의 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
);
=> 서브쿼리로 해당 부서원(리더제외)들의 salary를 가져와
리더의 salary보다 하나라도 큰 salary가 있다면 반환.
=> 리더인 MESSI 와 JENNY의 부서에는 각 리더의 연봉보다 많이 받는 부서원이 있음.
* v comparison_operator ANY (subquery) : subquery 가 반환한 결과들 중에
단 하나라도 v와의 비교 연산이 TRUE라면 TURE를 반환한다.
* SOME도 ANY와 같은 역할을 한다.
- 리더보다 높은 연봉을 받는 부서원을 가진 리더의 ID와 이름과 연봉을 알고 싶다.
+ 해당 부서의 최고 연봉도 알고 싶다.
SELECT E.id, E.name, E.salary,
( --- 추가된부분
SELECT max(salary)
FROM employee
WHERE dept_id = E.dept_id __ 해당 리더가 속한 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
);
=> FROM , WHERE 절은 변화 없음.
=> dept_max_salary 라는 새로운 attribute 생성.
- ID가 13인 임직원과 한번도 같은 프로젝트에 참여하지 못한 임직원들의 ID, 이름, 직군을 알고 싶다.
(ID가 13인 임직원이 참여한 프로젝트)
SELECT proj_id FROM works_on WHERE empl_id = 13;
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) : subquery 가 반환한 결과들과
v와의 비교 연산이 모두 TRUE라면 TRUE를 반환한다.
https://www.youtube.com/watch?v=lwmwlA2WhFc&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=6
'Database > MySQL' 카테고리의 다른 글
| [데이터베이스(강의)] 8. SQL로 데이터 조회 (join) (0) | 2024.02.05 |
|---|---|
| [데이터베이스(강의)] 7. SQL로 데이터 조회 (NULL, three-valued logic) (0) | 2024.02.05 |
| [데이터베이스(강의)] 5. SQL로 데이터 조회 (Select) (0) | 2024.02.01 |
| [데이터베이스(강의)] 4. SQL (Insert / Update / Delete) (0) | 2024.01.30 |
| [데이터베이스(강의)] 3. SQL 기본 개념 및 SQL로 데이터베이스 정의 (0) | 2024.01.25 |
