SQL_Subquery (Subquery, Inline View)

📌 subquery

1. 개요

하나의 sql 명령문의 결과를 다른 sql명령문에 전달하기 위해 두개 이상의 sql명령문을 하나의 sql명령문으로 연결하여 처리하는 방법을 말한다.

2. sub쿼리의 필요성

1) 사원 정보에서 급여의 최고 정보를 가져올 때

• 급여가 최고인 정보를 sql처리
• 그 결과를 조회 조건으로 사원 정보를 검색 처리

3. 처리 과정

1) 서버 쿼리는 메인쿼리가 실행되기 전에 한번씩 실행되고,
2) 서브 쿼리에서 실행된 결과가 메인 쿼리에 전달되어 최종적인 결과를 출력 처리.

서브쿼리가 라인상으로는 하위에 있지만 실행은 먼저하고, 그 결과를 기준으로 데이터를 처리한다.

-- 급여가 최고인 사원의 정보(사원 모든 컬럼)를 출력하세요
-- 1) 최고 급여
-- 2) 그 급여와 동일한 급여를 조회
SELECT * 
FROM emp 
WHERE sal = (
	SELECT max(sal)
	FROM emp
);

--ex1) 급여가 최저인 사원의 정보를 출력하세요
SELECT *
FROM emp
WHERE sal = (
	SELECT min(sal)
	FROM emp
);

--ex2) 최근에 입사한 사원의 정보를 출력하세요. 
SELECT *
FROM emp
WHERE hiredate = (
	SELECT max(hiredate)
	FROM emp
);

📖 subquery의 종류

subquery를 통해 결과가 행이 1개인 경우와 여러개인 경우로 나누어서 사용할 수 있다.

• 단일행 서버쿼리
• 복합행 서버쿼리

1. 단일행 서버쿼리

1) 서브쿼리에서 단 하나의 행만을 검색하여 메인쿼리에 반환하는 질의문
2) 메인쿼리의 where 절에서 서브쿼리의 결과와 비교할 경우에는 반드시 단일행 비교 연산자
중 하나만 사용해야 함

• 단일행 비교 연산자 : =, >, >=, <, <>, <=
  3) 서브쿼리의 결과로 하나의 행만이 출력되어야 함
• 방법1 : 서브쿼리의 조건절에서 기본 키나 고유 키를 '=' 비교하는 방식
• 방법2 : 서브쿼리의 select절에서 전체 집합을 대상으로 그룹함수 사용

-- 방법1
-- 부서명이 SALES인 부서번호를 검색
-- 그 부서정보와 동일한 사원들 검색
SELECT *
FROM EMP e 
WHERE DEPTNO = (
	SELECT DEPTNO 
	FROM DEPT 
	WHERE DNAME = 'SALES'
);

-- 방법2
-- 부서번호 중에 가장 낮은 정보를 검색
-- 그 부서번호에 해당하는 사원들 검색
SELECT *
FROM EMP e 
WHERE DEPTNO = (
	SELECT MIN(DEPTNO)
	FROM DEPT); 

-- 평균 급여보다 높은 사원 정보를 출력
-- 1) 평균 급여 
SELECT AVG(SAL) 평균급여
FROM EMP; 

-- 2) 평균급여 초과 정보 검색
SELECT *
FROM EMP e 
WHERE sal > (
   SELECT AVG(SAL) 평균급여
   FROM EMP
);

-- 3) 평균급여 미만 정보 검색
-- 테이블을 모든 row를 일단 처리해야지 그룹함수의 데이터가 나오기에 먼저 subquery로 
처리하고 그 다음 메인 쿼리에서 데이터를 처리한다. 
SELECT *
FROM EMP e 
WHERE sal < (
 SELECT AVG(SAL) 평균급여
 FROM EMP
);

-- ex1) 30번 부서에 최고 급여자 보다 높은 사원을 검색하세요. 
SELECT *
FROM EMP e 
WHERE sal > (
	SELECT max(sal)
	FROM EMP
	where deptno=30
);

-- ex2) 직책이 CLERK 사람의 평균 급여보다 낮은 사원을 검색하세요
SELECT *
FROM EMP e 
WHERE sal < (
	SELECT AVG(SAL) 
	FROM EMP
	WHERE job = 'CLERK'
);

-- ex3) 관리자가 'MANAGER'인 사원의 최저 급여보다 적은 사원들 출력하세요. 
SELECT *
FROM EMP e 
WHERE sal < (
  SELECT sal
  FROM EMP e 
  WHERE sal = (
    SELECT min(sal)
    FROM EMP e 
    WHERE job='MANAGER'
));


--ex4) emp, salgrade테이블과 조인하여, 등급이 1~2등급인 사원의 최고 급여보다 많은 사원을 
출력하세요. 
SELECT *
FROM emp 
WHERE sal > (
  SELECT max(sal) 
  FROM EMP, SALGRADE
  WHERE empno BETWEEN losal AND hisal
);

2. 다중행 서버쿼리(복합행 서버쿼리)

1. 서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리
2. 메인쿼리의 WHERE 절에서 서버쿼리의 결과와 비교할 경우에는 다중 행 비교 연산자를
   사용하여 비교
3. 다중행 비교 연산자
   IN() : 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 하나라도 일치하면 참, '=' 비교만
   가능
   ANY,SOME() : 메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 하나라도 일치하면 참, '='
   비교만 가능
   ALL() : 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 모든 값이 일치하면 참
   EXISTS() : 메인 쿼리의 비교 조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도
   존재하면 참.

-- ANY,SOME() 
-- any는 어느 하나라도 충족하면 된다. or 조건으로 연결
SELECT *
FROM EMP e 
WHERE sal > any(
  SELECT SAL 
  FROM EMP 
  WHERE DEPTNO =20
);
SELECT *
FROM EMP e 
WHERE sal > (
  SELECT min(SAL)
  FROM EMP
  WHERE deptno = 20);
------------------------ALL() -- all은 나오는 데이터가 and
SELECT *
FROM EMP e 
WHERE sal > (
  SELECT max(SAL)
  FROM EMP
  WHERE deptno = 20); 

exists 연산자를 이용한 다중행 서브 쿼리

1. 서브쿼리에서 검색된 결과가 하나라도 존재하면 메인 조건절이 참이 되는 연산자
2. 서브쿼리에서 검색된 결과가 존재하지 않으면 메인쿼리의 조건절은 거짓

• '선택된 레코드가 없습니다'라는 메시지 존재
• 하위에 있는 데이터가 있기만 하면 상위 쿼리를 수행 처리한다.

3. not exists
   1) exists와 상반되는 연산자
   2) 서브쿼리에서 검색된 결과가 하나라도 존재하지 않으면 메인쿼리의 조건절이 참이 되는
   연산자

-- 관리자 번호가 null인 것이 존재할 때, 사원 정보를 조회하라
SELECT *
FROM emp
WHERE EXISTS (
  SELECT mgr
  FROM EMP e 
  WHERE mgr IS NULL);

-- ex1) 부서번호가 40번이 없을 때, 부서번호가 10인 사원 정보를 출력
-- hint) exist(), not exist(), where에 위 내용 다음 and도 가능
--1단계)
SELECT *
FROM emp
WHERE NOT EXISTS (
  SELECT deptno 
  FROM emp
  WHERE deptno=40 
);
--2단계)
SELECT *
FROM emp
WHERE NOT EXISTS (
  SELECT deptno 
  FROM emp
  WHERE deptno=40)
AND deptno = 10; 

-- ex2) 보너스가 0인 데이터가 있을 떄, 보너스가 null이 아닌 사원 정보를 출력
--1단계) 
SELECT comm
FROM EMP e 
WHERE comm = 0
--2단계)
SELECT *
FROM emp
WHERE EXISTS (
  SELECT comm
  FROM EMP e 
  WHERE comm = 0) 
AND comm IS NOT NULL;

-- null에 대한 검색 조건 컬럼명=null(X) -- 컬럼명 is null : 해당 데이터가 없을 때
-- 컬럼명 is not null : 해당 데이터가 있을 때

📌 inline view

1. view란 기존 테이블의 실제 테이블에서 파생된 논리적 테이블
   emp ==> 논리테이블(부서번호가 10이고 사원명, 급여, 부서번호)
2. inline view는 테이블 구성 시, subquery로 테이블을 논리적으로 선언하여 사용하는 것을
   말한다.

SELECT * 
FROM (
 SELECT empno, ename, job
 FROM EMP 
 WHERE deptno = 10
); 

SELECT d.*, sal
FROM (
 SELECT deptno, max(sal) sal 
 FROM EMP e 
 GROUP BY deptno
)e, dept d
WHERE e.deptno = d.deptno


-- ex) 인라인뷰1 (직책별 최고 급여), 사원 전체(emp)
-- 조인 : 급여
-- ==> 직책별 최고 급여자의 정보를 출력
-- 1단계
SELECT job, max(sal) sal
FROM EMP e 
GROUP BY job; 

-- 2단계
SELECT me.*, e.*
FROM (
	SELECT job, max(sal) sal
	FROM EMP e 
	GROUP BY job) me, emp e
WHERE me.sal = e.sal
ORDER BY me.sal; 

-- 과제 1. inline view을 활용하여 분기별 최저급여자의 사원 정보를 출력하세요. 
SELECT me.*, e.*
FROM (
  SELECT to_char(hiredate,'Q') 분기, min(sal) sal
  FROM EMP e 
  GROUP BY to_char(hiredate,'Q')) me, emp e
WHERE me.sal = e.sal
ORDER BY me.sal;