오래 못 할 짓 하지 않기
[ 데이터베이스 ] 5. Subquery 본문
Subquery
: 윙의윙 느낌임
예시를 하나 하고 설명해주겠음
(저번에 만든 table에 이어서 만든다)
===============문제 1) =============
id=14인 직원의 생일보다 빠른 직원들의 [ id,이름,생일 ] 을 알고자 한다.
[ 1번 접근법 ]
● 우선 id=14인 직원의 생일을 구한다.
→ select birth_date from employee where id=14;
(결과) 1992-08-04임.
→ select id,name,birth_date from employee where birth_date < '1992-08-04';
이 두 개의 쿼리를 하나의 쿼리로 바꿀 방법이 있다
[ 2번 접근법 ]
위에서 첫 번째 쿼리의 값이 '1992-08-04' 이므로, 그 값을 where 뒤에 넣을 수 있다.
→ select id,name,birth_date from employee
where birth_date < (
select birth_date from employee where id=14
) ;
이처럼 쿼리 안에 쿼리가 있을 수가 있는데 이럴 때
안에 들어가는 쿼리 = Subquery or Inner query
겉에 감싸는 쿼리 = Outer query
SUBQUERY 의 특징 1
subquery는 ( ) 안에 있어야 한다.
===============문제 2) =============
ID= 1인 직원과 같은 부서 and 같은 성별인 직원들의 [ ID , 이름 , 직군 ]을 알고자 한다.
SELECT id, name, position from employee
where (sex and , dept_id) == ( SELECT name,dept_id from emplyee
where id=1
);
(빨간줄은 내가 틀린 거.. and가 아니라 ,로 가야 함)
SUBQUERY 의 특징 2
subquery는 1개 이상의 attribute 값을 return 할 수 있고,
1개 이상의 attribute와 where에서 비교할 수 있다.
===============문제 3) =============
ID= 5인 직원과 같은 프로젝트에 참여한 직원들의 ID 를 알고자 한다.
[접근법 1]
select proj_id from works_on where empl_id=5;
결과 : 2001 , 2002
select distinct empl_id from works_on
where empl_id !=5 And (proj_id = 2001 OR proj_id=2002);
해설 :
1) distinct 쓰는 이유 → 프로젝트 2001, 2002 둘 다 같이 참여한 사람은 중복으로 뜨게 될 거임.
중복 제거를 위해 사용한다.
2) empl_id !=5 하는 이유 → 5와 같이 했던 애를 구하는데 5가 또 나올테니까
꿀팁
OR 이런 거 쓰기 번거롭고, 항목이 많아지면 더 귀찮아 지니
(proj_id = 2001 OR proj_id=2002)
= proj_id IN(2001,2002);
= proj_id 가 2001이거나 2002이거나!
위에서 나온 쿼리 두 개를 합치면?
▼
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
);
(내가 한 거)
Select id from employee where proj_id ==
( Select proj_id from works_on where empl_id=5);
SUBQUERY - 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
);
주황색 attribute 들은 밑줄 그어져 있는 works_on table을 참조하고
초록색 arrtibute 들은 빨간색 works_on table을 참조한다.
[ 추가 ]
https://youtu.be/lwmwlA2WhFc?si=wtH7PIU3lEVFiISY&t=704
여기에서 직원들의 ID뿐만 아니라 이름도 알고 싶다면?
(subquery 가 where에)
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
)
);
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;
해석 :
1) From절에 employee table 하나,
subquery의 결과로 만들어진 DSTNCT_E 라는 이름의 가상 table .
이 두 개의 테이블을 명시한다.
2) Where 절에서는 이미 위에 있는 두 개의 table을 Join으로 묶어준 상태이다.
employee에 있는 id 와 subquery로 만들어진 DSTNCT_E에 있는 empl_id 를 보고
이 두 개가 같을 때, 그 데이터의 id와 name을 가져와라
Exists
: 뒤에 오는 subquery 조건에 만족하는 게 하나라도 있다면 TRUE 반환
※ In과의 차이점
1) 무얼 return 하냐
- In : 값을 return
- Exists : T/F 를 return
2) 어떤 형식으로 쓰이냐
- In : 찾으려는 특정 값을 입력한다
- Exists : Subquery의 결과로 나오는 값들이 Outer query 범위에 있는지
===============문제 4) =============
ID가 7 혹은 12인 직원이 참여한 프로젝트의 ID와 이름을 구하기
Select P.id, P.name from project P // project 테이블의 별칭을 P로 만들어서 사용
where Exists // 다음 subquery의 결과로 나오는 값들이 P에 있으면 그거 select
( select * from works_on W // works_on 테이블의 별칭을 W로 만들었음
where W.proj_id = P.id and W.empl_id IN(7,12) // 직원들이 갖고있는 Project id가 project 테이블에 있어야 하고,
// id가 7번인 사람과 12번인 사람이 한 프로젝트이어야 한다.
);
이거 Exists를 In으로 바꿀 수 있음
바꿀 땐
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)
);
1) Exist → IN
2) Subquery의 Where 에서 outer query 테이블을 갖는 애[ = 초록색 ] 를 In으로 데려오고
3) Subquery의 Where에서 Subquery 테이블의 요소를 갖는 애[ = 주황색 ]를 Select로 뺀다
[ 결과 ]
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)
);
===============문제 5) =============
2000년생이 없는 부서의 ID와 이름 구하기
select D.id, D.name from Department as D
Where Not Exists (
Select * from employee
where dept_id = D.id and birth_date >= '2000-01-01'
);
틀린 거: dept_id = D.id도 추가해야함 이거 안 넣었음
select D.id, D.name from Department as D
Where id Not In (
Select dept_id from employee
where birth_date >= '2000-01-01'
);
ANY
: @ ( 비교기 ) ANY (subquery) // ex) salary < ANY ( Subquery )
Subquery가 반환한 결과 중에서 단 하나라도 V와 연산했을 때 True면 True를 반환
@ < ANY (subquery) 해석 : @와 Subquery 결과들을 비교했을 때 < 가 성립하는 게 하나라도 있으면 true
===============문제 6) =============
부서의 리더보다 높은 연봉을 받는 부서원을 가진 리더의 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
);
여기에서 그럼 가장 연봉이 높은 사람을 찾으려고 한다면?
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 안에 subquery를 넣을 수 있다.
해당 subqeury의 결과가 Dept_max_salary라는 attribute로 들어가서 함께 출력된다
max(salary)는 salary attribute에서 가장 큰 값을 찾아준다.
===============문제 6) =============
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번이 했던 project의 id들 ) 와 모두 같지 않으면! select된다!
(출처)
유튜브 쉬운코드
'3학년 1학기 > 데이터베이스(DB)' 카테고리의 다른 글
| [ 데이터베이스 ] 7. Join (좀 어려움 ㅠ) (0) | 2024.01.02 |
|---|---|
| [ 데이터베이스 ] 6. NULL / 3 valued logic (0) | 2023.12.31 |
| [ 데이터베이스 ] 4. SQL 활용 ( 추가/수정/삭제 ) (0) | 2023.12.30 |
| [ 데이터베이스 ] 3. SQL (0) | 2023.12.28 |
| [ 데이터베이스 ] 2. Relational Database (0) | 2023.12.26 |
