4일차 : 서브쿼리(Subquery), SET연산자(집합연산자)

오늘 중요한 부분 : 서브쿼리(Subquery) 꼭 알아야하고 인서트, 업데이트, 딜리트 문법 알아야 한다

서브쿼리(Subquery)

SQL 명령에 포함되어 실행되는 SELECT 명령,
여러 번의 SQL 명령으로 얻을 수 있는 결과를 하나의 SQL 명령으로 얻기 위해 사용하는 기능
SELECT 명령(MAINQUERY)에 포함되어 실행되는 SELECT 명령(SUBQUERY)

• 서브쿼리의 명령은 메인쿼리의 FROM,WHERE, HAVING 구문에서 ()안에 작성
• 서브쿼리 명령이 먼저 실행된 후 메인쿼리 명령을 실행

서브쿼리 사용

서브쿼리를 이용하면 하나의 SELECT 명령을 사용하여 원하는 결과 검색할 수 있다. SELECT 문을 두번 사용하지 않아도 됨!

--WHERE 구문에서 조건식의 비교값 대신 서브쿼리의 검색 결과값을 사용하여 검색
--조건식의 비교대상과 같은 자료형의 값이 하나만 검색되도록 서브쿼리 작성 - 단일행(SINGLE ROW)의 단일 컬럼값(SINGLE COLUMN)으로 비교

--EMP 테이블에서 사원이름이 SCOTT인 사원보다 많은 급여를 받는 사원의 사원번호,사원이름,급여 검색
--SELECT문 2번 사용
SELECT SAL FROM EMP WHERE ENAME='SCOTT'; --검색결과 : 3000
SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL>=3000;

--같은 내용 검색, 서브쿼리 사용
SELECT EMPNO,ENAME,SAL FROM EMP 
	WHERE SAL>=(SELECT SAL FROM EMP WHERE ENAME='SCOTT'); 
    
--SELECT SAL FROM EMP WHERE ENAME='SCOTT': 
--	서브쿼리만 드래그해서 범위 지정하고 CTRL+엔터로 확인해봐도 됨

질의 결과 : 서브쿼리 부분만 결과 확인 - 전체 결과 확인

EMP 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,업무,급여 검색

--부서 이름이 DEPT 테이블에 저장되어 있으므로 테이블 조인을 사용하여 검색  
 SELECT EMPNO,ENAME,JOB,SAL FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE DNAME='SALES';
 
--테이블 조인 대신 서브쿼리를 사용하여 검색 
 SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');
 
 질의 결과 - 테이블 조인

질의 결과 - 서브쿼리

EMP 테이블에서 SALES 부서에 근무하는 사원 중 급여를 가장 많이 받는 사원의 사원번호,사원이름,업무,급여 검색
--오라클은 서브쿼리 안에 서브쿼리 사용 가능

--SELECT DEPTNO FROM DEPT WHERE DNAME='SALES' : 서브쿼리
SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP 
    WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES'));

질의 결과 : 서브쿼리 SELECT DEPTNO FROM DEPT WHERE DNAME='SALES' 결과
			- 전체 결과

서브쿼리 검색 결과값이 다중행인 경우

EMP 테이블에서 부서별로 가장 적은 급여를 받는 사원의 사원번호,사원이름,급여,부서번호 검색
--서브쿼리의 검색 결과값이 다중행(MULTI-ROW-SUBQUERY)인 경우 = 연산자를 사용하여 비교하면 에러 발생

--SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO : 이 부분이 다중행!
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL=(SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO); -- =연산자 사용해 에러 발생

1) 서브쿼리 검색 결과값이 다중행인 경우 = 연산자 대신 IN 연산자를 사용하여 컬럼값 비교

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL IN(SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);

질의 결과

2) 서브쿼리의 검색 결과값이 다중행(MULTI-ROW-SUBQUERY)인 경우 > 또는 < 연산자는 ANY(어떠한 것보다) 또는 ALL(모두 보다) 키워드와 같이 사용하여 검색

--EMP 테이블에서 부서번호가 10인 부서에 근무하는 모든 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색 
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL<(SELECT SAL FROM EMP WHERE DEPTNO=10); --다중행이 되었기 때문에 에러 

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL<ALL(SELECT SAL FROM EMP WHERE DEPTNO=10);

질의 결과

--EMP 테이블에서 부서번호가 10인 부서에 근무하는 어떤 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색 (10번 부서 제외)
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL<ANY(SELECT SAL FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10;

질의 결과

--EMP 테이블에서 부서번호가 20인 부서에 근무하는 모든 사원보다 급여가 많은 사원의 사원번호,사원이름,급여,부서번호 검색 
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL>ALL(SELECT SAL FROM EMP WHERE DEPTNO=20);

질의 결과

--EMP 테이블에서 부서번호가 20인 부서에 근무하는 어떤 사원보다 급여가 많은 사원의 사원번호,사원이름,급여,부서번호 검색 
SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL>ANY(SELECT SAL FROM EMP WHERE DEPTNO=20) AND DEPTNO<>20;

질의 결과

3) 다중행 서브쿼리의 ANY 또는 ALL 대신 단일행 서브쿼리의 MIN 또는 MAX 함수를 사용 --ANY, ALL보다 MIN,MAX 사용이 더 속도가 빠르다 (권장방법)

--EMP 테이블에서 부서번호가 10인 부서에 근무하는 모든 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색 
--컬럼명<ALL(다중행 서브쿼리) 대신 컬럼명<(단일행 서브쿼리 - MIN함수)

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL<ALL(SELECT SAL FROM EMP WHERE DEPTNO=10); --위에 표현했던 원래 표현방법 

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL<(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=10);

질의 결과 - MIN 사용

--EMP 테이블에서 부서번호가 10인 부서에 근무하는 어떤 사원보다 급여가 적은 사원의 사원번호,사원이름,급여,부서번호 검색 (10번 부서 제외)
--컬럼명<ANY(다중행 서브쿼리) 대신 컬럼명<(단일행 서브쿼리 - MAX 함수)

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL<ANY(SELECT SAL FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10; --위에 표현했던 원래 표현방법 

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL<(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10) AND DEPTNO<>10;

질의 결과 - MAX 사용

--EMP 테이블에서 부서번호가 20인 부서에 근무하는 모든 사원보다 급여가 많은 사원의 사원번호,사원이름,급여,부서번호 검색
--컬럼명>ALL(다중행 서브쿼리) 대신 컬럼명>(단일행 서브쿼리 - MAX 함수)

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL>(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=20);

질의 결과

--EMP 테이블에서 부서번호가 20인 부서에 근무하는 어떤 사원보다 급여가 많은 사원의 사원번호,사원이름,급여,부서번호 검색
--컬럼명>ANY(다중행 서브쿼리) 대신 컬럼명>(단일행 서브쿼리 - MIN 함수)

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP 
	WHERE SAL>(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20) AND DEPTNO<>20;

질의 결과

멀티컬럼 서브쿼리

--멀티컬럼 서브쿼리
--서브쿼리의 검색대상이 여러개(MULTI-COLUMN SUBQUERY)인 경우인 경우 비교컬럼을 ()안에 ,로 구분하여 나열하면 비교가 가능

-에러남, IN을 사용해도 안된다
SELECT EMPNO,ENAME,MGR,JOB,SAL FROM EMP WHERE MGR=(SELECT MGR,JOB FROM EMP WHERE ENAME='ALLEN') AND ENAME<>'ALLEN'; 

SELECT EMPNO,ENAME,MGR,JOB,SAL FROM EMP WHERE (MGR,JOB)=(SELECT MGR,JOB FROM EMP WHERE ENAME='ALLEN') AND ENAME<>'ALLEN';

질의 결과