서브쿼리
하나의 쿼리 안에 포함된 또 하나의 쿼리
그동안 배운쿼리가 메인 쿼리이고 메인쿼리가 서브쿼리를 포함하는 종속적인 관계
왜 사용하냐면, 복잡한 논리로 데이터를 추출하고 싶다면,
예를 들어 연봉이 얼마인 직원이 어떤 책을 자주 읽는지 궁금한데 그게 매년그런지 올해만 그런 지궁금 하고 조건문이 꼬이는 것을 방지함.
특징
알려지지 않은 기준을 이용한 검색에 유용
- 연봉이 상위 5% 인 직원(아직 누군지모름)
메인 쿼리가 실행되기 이전에 한 번만 실행
- select → (2) 서브쿼리 → (1) 메인쿼리 순으로 실행됨
한 문장에서 여러 번 사용 가능
- 서브쿼리의 서브쿼리 서브쿼리 실행가능 인셉션급
select * from employee
where 급여 > --연산자의 오른쪽에 사용
(select 급여 from employees where 이름='elice'); --서브쿼리()괄호
--사원 elice의 급여를 알지 못해도 검색가능
-- 알려지지 않은 elice 의 급여보다 높은거 추출가능함.주의사항
- 서브쿼리는 괄호와 함께 사용되어야 한다.
- 서브쿼리 안에서 order by 절은 사용할 수 없다.
- 연산자의 오른쪽에 사용되어야한다. 메인쿼리 < 서브쿼리
- 서브쿼리는 오로지 select 문으로 만 작성 가능
서브쿼리를 사용해야하는 조건이 있을 땐 --서브쿼리, --메인쿼리의 정의를 세워두고 시작하면 훨-씬 수월하다.
단일 행 서브쿼리 1
단일 행 서브쿼리(Single-Row Subquery)는 서브쿼리의 조회 결과가 1개의 행만 나오는 서브쿼리를 뜻합니다.
서브쿼리에서 나오는 결과가 하나이므로 메인쿼리와 서브쿼리 결과는 단일행 연산자를 사용하여 비교합니다.
지시사항
emp테이블을 조회해주세요.- 사원 번호가 7인 사원보다 나이가 어린 사원을 조회하는 쿼리를 작성하세요.
서브쿼리부터 작성
-- 서브쿼리 = 사원 번호가 7인 사원의 나이 = 생년월일
-- 메인쿼리 = 생년월일을 비교
select *
from emp
where birthdate >
(select birthdate from emp where empnum = 7);지시사항
emp테이블에서 부서번호가 40인 부서의 급여 평균보다 높게 받는 사원들의 모든 컬럼과 나이를 조회하는 쿼리를 작성해주세요.
SELECT * FROM emp;
-- 부서번호가 40인 부서의 급여 평균보다 높게 받는 사원들의 모든 컬럼과 나이를 조회하는 쿼리를 작성해주세요.
--서브쿼리 부서번호가 40인 부서의 급여 평균
-- 메인쿼리 : 40인 부서 급여 평균보다 높게 받는 사원의 컬럼 + 나이
(select avg(sal)
from emp
where deptno = 40);
select *,FLOOR( (CAST(REPLACE(CURRENT_DATE,'-','') AS UNSIGNED) -
CAST(REPLACE(birthdate,'-','') AS UNSIGNED)) / 10000 ) as age
--나이를 계산하는 조건
from emp
where sal >
(select avg(sal)
from emp
where deptno = 40);
반환에 따른 분류 다중행 서브쿼리 : 반에 키 180이상은 여럿이었다.
서브쿼리가 결과를 2개이상 반환하고 이결과를 메인쿼리로 전달하는 쿼리
단일행 - 엘리스의 급여 : 100 ( 1가지 값만 있음)
다중행 - 반에서 키가 180cm : 1~5 명 많이 나옴
공통점은 결과물을 메인쿼리로 전달함.
select * from employee
where 급여 in (
Select max(급여) from employee Group by 부서번호);
--부서번호 별로 급여가 제일 큰 사람
-- IN 다중행 연산자다중행 연산자
in : 하나라도 만족하면 반환
ANY : 하나라도 만족하면 반환 비교 연산가능 : 내부적으로 모두 or 연산을 함
ALL : 모두 만족하면 반환 비교 연산가능 : 내부적으로 모두 and 연산을 함
< any 최대값 \ > all 최대값
any 최소값 \ < all 최소값
1 in (1,2,3,4) → 참
10<any(1,2,3,4) = 10<4 최대값 → 거짓
99>all(99,100,101) = 99≥101 최대값 포함 → 거짓
지시사항
emp테이블에서 각 부서별 급여를 제일 많이 받는 사원들을 출력해주세요.
SELECT * FROM emp;
-- 각 부서별 급여를 제일 많이 받는 사원의 월급을 받는 사원들을 조회하는 쿼리를 작성해주세요.
-- 서브쿼리 : 부서별로 제일 많이 받는 급여
-- 메인쿼리 : 어떤사람인지 전체 내용 출력
(select deptno, max(sal)
from emp
group by deptno);
select *
from emp
where sal in --sal 이 누구든 in도 되고 =any도 됨
(select max(sal)
from emp
group by deptno);
하나라도? 혹은 전부? - ANY, ALL
ANY 는 만족하는 조건이 하나라도 있으면 참이 되는 것이고 ALL 은 모든 조건이 참이 되어야 만족합니다.
-- salaries 테이블에서 from_date가 2000-12-31 이전인 사람들의 급여 중 하나의 급여 보다 더 적은 급여를 받은 직원의 급여 정보를 모두 출력해보세요.
select * from salaries;
-- 서브쿼리 <any : 시작일이 '2000-12-31' 이전인 사람들의 급여
-- 메인쿼리 or: 더 적은 급여를 받는 사람
(select from_date, salary
from salaries
where from_date < '2000-12-31');
select *
from salaries
where salary <any
(select salary
from salaries
where from_date < '2000-12-31');
-- salaries 테이블에서 from_date가 2000-12-31 이전인 사람들의 급여 중 모든 급여보다 적은 급여를 받은 직원의 급여 정보를 모두 출력해보세요.
-- 서브쿼리 <all : 시작일이 '2000-12-31' 이전인 사람들의 급여
-- 메인쿼리 and: 더 적은 급여를 받는 사람
select *
from salaries
where salary <all
(select salary
from salaries
where from_date < '2000-12-31');
<any 는 56000보다 적은 직원 모두
<all 은 45200 보다 적은 직원 모두
위치에 따른 분류 : 스칼라 서브쿼리 select에서 사용
오로라 한 행만 반환하고
마치 JOIN을 사용한 것과 같은 결과를 나타낸다.
왜? 계산속도를 빠르게할려고!
select students.name, (
select math
from middle_test as m --m으로 별명 지어주기
where m.student_id = students.student_id
) AS middle_avg -- 위의 스칼라 서브쿼리가 as로 됨
from students;
미션
지시사항
middle_test테이블에서 10504학번인 경민이보다 중간고사 수학점수를 높거나 같게 받은 학생들을 조회해 주세요.middle_test테이블과students테이블을 join하여 진행하셔야 합니다.
-- 1. 경민이보다 중간고사 수학점수를 높거나 같게 받은 학생들을 조회해 주세요.
select *
from middle_test
left join students
on students.student_id = middle_test.student_id
where middle_test.math >=
(select math
from middle_test
where middle_test.student_id = 10504);
-- join 과 함께 서브쿼리를 사용할 수 있다는 점 확인
-- 일단 left join 이나 inner join으로 두 테이블을 묶는다.(아마도 데이터가 어긋나는 건 없는듯)
--그리고 where로 조건을 걸어주는데 여기서 서브쿼리를 넣어준다. 비교대상은 중간고사 수학점수
-- 경민이 보다 낮아야하니 경민이를 middletest 테이블에서 찾아준다. 거기서 math 칼럼을 찾고 비교한다.