08. 서브 쿼리 SUBQUERY

CHOISUJIN·2023년 1월 17일
db
0
post-thumbnail

SUBQUERY 서브쿼리

  • 하나의 SQL문 안에 포함된 또다른 SQL(SELECT)문
  • SELECT, FROM, WHERE, HAVING 절에서 사용가능
-- 부서코드가 노옹철 사원과 같은 소속의 직원의 이름, 부서코드 조회하기

SELECT EMP_NAME , DEPT_CODE 
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE 
					FROM EMPLOYEE
					WHERE EMP_NAME = '노옹철');

📍 단일행 (단일열) 서브쿼리

: 서브쿼리의 조회 결과 값의 개수가 1개일 때 (1행 1열)

  • 단일행 서브쿼리 앞에는 비교 연산자 사용
  • <, >, <=, >=, =, !=/^=/<>
SELECT CEIL(AVG(SALARY)) FROM EMPLOYEE -- 단일행, 단일열

-- 전 직원의 급여 평균보다 많은(초과) 급여를 받는 직원의 
이름, 직급, 부서, 급여를 직급 순으로 정렬하여 조회

SELECT EMP_NAME , JOB_NAME, DEPT_TITLE, SALARY  	
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE)
WHERE SALARY > (SELECT CEIL(AVG(SALARY)) FROM EMPLOYEE) -- 단일행
ORDER BY JOB_CODE ;
-- 부서별(부서가 없는 사람 포함) 급여의 합계 중 가장 큰 부서의
부서명, 급여 합계를 조회 

SELECT DEPT_TITLE , SUM(SALARY) 
FROM EMPLOYEE		
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY)) 
					  FROM EMPLOYEE 	
				 	  GROUP BY DEPT_CODE );

📍 다중행 (단일열) 서브쿼리

: 서브쿼리의 조회 결과 값의 개수가 여러개일 때 (행은 여러개 컬럼은 1개)
--> 다중행 서브쿼리 앞에는 일반 비교연산자 사용 불가

  • IN / NOT IN
    : 여러 개의 결과값 중에서 한개라도 일치하는 값이 있다면 또는 없다!
SELECT MAX(SALARY)
FROM EMPLOYEE 
GROUP BY DEPT_CODE
-- 부서별 최고 급여를 받는 직원의 
-- 이름, 직급, 부서, 급여를 부서 순으로 정렬하여 조회

SELECT EMP_NAME , JOB_CODE , DEPT_CODE , SALARY 					
FROM EMPLOYEE	
WHERE SALARY IN (SELECT MAX(SALARY)
				 FROM EMPLOYEE 
				 GROUP BY DEPT_CODE)
ORDER BY DEPT_CODE ;
-- 사수에 해당하는 직원에 대해 조회 
--  사번, 이름, 부서명, 직급명, 구분(사수 / 직원)
-- * 사수 == MANAGER_ID 컬럼에 작성된 사번

SELECT EMP_ID , EMP_NAME , DEPT_TITLE, JOB_NAME, '사수' AS "구분"
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE EMP_ID  IN (SELECT DISTINCT MANAGER_ID 
						FROM EMPLOYEE 
						WHERE MANAGER_ID IS NOT NULL);
                        
                        
SELECT EMP_ID , EMP_NAME , DEPT_TITLE, JOB_NAME, '사원' AS "구분"
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE EMP_ID  NOT IN (SELECT DISTINCT MANAGER_ID 
						FROM EMPLOYEE 
						WHERE MANAGER_ID IS NOT NULL);                        

SELECT EMP_ID , EMP_NAME , DEPT_TITLE, JOB_NAME, 
	CASE WHEN EMP_ID IN (SELECT DISTINCT MANAGER_ID
							FROM EMPLOYEE
							WHERE MANAGER_ID IS NOT NULL)
							THEN '사수'
							ELSE '사원'
	END 구분
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
ORDER BY EMP_ID;
  • UNION : 집합 연산자 (합집합)
SELECT EMP_ID , EMP_NAME , DEPT_TITLE, JOB_NAME, '사수' 구분
FROM EMPLOYEE 
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE  EMP_ID  IN (SELECT DISTINCT MANAGER_ID
					FROM EMPLOYEE 
					WHERE MANAGER_ID IS NOT NULL )
UNION 				    
SELECT EMP_ID , EMP_NAME , DEPT_TITLE, JOB_NAME, '사원' 구분
FROM EMPLOYEE 
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE  EMP_ID  NOT IN (SELECT DISTINCT MANAGER_ID
					FROM EMPLOYEE 
					WHERE MANAGER_ID IS NOT NULL );

--> 쉽지만 정렬하려면 Result Set을 SELECT하는 구문이 또 필요함

  • > ANY : 가장 작은 값보다 큰가?
  • < ANY : 가장 큰 값보다 작은가?
-- 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다 많이 받는 직원
-- 1) MIN을 이용한 단일행 서브쿼리.

	SELECT EMP_ID , EMP_NAME , JOB_NAME , SALARY 
	FROM EMPLOYEE 
	JOIN JOB USING(JOB_CODE)
	WHERE JOB_NAME = '대리'
	AND SALARY > ( SELECT MIN(SALARY) 
					FROM EMPLOYEE 
					JOIN JOB USING(JOB_CODE)
					WHERE JOB_NAME = '과장' );
	
	SELECT MIN(SALARY) 
	FROM EMPLOYEE 
	JOIN JOB USING(JOB_CODE)
	WHERE JOB_NAME = '과장';
    
-- 2)ANY를 이용하여 과장 중 가장 급여가 적은 직원 초과하는 대리를 조회
			 
	SELECT EMP_ID , EMP_NAME , JOB_NAME , SALARY 
	FROM EMPLOYEE 
	JOIN JOB USING(JOB_CODE)
	WHERE JOB_NAME = '대리'
	AND SALARY > ANY ( SELECT SALARY 
					FROM EMPLOYEE 
					JOIN JOB USING(JOB_CODE)
					WHERE JOB_NAME = '과장' );
  • > ALL : 가장 큰 값보다 큰가?
  • < ALL : 가장 작은 값보다 작은가?
	SELECT EMP_ID , EMP_NAME , JOB_NAME, SALARY 
	FROM EMPLOYEE e 
	JOIN JOB USING(JOB_CODE)
	WHERE JOB_NAME = '과장'
	AND SALARY > (SELECT MAX(SALARY) 
				 FROM EMPLOYEE 
				 JOIN JOB USING(JOB_CODE)
				 WHERE JOB_NAME = '차장' );
                 

	SELECT EMP_ID , EMP_NAME , JOB_NAME, SALARY 
	FROM EMPLOYEE e 
	JOIN JOB USING(JOB_CODE)
	WHERE JOB_NAME = '과장'
	AND SALARY > ALL (SELECT SALARY 
					 FROM EMPLOYEE 
					 JOIN JOB USING(JOB_CODE)
					 WHERE JOB_NAME = '차장' );
  • EXISTS / NOT EXISTS : 값이 존재하는가? / 존재하지 않는가?

📍 (단일행) 다중열 서브쿼리

: 서브쿼리의 SELECT절에 나열된 컬럼 수가 여러개 일 때 (컬럼이 여러개, 행은 1개)

SELECT EMP_NAME , JOB_CODE , DEPT_CODE , HIRE_DATE 
FROM EMPLOYEE										 
WHERE (DEPT_CODE , JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE
								FROM EMPLOYEE
								WHERE ENT_YN = 'Y'
								AND SUBSTR(EMP_NO, 8 , 1) = '2');

--> WHERE절에 작성된 컬럼 순서와 서브쿼리의 조회 컬럼 순서가 동일해야 함!

📍 다중행 다중열 서브쿼리

: 조회 결과 행 수와 열 수가 여러개일 때 (행 여러개 컬럼도 여러개)

--본인 직급의 평균 급여를 받고 있는 직원

SELECT EMP_ID , EMP_NAME , JOB_CODE , SALARY 
FROM EMPLOYEE 
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, TRUNC(AVG(SALARY),-4)
							 FROM EMPLOYEE 
							 GROUP BY JOB_CODE );

📍 상관 서브쿼리 (상호연관)

: 서브쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때 메인 쿼리 테이브르이 값이 변경되면 서브쿼리의 결과값도 바뀌는 서브쿼리 -> 메인이 먼저, 서브가 나중에 해석된다

SELECT EMP_NAME, JOB_CODE, SALARY -- 메인쿼리
FROM EMPLOYEE MAIN
WHERE SALARY > (SELECT AVG(SALARY)
				FROM EMPLOYEE SUB
				WHERE SUB.JOB_CODE = MAIN.JOB_CODE);
SELECT EMP_ID , EMP_NAME , NVL(DEPT_TITLE, '소속없음'), JOB_NAME, HIRE_DATE 
FROM EMPLOYEE MAIN
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
WHERE ENT_YN = 'N'
AND HIRE_DATE = (SELECT MIN(HIRE_DATE)
				   FROM EMPLOYEE SUB
				   WHERE MAIN.DEPT_CODE = SUB.DEPT_CODE)
ORDER BY HIRE_DATE;

📍 스칼라 서브쿼리 (단일)

: SELECT절에만 사용되는 서브쿼리 결과로 1행만(단일) 반환

-- 모든 직원의 이름, 직급, 급여, 전체 사원 중 가장 높은 급여와의 차

SELECT EMP_NAME, JOB_CODE, SALARY, 
(SELECT MAX(SALARY) SALARY FROM EMPLOYEE) - SALARY
FROM EMPLOYEE;
-- 모든 사원의 사번, 이름, 관리자사번, 관리자명을 조회
-- 단, 관리자가 없는 경우 '없음' 으로 표시
-- 스칼라 + 상관쿼리

SELECT EMP_ID, EMP_NAME, NVL(MANAGER_ID, '없음'), 
		NVL((SELECT EMP_NAME
		FROM EMPLOYEE SUB
		WHERE MAIN.MANAGER_ID = SUB.EMP_ID),'없음') "관리자명"
FROM EMPLOYEE MAIN;

📍 인라인 뷰 INLINE-VIEW

: FROM절에서 서브쿼리 사용!

SELECT * 
FROM (SELECT EMP_NAME "이름", DEPT_TITLE "부서"
		FROM EMPLOYEE
		JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE))
WHERE 부서 = '기술지원부';
profile
CHOISUJIN
매일매일 머리 터지는 중 ᕙ(•̀‸•́‶)ᕗ
이전 포스트

0115. 데이터베이스 구현 TEST

다음 포스트

09. DDL(ALTER, DROP) -2

0개의 댓글