서브쿼리란?
- 두 개의 쿼리를 결합하여 하나의 문장으로 표현하는 것
- 서브 쿼리는 메인 쿼리 내에서 리터럴을 대신하는 것
->사용자가 직접 쿼리를 두 번 실행하는 것과 같이 대체가 가능하지만
서브쿼리가 성능과 비용이 월등히 우수함
메인 쿼리(MAIN QUERY)
: 외부 질의
서브 쿼리(SUB QUERY)
: 내부 질의
서브 쿼리에 의해 검색된 결과값이 MAIN QUERY 문에 필요한 경우에 사용.
FROM절에 쓰이는 서브쿼리는 인라인 뷰(INLINE VIEW) 라고 부른다.
단일 행 서브쿼리
: 서브 쿼리가 하나의 컬럼에서 하나의 행을 검색 (' = ' 조건문에서 자주 사용)
다중 행 서브쿼리
: 서브 쿼리가 하나의 컬럼에서 여러개의 행을 검색 (IN 연산자에 사용할 수 있음)
다중 열 서브쿼리
: 서브 쿼리가 여러개의 컬럼을 검색
1. 단일 행 서브쿼리
SELECT (DISTINCT|ALL] 컬럼, ...
FROM 테이블
WHERE 컬럼 단일행 연산자 (SELECT 문장 : SUB QUERY)
- 단일행 연산자가 사용되므로 반드시 서브쿼리의 결과값은 한 개만 검색된다.
- 서브쿼리는 반드시 괄호로 묶는다.
- 서브쿼리는 메인쿼리 실행 전에 실행됨
- 서브쿼리의 검색된 결과값은 메인 쿼리에 사용
- 단일행 연산자 오른쪽에 기술 (<= , < , > , >=, !=, =) (+ IN)
- WHERE절에 기술된 열의 숫자와 타입은 SELECT절과 1:1 대응관계가 되어야 함. (데이터타입이 같아야 함)
예측하기 어려운 단일행 서브 쿼리를 수정하는 방법
- ' = '연산자는 'IN' 연산자로 바꾼다.
- 부등호 <, >, <=, >=는 ANY(하나라도 해당), ALL(모두 해당) 연산자를 추가한다.
- MAX(), MIN() 같은 그룹함수를 사용한다.
EXERCISE 1
남궁연호보다 급여를 많이 받는 사원을 검색
SELECT A.ENO 사번
,A.ENAME 이름
,A.SAL 급여
,B.ENO 사번
,B.ENAME 이름
,B.SAL 급여
FROM EMP A
INNER JOIN EMP B ON A.SAL > B.SAL
WHERE B.ENAME = '남궁연호'
ORDER BY A.SAL;
--서브쿼리 사용
SELECT *
FROM EMP A
WHERE A.SAL > (SELECT SAL
FROM EMP B
WHERE B.ENAME = '남궁연호');EXERCISE 2
김선유 보다 급여를 많이 받는 사원을 검색
SELECT *
FROM EMP A
WHERE A.SAL > ANY (SELECT SAL
FROM EMP B
WHERE B.ENAME = '김선유');
--서브쿼리 사용
SELECT A.*, B.ENO 사번, B.SAL 김선유_급여
FROM EMP A
INNER JOIN EMP B ON A.SAL > B.SAL
WHERE B.ENAME = '김선유'
AND A.ENO = '0301'
ORDER BY B.ENO, A.SAL;EXERCISE3
문시현과 부서가 다르고 동일한 업무를 하는 사원의 정보
SELECT *
FROM EMP A
WHERE A.DNO != (SELECT B.DNO FROM EMP B WHERE B.ENAME = '문시현')
AND A.JOB = (SELECT C.JOB FROM EMP C WHERE C.ENAME = '문시현');
--서브쿼리 사용
SELECT * --B 테이블의 컬럼은 사용 불가능. B테이블은 서브쿼리 괄호() 안에서만 사용
FROM EMP A
WHERE EXISTS (SELECT 1 --EXISTS() : BOOLEAN 값으로 데이터가 있는지 없는지만 판단.
FROM EMP B
WHERE B.ENAME = '문시현'
AND B.DNO != A.DNO
AND B.JOB = A.JOB);EXERCISE 4
부산에서 근무하는 사원의 정보
SELECT *
FROM EMP A
WHERE A.DNO = (SELECT B.DNO
FROM DEPT B
WHERE LOC = '부산');1. 김혁윤보다 평점이 우수하고 전공이 같은 학생의 학번과 이름
SELECT A.SNO, A.SNAME, A.MAJOR, A.AVR
FROM STUDENT A
WHERE A.AVR > (SELECT B.AVR
FROM STUDENT B
WHERE A.MAJOR = B.MAJOR
AND B.SNAME = '김혁윤')
ORDER BY A.AVR;위 쿼리를 EXISTS 를 사용하면 더 고급스러운 쿼리문이 된다.
SELECT A.SNO, A.SNAME, A.MAJOR, A.AVR
FROM STUDENT A
WHERE EXISTS (SELECT 1
FROM STUDENT B
WHERE A.MAJOR = B.MAJOR
AND A.AVR > B.AVR
AND B.SNAME ='김혁윤')
ORDER BY A.AVR; 2. 권현과 동일한 학년 학생 중에 평점이 강은혜와 동일한 학생
SELECT *
FROM STUDENT A
WHERE EXISTS (SELECT 1
FROM STUDENT B, STUDENT C --B:권현과 동일학년 / C:강은혜와 동일 평점
WHERE A.SYEAR = B.SYEAR
AND B.SNAME = '권현'
AND A.AVR = C.AVR
AND C.SNAME = '강은혜'); 3. 이학수학 과목과 동일한 학점수인 과목을 검색
SELECT *
FROM COURSE A
WHERE A.ST_NUM = (SELECT B.ST_NUM
FROM COURSE B
WHERE B.CNAME = '이학수학'
AND A.CNAME != B.CNAME);4. 타 학과에 송강 교수와 동일한 지위의 교수 명단을 검색
SELECT *
FROM PROFESSOR A
WHERE EXISTS (SELECT 1
FROM PROFESSOR B
WHERE A.ORDERS = B.ORDERS
AND A.SECTION != B.SECTION
AND B.PNAME = '송강');5. 제갈민 보다 나중에 부임한 교수의 명단
SELECT *
FROM PROFESSOR A
WHERE A.HIREDATE > (SELECT B.HDATE
FROM EMP B
WHERE B.ENAME = '제갈민');6. 강태용보다 일반화학 과목의 점수가 더 낮은 학생의 명단을 학점과 검색
SELECT A.SNO, A.SNAME, C.CNAME, B.RESULT
FROM STUDENT A, SCORE B, COURSE C
WHERE EXISTS (SELECT 1
FROM STUDENT A2, SCORE B2, COURSE C2 --강태용의 일반화학 점수 확인 테이블
WHERE A.SNO = B.SNO
AND A2.SNO = B2.SNO
AND B.CNO = C.CNO
AND B2.CNO = C2.CNO
--------------------- ▲조인조건
AND A2.SNAME = '강태용'
AND C.CNAME = C2.CNAME
AND C2.CNAME = '일반화학'
AND B.RESULT < B2.RESULT)
ORDER BY B.RESULT;2. 다중 열 서브쿼리
서브쿼리의 결과값이 두 개 이상의 컬럼을 반환하는 서브쿼리.
다중 열 서브쿼리의 비교 방식
- NON-PAIRWISE 비교
- PAIRWISE 비교
예제1) 'FORD', 'BLAKE' 사원과 같은 부서에 근무하면서 매니저(mgr)가 같은 사원의 이름과 매니저사원번호, 부서번호를 조회.
SELECT ename, mgr, deptno
FROM emp
WHERE mgr IN ( SELECT mgr FROM emp
WHERE ename IN ('FORD', 'BLAKE'))
AND deptno IN ( SELECT deptno FROM emp
WHERE ename IN ('FORD', 'BLAKE'))
AND ename NOT IN ('FORD', 'BLAKE');예제2) 예제1과 결과 비교
SELECT ename, mgr, deptno
FROM emp
WHERE (mgr, deptno) IN ( SELECT mgr , deptno FROM emp
WHERE ename IN ('FORD', 'BLAKE'))
AND ename NOT IN ('FORD', 'BLAKE');⭐⭐예제 1의 문제 의도는 'FORD', 'BLAKE' 사원과 부서도 같고, 매니저도 같은(교집합) 사원을 조회하는 것인데
1번의 풀이는
FORD, BLAKE 사원과 부서가 같은 사원과, 매니저가 같은 사원을 모두(합집합) 조회하게 된다.
따라서 예제 2번과 같이 두 조건을 따로 조회하지 말고 쌍(PAIR)으로(=멀티컬럼) 조회해야한다.
예제 3) 선수테이블에서 각 팀별로 키가 가장 작은 선수의 팀번호, 키를 조회하자.
---- 틀린 답
SELECT TEAM_ID, MIN(HEIGHT)
FROM PLAYER
GROUP BY TEAM_ID
ORDER BY 1;
-->> 중복값이 모두 제거되어 '선수'를 조회하는것이 아니라,
-- 팀별 가장 작은 '키'를 조회하게 됨.
---- 맞는 답
SELECT team_id AS 팀id, PLAYER_NAME, height AS 키
FROM player
WHERE (team_id,height) IN (SELECT team_id, MIN(height)
FROM player
GROUP BY team_id)
ORDER BY 1;
내가 보려고 쓰는 블로그