subquery
SQL 활용에 있어서 매우 중요
서브쿼리 : 쿼리안에서 실행되는 쿼리를 의미한다.
1. 서브쿼리 분류 - 서브쿼리가 사용되는 위치에 따른분류
1-1 SELECT : 스칼라 서브쿼리
1-2 FROM 인라인 뷰 (INLINE - VIEW)
1-3 WHERE : 서브쿼리 ( SUB QUERY )
2. 서브쿼리 분류 : 서브쿼리가 반환하는 행, 컬럼의 개수에 따른 분류
(행 1, 행 여러개 ) , ( 컬럼 1, 컬럼 여러개 )
(행, 컬럼 ) : 4가지
2-1 . 단일행 , 단일 컬럼
2-2 . 단일행 , 복수컬럼 ==> X
2-3 . 복수행 , 단일 컬럼
2-4 . 복수행 , 복수 컬럼
3. 서브쿼리 분류 - 메인쿼리의 컬럼을 서브쿼리에서 사용여부에 따른 분류
3.1 상호 연관 서브 쿼리 ( corelated sub querry )
- 메인 쿼리의 컬럼을 서브쿼리에서 사용하는 경우
3-2 비상호 연관 서브 쿼리 ( non - corelated sub querry )
- 메인 쿼리의 컬럼을 서브 쿼리에서 사용하지 않는 경우
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
--> 스미스의 부서숫자를 구했어 DEPTNO 가 20이다.
스미스가 속한 부서에 속한 사원들은?
1. 스미스가 속한 부서번호 구하기
SELECT deptno
FROM emp
WHERE ename = 'smith '
2. 1번에서 구한 부서에 속해 있는 사원들 구하기
SELECT *
FROM emp
WHERE deptno = ' 20 '
이거를 한번에 할 수 있는게 서브 쿼리이다.
-> 서브쿼리를 이용하여 하나로 합칠 수 가 있다.
SELECT *
FROM emp
WHERE deptno = ( SELECT deptno
FROM emp
WHERE ename = 'SMITH' ) ;
서브쿼리를 사용할 때 주의 점
1. 연산자
2. 서브쿼리의 리턴 형태
서브쿼리가 한 개의 행 복수컬럼을 조회하고 단일컬럼과 비교하는 경우 -> 불가능
예 )
SELECT *
FROM emp
WHERE deptno = ( SELECT deptno , empno
FROM emp
WHERE enam = 'SMITH ' ) ;
-> 실행을 해보면 실행이 안된다.
서브쿼리가 여러개의 행, 단일 컬럼을 조회하는 경우
1. 사용되는 위치 : WHERE - 서브쿼리
2. 조회되는 행 , 컬럼의 개수 : 복수행, 단일컬럼
3. 메인쿼리의 컬럼을 서브쿼리에서 사용 유무 : 비상호 연관 서브쿼리
--> 밑에 있는 애 설명한것.
SELECT *
FROM emp
WHERE deptno = ( SELECT deptno
FROM emp
WHERE ename = 'SMITH'
OR ename = 'ALLEN' ) ;
deptno IN ( 20, 30 )
행이 여러개일때는 컬럼이 단일일 경우
SELECT *
FROM emp
WHERE deptno IN ( SELECT deptno
FROM emp
WHERE ename = 'SMITH'
OR ename = 'ALLEN ' ) ;
------------> 이거는 실행이 된다.
서브쿼리랑 IN 이랑 많이 쓰인다.
실습 SUB 1 .
SELECT *
FROM emp;
SELECT count(*)
FROM emp
WHERE sal > (SELECT AVG(sal)
FROM emp ) ;
실습 sub 2
SELECT *
FROM emp
WHERE sal > (SELECT AVG(sal)
FROM emp ) ;
서브쿼리
IN 연산자가 중요함
실습 sub 3
SELECT *
FROM EMP
1. SMITH 가 속한 부서
2. WARD 가 속한 부서
1.
SELECT deptno
FROM emp
WHERE ename = 'SMITH'
2.
SELECT deptno
FROM emp
WHERE ename = 'WARD'
SELECT *
FROM emp
WHERE deptno IN ( SELECT deptno
FROM emp
WHERE ename = 'WARD'
OR ename = 'SMITH' )
IN 연산자 쓰는 이유 ( = 이 아니라 ) 서브쿼리에서의 것이 여러개가 나오니까 IN 쓰는데
1개만 있어도 IN 을 쓸 수 있다.
복수행 연산자 : IN ( 중요) , ANY, ALL ( 빈도가 떨어진다 )
SELECT *
FROM emp --
WHERE sal < ANY( SELECT SAL
FROM emp
WHERE ename IN ( 'WARD' , 'SMITH' ) ) ;
SAL 컬럼의 값이 800 이나 1250 보다 작은 사원
-> sal 컬럼의 값이 1250 보다 작은 사원
SELECT *
FROM emp -- 800, 1250
WHERE SAL > ALL ( SELECT sal
FROM emp
WHERE ename IN ( 'WARD' , 'SMITH' ) ) ;
sal 컬럼의 값이 800 보다 크면서 1250보다 큰 사원
-> sal 컬럼의 값이 1250 보다 큰 사원
복습 :
NOT IN 연산자와 NULL
SELECT *
FROM emp
WHERE mgr NOT IN
NOT IN 연산자와 NULL
not in 연산자가
복습
SELECT *
FROM emp
WHERE mgr NOT IN (7698, 7839 ) ;
mgr != 7698 AND mgr != 7839 AND mgr != null ;
여기서 NOT IN 이라는 연산자는 위에 처럼 읽기 때문에 FALSE가 됨
NOT IN 연산자와 NULL
관리자가 아닌 사원의 정보를 조회
SELECT *
FROM emp
WHERE empno NOT IN ( SELECT mgr
FROM emp ) ;
* mgr 에 null이 하나가 있기 때문에.
pair wise 개념 : 순서쌍 , 두가지 조건을 동시에 만족시키는 데이터를 조회 할 떄 사용
AND 논리연산자와 겨로가 값이 다를 수 있다. (선생님 예시 참조
서브쿼리 : 복수행, 복수컬럼
SELECT *
FROM emp
WHERE ( mgr, deptno ) IN ( SELECT mgr, deptno
FROM emp
WHERE empno IN ( 7499, 7782)) ;
SELECT 절에서 사용이 되는
SCALAR SUBQUERY : SELECT 절에 기술된 서브쿼리
하나의 컬럼
**중요** 스칼라 서브쿼리는 하나의 행 , 하나의 컬럼을 조회하는 쿼리 이어야 한다.
SELECT dummy, (SELECT SYSDATE , ' TEST'
FROM dual )
--오라클 입장에서는 처리하는게 애매하다.
FROM dual ;
스칼라 서브 쿼리가 복수개의 행 ( 4개) , 단일 컬럼을 조회 -> 에러
SELECT empno, ename, deptno, (SELECT dname FROM dept)
FROM emp
emp 테이블과 스칼라 서브 쿼리를 이용하여 부서명 가져오기
기존 : emp 테이블과 dept 테이블을 조인하여 컬럼을 확장
조인을 안하고 가지고 오기
SELECT empno, ename, deptno,
( SELECT dname FROM dept WHERE deptno = emp.deptno )
FROM emp
-- deptno 는 가장 가까운애
메인 쿼리에 있는 애를 상호쿼리에 있는애에 가져다가 쓰는것.
-- 행마다 다른 값으로
SELECT dname
FROM dept
WHERE deptno = 20 ;
매번 20 숫자를
SELECT dname FROM dept WHERE deptno = deptno -> 항상 참이다.
상호 연관서브 쿼리 . 서브쿼리만 별도로 실행하는 것이 불가능하다.
비상호 연관서브 쿼리는 메인쿼리에 의존하지 않는다. 독단적으로 사용 가능
상호연관 서브 쿼리 : 메인 쿼리의 컬럼을 서브쿼리에서 사용한 서브쿼리
서브쿼리만 단독으로 실행하는 것이 불가능 하다.
메인쿼리와, 서브 쿼리의 실행 순서가 정해져 있다.
( 항상 메인쿼리가 먼저 실행된다 )
비상호 연관 서브 쿼리 : 메인 쿼리의 컬럼을 서브쿼리에서 사용하지 않은 서브쿼리
서브 쿼리만 단독으로 실행하는 것이 가능하다.
메인 쿼리와 서브 쿼리의 실행 순서가 정해져 있지 않다.
메인 -> 서브, 서브 -> 메인 둘다 가능
비상호 연관 쿼리
SELECT *
FROM dept
WHERE deptno IN ( SELECT deptno
FROM emp ) ;
dept 테이블을 먼저 읽을지 emp 가 먼저읽을지 알수가 없다. 실행계획을 봐야지 알수가 있다. -> 정해져 있는 것은 아니다.
-- 실습
본인 속한 부서의 급여 평균보다 높은 급여를 받는 사람들을 조회 .
SELECT *
FROM emp e
WHERE sal > ( SELECT AVG (sal)
FROM emp
WHERE deptno = e.deptno ) ;
* 상호연관 이다. 메인 커리에 있는 컬럼을 가져다 썼다.
서브쿼리자체만으로 실행이 불가능하다.
실습 sub 4
테스트용 데이터 추가
DESC dept;
INSERT INTO dept VALUES ( 99, 'ddit' , 'daejeon' ) ;
-> 한 행이 추가됬다.
SELECT *
FROM dept
SELECT *
FROM emp
1. emp 테이블로 등록된 사원들이 속한 부서 번호 확인
SELECT deptno
FROM dept
실습 sub 4
SELECT *
FROM dept
WHERE deptno NOT IN ( SELECT deptno
FROM emp ) ;
실습 sub 5
PID 가 100이랑 400
SELECT *
FROM cycle
SELECT *
FROM product
WHERE pid NOT IN ( SELECT pid
FROM CYCLE
WHERE cid = 1 )
SELECT *
FROM product
WHERE pnm NOT IN ( SELECT pnm
FROM product
WHERE pid IN ( 400, 100 ) )
SELECT *
FROM cycle
WHERE cid = 1
1번고객이 먹지 않는 제품 정보를 조회한다.
실습 SUB 6
PID 가 100인것
SELECT *
FROM cycle
WHERE cid = 1
AND pid = 100 ;
--> 여기가 시작
SELECT *
FROM cycle
WHERE cid = 1
AND pid IN (SELECT pid
FROM CYCLE
WHERE CID = 2 )
댓글
댓글 쓰기