1. 서브쿼리(시험예상)

• 다른 쿼리 내부에 포함되어 있는 SELECT 쿼리를 의미, 보다 다양한 데이터를 출력하는 방법을 제공
  → “ 실무에서 굉장히 많이 사용되는 기술 ”

---

1.1. 스칼라 서브쿼리

• SELECT 에서 사용되는 서브쿼리로 하나의 컬럼에 대해 하나의 행만 반환하는 특징. 출력되는 하나의 값이 없다면 NULL 값 반환
• 서브 쿼리를 사용하다보면 컬럼 또는 테이블 중 어느 것인지 혼동하는 경우가 있음. -> SELECT 컬럼으로 사용하는 것이 스칼라 서브쿼리

1.1.1. 스칼라 서브쿼리의 실행 원리

• 메인쿼리에서 출력되는 튜플의 수만큼 SELECT 에 있는 서브쿼리 반복 실행

• 스칼라 서브쿼리 실행 순서(위 이미지 참조)
  1. BTWEEN의 범위를 A.직원ID 'A0001'을 찾음
  2. A.직원ID 'A0001'에 A.부서ID의 'D0001'을 찾음
  3. A.부서ID의 'D0001'이 스칼라 서브쿼리에 들어감
  4. 스칼라 서브쿼리에서 'D0001'기준으로 부서명을 찾음
  5. 'A0001'과 'D0001'에 대한 부서명을 출력함 하나의 데이터 (인사부)
  

--실습해보기 !

--직원테이블에서 직원A0001 부터 A0006까지의 직원ID, 연봉, 부서ID를
--출력하고 부서ID에 대한 부서명도 함께 출력하시오.
--(스칼라 서브쿼리 말고 직원, 부서 테이블을 조인방식으로 해결해보자)

-- 오라클 OUTER JOIN 문법
  SELECT  A.직원ID, A.연봉, B.부서ID, B.부서명
  FROM    직원 A , 부서 B
  WHERE   A.부서ID = B.부서ID(+)
  AND A.직원ID BETWEEN 'A0001' AND 'A0006';

-- ANSI LEFT OUTER JOIN 문법
  SELECT  A.직원ID, A.연봉, B.부서ID, B.부서명
  FROM    직원 A LEFT OUTER JOIN 부서 B
  ON   A.부서ID = B.부서ID
  WHERE A.직원ID BETWEEN 'A0001' AND 'A0006';

※ 아우터 조인과 스칼라 서브쿼리의 차이점.
1. 성능면에서 차이가 있음.
2. 아우터 조인은 가끔 사용할때 좋음.
3. 스칼라 서브쿼리는 자주 사용할때 좋음, 자주 사용시 컴퓨터 캐쉬라는 메모리 한켠에 저장함.

1.1.2. 스칼라 서브쿼리 주의사항

--문제1) 직원테이블에서 직원 A0001 부터 A0006 까지의 직원ID , 연봉 , 부서ID 를
--출력하고 부서ID에 대한 부서명도 함께 출력되도록 해주세요. (스칼라서브쿼리 사용)

-- 오라클 스칼라 서브쿼리 문법
  SELECT A.직원ID 
  		, A.연봉 
        , A.부서ID
        , ( SELECT  부서명
            FROM    부서
            WHERE   부서ID = A.부서ID ) AS 부서명
  FROM 직원 A
  WHERE 직원ID BETWEEN  'A0001' AND 'A0006' ; 

-- 오라클 아우터 조인 문법
  SELECT  A.직원ID 
  		, A.연봉 
        , B.부서ID
        , B.부서명
  FROM    직원 A, 부서 B
  WHERE   A.부서ID = B.부서ID(+)
  AND A.직원ID BETWEEN  'A0001' AND 'A0006' ; 



--문제2) 직원테이블에서 직원 A0006 부터 A0010 까지의 직원ID , 이름 , 주민등록번호 를 출력하고
--휴대폰번호도 함께 출력되도록 해주세요. (없으면 NULL이 출력되도록 스칼라서브쿼리 활용)

-- 오라클 스칼라 서브쿼리 문법
  SELECT  A.직원ID 
  		, A.이름 
        , A.주민등록번호
        , ( SELECT  연락처
            FROM    직원연락처
            WHERE   직원ID = A.직원ID
            AND 구분코드 = '휴대폰' ) AS 휴대폰번호
  FROM    직원 A
  WHERE A.직원ID BETWEEN  'A0006' AND 'A0010' ; 

-- 오라클 아우터조인 문법
SELECT  A.직원ID 
		, A.이름 
        , A.주민등록번호
        , B.연락처 AS 휴대폰번호
FROM    직원 A , 직원연락처 B
WHERE   A.직원ID = B.직원ID(+)
AND A.직원ID BETWEEN  'A0006' AND 'A0010'  
AND (구분코드 = '휴대폰' OR 연락처 IS NULL) ;

---

2.1. 인라인 뷰 (시험예상)

! 이전 시간 교육한 자료 참조 : TOP-N (ROWNUM + 인라인 뷰)

• FROM 에서 사용되는 서브쿼리로 마치 가상의 테이블처럼 이용 가능한 서브쿼리
  → 따로 떼어내서 실행해도 결과가 출력되는 독립적인 쿼리
  

-- 실습문제
-- 1. 나이가 어린 직원 3명의 모든 정보를 출력해주세요.
SELECT *
FROM ( SELECT  *
       FROM    직원
       ORDER BY 나이 ASC )
WHERE ROWNUM <= 3;

---

3.1. 중첩 서브쿼리 (Nested Sub query)

• WHERE 에서 주로 사용되는 서브쿼리로 메인쿼리와 관계가 있는지에 따라 상관 서브쿼리와 비상관 서브쿼리로 나눔
  

• 비상관 서브쿼리 : 메인 쿼리의 컬럼을 사용하지 않는 서브쿼리
  → 서브쿼리가 먼저 실행되고 메인 쿼리 실행
  → 비상관 서브쿼리는 독립적으로 SQL문 실행 가능

-- 평균 연봉보다 높은 연봉을 받는 데이터 출력
  SELECT  *
  FROM    직원  
  WHERE   연봉 >= (SELECT AVG(연봉)
                  FROM 직원);

• 상관 서브쿼리 : 메인 쿼리의 컬럼을 사용하는 서브쿼리
  → 메인쿼리가 먼저 실행되고 서브 쿼리 실행
  → 상관 서브쿼리는 서브쿼리가 독립할 수 없는 구조!

-- 부서 마다 가장 적은 연봉을 받는 데이터 출력
  SELECT *
  FROM  직원 A
  WHERE 연봉 = ( SELECT MIN (연봉)
                FROM 직원
                WHERE 부서ID = A.부서ID ) ;
                -- A.부서ID로 메인쿼리와 묶여 있음.

---

※ 중요하니 다시 읽어보기 ※

비상관 서브쿼리와 상관 서브쿼리는 독립적인지 아닌지로 판별 가능 ! ( 독립적 : 비상관 서브쿼리 )

---

3.1.1. 상관 서브쿼리의실행 원리

• 상관 서브쿼리의실행 순서(위 이미지 참조)

1. 직원 테이블에서 A0001의 직원ID에서 부서ID 파악함.
2. MAIN A.부서ID 와 중첩쿼리의 부서ID가 같은 것 중에서
3. 가장 적은 연봉의 직원 데이터 불러옴
4. MAIN A0001의 직원 테이블의 *(모든) 데이터를 불러옴
5. 테이블의 인스턴스 끝까지 반복

3.1.2. 실습문제

-- 1. 부서별로 가장 높은 연봉을 가진 직원들의 모든 정보를 출력해주세요.
--(이번에는 인라인 뷰가 아니라 WHERE 절에 상관서브쿼리를 활용합니다) 

--상관 서브쿼리
    SELECT  *
    FROM    직원 A
    WHERE   연봉 = ( SELECT  MAX(연봉) 
                    FROM     직원
                    WHERE   부서ID = A.부서ID );
                
                
-- 2 .입사를 가장 늦게 한 직원의 정보를 모두 출력해주세요.

-- 비상관 서브쿼리
    SELECT 	*
    FROM 	직원 A
    WHERE 	입사일시 = ( SELECT MAX(입사일시)
                        FROM  직원 );


-- 3. 가장 고연봉인 직원의 정보를 모두 출력해주세요.

-- 비상관 서브쿼리
    SELECT 	*
    FROM 	직원 A
    WHERE 	연봉 = ( SELECT MAX(연봉)
                    FROM  직원 );

• WHERE절 중첩 서브쿼리 사용 이유

1. WHERE 조건을 자동으로 맞추기 위해 사용함
2. 서브쿼리를 사용하지 않고 SQL문을 작성하려면 SELECT *, MAX(연봉)로 표현할 수 없기 때문에 각각의 컬럼을 SELECT에 입력해야 하는 번거로움을 서브 쿼리로 간결하게 작성
   (이는 JOIN에서도 가능)