서브쿼리

서브쿼리란

하나의 SQL문안에 포함 되있는 다른 SQL문을 말한다 메인쿼리 안쪽에 있어서 서브쿼리라고 한다

서브쿼리 특징

• 서브쿼리는 반드시 괄호로 감싸서 사용해야함
• 서브쿼리는 단이랭 다중행 비교연산자와 사용해야함
• 중첩 서브쿼리 및 스칼라 서브쿼리는 ORDER BY를 사용할수 없다
• 연관서브쿼리는 메인 -> 서브 순서로 실행 비연관 서브쿼리는 서브-> 메인순으로 실행됨
• 조인은 1:M 관계의 테이블일 경우 M쪽으로 결과 집합이 만들어지지만 서브쿼리는 1쪽 결과 집합으로 만들수 있다

스칼라 서브쿼리 개념

결과가 단 한개의 값(1개의 컬럼,1개의 행)이 서브쿼리를 의미한다 SELECT에서 사용할수 있으며 결과는 반드시 1개의 행 또는 NULL이 나온다

예시)

SELECT A.이름,
--서브쿼리 사용--
(SELECT B.연락처 FROM 회원연락처 B
	WHERE A.회원ID = B.회원ID
	AND B.구분코드 = '휴대폰') AS 휴대폰번호
FROM 회원 A;

스칼라 서브쿼리 실행 과정

SELECT A.이름

• 출력될 행수 만큼 SELECT가 실행되고 서브쿼리도 똑같이 실행된다
• 회원 테이블에 총 3개의 행이 출력되기때문에 서브쿼리도 3번 출력된다
• 서브쿼리안에 메인쿼리의 컬럼(A.회원ID)가 있으면 메인쿼리에서 서브쿼리 순서로 실행되고 이것을 연관서브쿼리라고 한다

A.회원ID = 'A0001'의 경우

(SELECT B.연락처 FROM 회원연락처 B WHERE 'A0001' = B.회원ID AND B.구분코드 = '휴대폰')의 대상을 조회해서 결과를 출력함

결과 010 -111-1111

A.회원ID = 'A0002'의 경우

(SELECT B.연락처 FROM 회원연락처 B WHERE 'A0002' = B.회원ID AND B.구분코드 = '휴대폰')의 대상을 조회했을때 결과가 없기때문에 NULL이 출력됨

결과 : NULL

스칼라 서브쿼리 주의할점

• 서브쿼리는 컬럼 1개만 조회해야한다
• 서브쿼리 결과는 1행만 반환해야한다
• 결과가 없으면 NULL을 반환한다
• ORDER BY 사용금지

인라인뷰 개념

인라인 뷰는 FROM절에 서브쿼리를 작성해서 결과를 테이블 처럼 사용하는것을 말한다  복잡한 조회를 유연하게 처리할수 있다

특징

• 여러행,여러컬럼을 반환할수 있고 스칼라 서브쿼리와 구분된다
• 서브쿼리에서 메인쿼리의 컬럼을 직접 참조할수 없다 (오라클 12c이상은 LATERAL을 사용해서 참조가능함)
• 인라인뷰로 사용된 서브쿼리는 독립적으로 실행가능하다
• 별칭을 지정해서 메인쿼리에서 참조할수 있다

예시) 부서별로 평균 급여를 구하고 평균급여보다 많이 받는 직원을 추출해라

SELECT A.*
FROM EMPLOYEE A
	--인라인 뷰 서브쿼리 사용--
	,(SELECT DEPT, AVG(SALARY) AS AVG_SALARY --별칭 지정--
    	FROM EMPLOYEE
       	GROUP BY DEPT) B
 WHERE A.DEPT=B.DEPT
   AND A.SALARY > AVG_SALARY; --지정한 별칭사용--

실행순서

1. 독립실행 할수 있어서 먼저 해석해서 가상 테이블 그리기

SELECT DEPT, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEE GROUP BY DEPT 

2. 메인테이블 EMPLOYEE A 랑 가상테이블 B를 조인한다

FROM EMPLOYEE A, (인라인 뷰)B * 카티션 곱 상태

카티션 곱이란 두 테이블의 모든 행을 조합하는것이다

3. 조건에 일치하는 대상만 필터링

WHERE A.DEPT=B.DEPT  AND A.SALARY > AVG_SALARY;

WHERE에서 단일 행 서브쿼리 사용

스칼라 서브쿼리 와 단일행 서브쿼리의 차이점

• 스칼라 서브쿼리 : 반드시 1행 1열(단일 값)
• 단일행 서브쿼리 : 1행만 반환되면 컬럼수는 여러개여도 상관없음

단일행 서브쿼리란 실행결과로 출력되는 행이 1개 이하인 서브쿼리다

단일행 서브쿼리 + 비연관 서브쿼리

예시)

SELECT *
	FROM EMPLOYEE
  WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE);

서브쿼리에 메인쿼리의 컬럼이 없으면 비연관 서브쿼리라고 하고 먼저 실행가능하다

결과 AVG(SALARY) 4250

단일행 연산자

비교 행의 개수가 1행이거나 NULL일때 사용가능함 일반적인 비교연산자 (>, <=, = 등) 단일행 연산자에 해당한다

단일행 연산자 사용시 2행 이상의 다중행이면 오류난다

단일행 서브쿼리 + 연관 서브쿼리

메인쿼리 컬럼이 서브쿼리 내부에 사용되고 있어 메인쿼리의 각 행을 기준으로 서브쿼리가 반복실행된다

실행순서는 메인쿼리 1건 실행 -> 1건에 대한 서브쿼리 실행 순서다

예시)

SELECT *
	FROM EMPLOYEE A
  WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE WHERE A.DEPT= DEPT);

현재 서브쿼리에 A.DEPT라는 메인쿼리의 컬럼이 있기때문에 서브쿼리를 독립적으로 실행할수 없다

실행순서

1. 메인쿼리 첫번째 행 먼저 실행

SELECT AVG(SALARY) FROM EMPLOYEE WHERE 인사팀= DEPT의 실행결과는 3900이다

 WHERE SALARY >=3900 인데 첫번째 행이 4000이다 연봉이 3900보다 크거나 같아야 하므로 참이되서 출력된다

2.메인쿼리 두번째 행 실행

SELECT AVG(SALARY) FROM EMPLOYEE WHERE 개발팀= DEPT의 실행결과는 4600이다

WHERE SALARY >=4600 인데 두번째 행이 4500이다 연봉이 4600보다 크거나 같아야 하므로 거짓이되서 출력되지 않는다

이런식으로 메인쿼리의 모든행을 실행한다 

WHERE 절에서 다중행 서브쿼리 사용하기

다중행 서브쿼리 개념과 다중행 연산자 종류

다중행 서브쿼리는 실행결과 행이 1개 이상인 서브쿼리를 의미한다 따라서 2개 이상이면 단일행 연산자를 사용할수 없고 다중행 연산자를 사용해야한다

다중행 연산자

• IN -> 다중행 서브쿼리 결과에 하나라도 일치하면 참이다
• ANY -> 다중행 서브쿼리 결과중 하나라도 조건을 만족하면 참이다
• ALL -> 다중행 서브쿼리 결과중 모든 조건을 만족해야한다

EXISTS

서브쿼리의 결과가 참이면 대상을 반환한다 존재여부만 판단하기 때문에 SELECT에 큰 의미없다 대표적 연관 서브쿼리다

WHERE EXISTS(SELECT 1 FROM 테이블 WHERE 메인쿼리 컬럼 = 컬럼);

WHERE 메인쿼리컬럼 = 컬럼 조건으로 일치여부를 확인한다

SELECT는 결과 유무만 판단하기 때문에 임의값을 넣어도 되고 비워두면 문법오류가 생긴다

비교할 컬럼이 없기때문에 WHERE에서 컬럼을 적지않고 바로 작성한다

예시)

SELECT *
FROM EMPLOYEE A
WHERE EXISTS(SELECT 1 FROM BOUNS WHERE A. EMP_ID = B.EMP_ID);

실행순서

메인 -> 서브 순서로 진행

1. 메인쿼리 첫번째 행 실행

SELECT 1 FROM BOUNS WHERE 'E001' = B.EMP_ID

EMPLOYEE테이블 첫번째 행 실행시 하나라도 존재한다면 첫번째 행을 출력한다

2.메인쿼리 두번째 행 실행

SELECT 1 FROM BOUNS WHERE 'E002' = B.EMP_ID

EMPLOYEE테이블 두번째 행 실행시 서브쿼리에 맞는 데이터가 없으므로 두번째 행을 출력하지 않는다

이런식으로 존재 여부만 확인해서 출력한다 

NOT EXISTS

NOT EXISTS는 EXISTS와 원리는 같고 조건에 일치하는것이 하나라도 있으면 대상이 되는행을 출력하지 않는다

SELECT *
FROM EMPLOYEE A
WHERE NOT EXISTS(SELECT 1 FROM BOUNS B WHERE A. EMP_ID = B.EMP_ID);

실행순서

1.메인쿼리 첫번째 행 실행

SELECT 1 FROM BOUNS B WHERE 'E001' = B.EMP_ID

EMPLOYEE테이블 첫번째 행 실행시 하나라도 존재하기 때문에 첫번째 행을 출력하지 않는다

2.메인쿼리 첫번째 행 실행

SELECT 1 FROM BOUNS B WHERE 'E002' = B.EMP_ID

EMPLOYEE테이블 두번째 행 실행시 아예 존재하지 않기 때문에 두번째 행을 출력한다

WHERE절에서 다중 컬럼 서브쿼리 사용해보기

다중 컬럼 서브쿼리는 결과가 여러칼럼으로 반환될때 사용하며 IN연산자를 사용한다

WHERE(컬럼1= 값1 AND 컬럼2= 값2)
OR(컬럼1 = 값3 AND 컬럼2= 값4)

괄호안은 AND로 적고 각 괄호 간은 OR로 적는다

예시)

SELECT *
FROM EMPLOYEE
WHERE(DEPT, POSITION) IN (SELECT DEPT,POSITION
			FROM PROMOTION_TARGET);

이 비연관 서브쿼리를 풀이하면

SELECT *
FROM EMPLOYEE
WHERE(DEPT, POSITION) IN (('인사팀','대리'),('영업팀', '사원'));

으로 풀이할수 있는데 DEPT가 인사팀이고 POSITION이 대리인 행 또는 DEPT가 영업팀이고 POSITION이 사원인 행을 반환한다