1. SUBQUERIES

• SUBQUERIES는 다른 쿼리 내에서 실행되는 쿼리이다.
• SUBQUERIES의 결과를 활용해서 복잡한 MAINQUERY를 작성해 한번에 여러 작업을 수행할 수 있다.

1. SUBQUERIES 활용

• 서브쿼리와 메인 쿼리를 활용한 다중열 결과 조회

  • 서브쿼리

    SELECT dept_code FROM employee WHERE emp_name = '유재식';  -- D6인 것을 알아내는 쿼리

  • 메인쿼리

    SELECT * FROM employee WHERE dept_code = 'D6'; -- 유재식씨와 같은 부서에서 일하는 직원(본인 포함됨)

    범위가 큰게 메인 쿼리, 범위가 좁으면 서브 쿼리

  • 서브쿼리를 포함한 메인쿼리

    SELECT 
    		*
        FROM employee
        WHERE dept_code = (SELECT dept_code
        							 FROM employee
        							WHERE emp_name = '유재식');

• 위의 방법을 통해서 유재식씨의 사원번호 추출해서 유재식 사원을 제외한 사원 추출하기

-- 이와 같은 방법으로 유재식씨 사원번호를 추출해서 해당 사원을 제거해 보자.
SELECT
		*
	FROM employee
	WHERE dept_code = (SELECT dept_code
								FROM employee
								WHERE emp_name = '유재식')
	AND emp_id <> (SELECT emp_id
						FROM employee
						WHERE emp_name = '유재식');

2. 상관 서브쿼리

• 메인 쿼리가 서브쿼리의 결과에 영향을 주는 경우 상관 서브쿼리라고 한다.

-- 메뉴를 조회하는데 메뉴의 카테고리에 있는 메뉴들의 평균 가격보다 높은 메뉴만 조회(상관서브쿼리, 단일행 서브쿼리)
SELECT 
		a.menu_code
		, a.menu_name
		, a.menu_price
		, a.category_code
		, a.orderable_status
    FROM tbl_menu a
    WHERE a.menu_price > (SELECT AVG(b.menu_price)
    								 FROM tbl_menu b
    								 WHERE b.category_code = a.category_code);

-- 메뉴 카테고리의 평균과 같은 가격의 메뉴를 조회(상관 서브쿼리 안쓰고 다중행 서브쿼리)
SELECT
		*
	FROM tbl_menu a
	WHERE a.menu_price IN (SELECT AVG(b.menu_price)
									FROM tbl_menu b
									GROUP BY b.category_code);

• FROM 절에 쓰인 서브쿼리(derived table, 파생 테이블)는 반드시 자신의 별칭이 있어야 한다.(feat. 이러한 서브쿼리를 ‘인라인 뷰’라고 한다.)

3. EXISTS

• 조회 결과가 있을 때 true 아니면 false

-- 메뉴가 있는 카테고리 조회
SELECT
		category_code
      , category_name 
  FROM tbl_category a
 WHERE EXISTS(SELECT 1
                FROM tbl_menu b
                WHERE b.category_code = a.category_code)
	 ORDER BY 1;
-- ----------------------------------------------------------------------------------------------
-- 4번 카테고리를 메인 쿼리에서 where 조건 판별 중 동작하는 서브쿼리
SELECT 1
	FROM tbl_menu b
	WHERE b.category_code = 4;				 
-- ----------------------------------------------------------------------------------------------
-- join을 활용한 메뉴가 있는 카테고리 조회도 해보자.
SELECT
		a.category_code
		, a.category_name
	FROM tbl_category a
	JOIN tbl_menu b ON (a.category_code = b.category_code)
	GROUP BY a.category_code, a.category_name;

4. CTE(Common Table Expressions)

• FROM절에서만 사용 됨(JOIN일 시 JOIN 구문에서도 가능)
• 인라인 뷰로 쓰인 서브쿼리(FROM 절에 쓰인 서브쿼리)를 미리 정의하고 메인쿼리가 심플해 질 수 있도록 사용하는 문법

• 점심시간 전 문제

  -- 1. 가장 높은 급여를 받는 사원 조회하기
  
  SELECT 
  			MAX(salary)
  		FROM employee;
  		
  -- 서브 쿼리
  SELECT
  		*
  	FROM employee
  	WHERE salary = (SELECT MAX(salary)
  							FROM employee); 
  
  -- 2.평균 급여가 가장 높은 부서 조회하기
  -- 1) 이중 서브 쿼리를 활용한 방법
  SELECT AVG(salary)
  	FROM employee
  	GROUP BY dept_code;
  	
  SELECT MAX(a.sal_avg)
  	FROM (SELECT AVG(salary) sal_avg -- 연산식을 사용하면 안되기 때문에 별칭을 달아줘야 함	
  				FROM employee
  				GROUP BY dept_code) a;
  
  SELECT 
  		dept_code
  	FROM employee
  	GROUP BY dept_code
  	HAVING AVG(salary) = (SELECT MAX(a.sal_avg)
  									FROM (SELECT AVG(salary) sal_avg 
  											FROM employee
  											GROUP BY dept_code) a);
  
  -- 2) >= All을 활용한 방법
  
  SELECT 
  		dept_code
  	FROM employee
  	GROUP BY dept_code
  	HAVING AVG(salary) >= ALL (SELECT AVG(salary)
  											FROM employee
  											GROUP BY dept_code);

서브쿼리 중에 다중행 서브쿼리인 경우에는 비교 연산자가 일반 비교연산자와 달라진다.
' > ALL, < ALL, > ANY, <ANY, IN '
1. > ALL: 모든 서브쿼리 결과보다 크다(서브쿼리의 최대값보다 크다)
2. < ALL: 모든 서브쿼리 결과보다 작다(서브쿼리의 최소값보다 작다)
3. > ANY: 서브쿼리 결과 보다 최소 하나보다는 크다(서브쿼리의 최소값보다 크다)
4. < ANY: 서브쿼리 결과 보다 최대 하나보다는 작다(서브쿼리의 최대값보다 작다)
5. IN: 서브쿼리 결과 중에 하나라도 일치한다