Oracle : SQL ( OUTER JOIN / SUBQUERY )

3. OUTER JOIN
 - 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법
 - LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN

 

 ① LEFT OUTER JOIN 
   -INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL값을 붙여서 INNER JOIN의 결과에 추가함
  - 형식 
  SELECT [테이블명1].속성명, [테이블명2].속성명, … 
           FROM 테이블명1 LEFT OUTER JOIN 테이블명2
           ON 테이블명1.속성명 = 테이블명2.속성명 ; 
 SELECT [테이블명1].속성명, [테이블명2].속성명, … 
           FROM 테이블명1, 테이블명2
           WHERE 테이블명1.속성명 = 테이블명2.속성명 (+) ; 

 ※ 구하고자 하는 조인 컬럼이 NULL인 쪽에 '(+)'를 붙임 

 

 ② RIGHT OUTER JOIN
   - INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL값을 붙여서 INNER JOIN의 결과에 추가함
 - 형식 
  SELECT [테이블명1].속성명, [테이블명2].속성명, … 
           FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
           ON 테이블명1.속성명 = 테이블명2.속성명 ; 
 SELECT [테이블명1].속성명, [테이블명2].속성명, … 
           FROM 테이블명1, 테이블명2
           WHERE 테이블명1.속성명 (+) = 테이블명2.속성명 ; 
 
 ※ INNER JOIN : 두 릴레이션에서 관련있는 튜플만 표시 
    LEFT OUTER JOIN : 좌측 릴레이션이 기준이 되어 좌측 릴레이션에 있는 튜플은 모두 표시                 

                                           우측 릴레이션에서는 관련이 있는 튜플만 표시

 

 

 ③ FULL OUTER JOIN 
  - LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합쳐 놓은 것 
  - 형식 
  SELECT [테이블명1].속성명, [테이블명2].속성명, … 
           FROM 테이블명1 FULL OUTER JOIN 테이블명2
           ON 테이블명1.속성명 = 테이블명2.속성명 ; 

  ※ 두 테이블 조인 시, 한 테이블에만 있는 속성은 테이블명 생략 가능 
      단, 두 테이블에 모두 속해있는 속성은 반드시 속성명을 테이블명과 함께 표시해야함  

 

 

 

---

( 예제 1 )

- OUTER JOIN 예시를 위한 데이터 추가 : dept_id 데이터 값을 NULL로 지정 

-- OUTER JOIN 연습을 위한 자료 추가 
INSERT INTO TBL_EMPLOYEE te
	( emp_id, emp_name, gender, age, hire_date, dept_id, address_id )
	VALUES 
	( 6, '왕건', '남성', 35, TO_DATE('2023-07-01', 'YYYY-MM-DD'), NULL, 4);

 

 

- 사원정보와 부서정보를 합친 '사원-부서 정보 테이블' 생성 

-- 사원정보와 부서정보를 합쳐서 사원-부서 정보 테이블을 생성하기 
SELECT te.EMP_ID , te.EMP_NAME , te.GENDER , te.HIRE_DATE , td.DEPT_NAME  
FROM TBL_EMPLOYEE te, TBL_DEPT td 
WHERE te.DEPT_ID = td.DEPT_ID AND te.gender = '남성';

 

- 현재 위에서 추가한 '왕건'의 데이터의 경우,위의 쿼리로 조회되지 않음 

  ( ∵ 데이터 추가 시, dept_id를 NULL로 지정했기 때문에 INNER JOIN으로 조회한 결과에 나타나지 않음 )

   ⇒ 이럴 경우, OUTER JOIN를 이용해야 함 

  ( 조건 ) 테이블1 : 사원테이블 | 테이블2 : 부서테이블 

SELECT te.EMP_ID , te.EMP_NAME , te.GENDER , te.HIRE_DATE , td.DEPT_NAME  
FROM TBL_EMPLOYEE te LEFT OUTER JOIN TBL_DEPT td 
ON te.DEPT_ID = td.DEPT_ID 
WHERE te.gender = '남성';

SELECT te.EMP_ID , te.EMP_NAME , te.GENDER , te.HIRE_DATE , td.DEPT_NAME  
FROM TBL_EMPLOYEE te ,TBL_DEPT td 
WHERE te.DEPT_ID = td.DEPT_ID(+)
      AND te.GENDER = '남성';

 

 

---

( 예제 2 )

 

- 부서 테이블에 '인사팀' 데이터 추가 

INSERT INTO tbl_dept ( dept_id, dept_name )
	VALUES (5, '인사팀');

 

- 사원-부서 테이블 조회해보자 

  단, 현재 사원이 등록되지 않은 '인사팀'도 포함해서 조회하기 

 ( 조건 ) 테이블1 : 사원테이블 | 테이블2 : 부서테이블 

SELECT te.EMP_ID , te.EMP_NAME , td.DEPT_ID , td.DEPT_NAME 
FROM TBL_EMPLOYEE te RIGHT OUTER JOIN TBL_DEPT td 
ON te.DEPT_ID = td.DEPT_ID ;

SELECT te.EMP_ID , te.EMP_NAME, td.DEPT_ID , td.DEPT_NAME 
FROM TBL_EMPLOYEE te , TBL_DEPT td 
WHERE te.DEPT_ID (+) = td.DEPT_ID ;

 

 

---

( 예제 3 )

 

- 사원-부서 테이블 조회 

   단, 부서가 정해지지 않은 사원 포함 / 부서는 있으나 아직 사원이 등록되지 않은 부서 포함 

SELECT te.EMP_ID , te.EMP_NAME , td.DEPT_ID , td.DEPT_NAME 
FROM TBL_EMPLOYEE te FULL OUTER JOIN TBL_DEPT td 
ON te.DEPT_ID = td.DEPT_ID 
ORDER BY emp_id;

SELECT te.EMP_ID , te.EMP_NAME , td.DEPT_ID , td.DEPT_NAME 
FROM TBL_EMPLOYEE te , TBL_DEPT td 
WHERE te.DEPT_ID (+) = td.DEPT_ID (+) ;

⇒ 두번째 방식은 Oracle에서 지원하지 않음 : outer-join된 테이블은 1개만 지정 가능함 

 

 

 

---

SUBQUERY (하위 질의)

 

 - 본 질의 쿼리 보조 또는 하위쿼리로 추가하여 실행되는 쿼리 

   → SELECT문 안에 또 다른 보조 SELECT문을 가지는 경우 

 - 조건절에 주어진 질의를 먼저 수행한 후, 그 검색 결과를 조건절의 피연산자로 사용함 

 - 서브쿼리의 실행 결과 반환 가능 / FROM절에 쓰여 테이블 대신 사용 가능 / WHERE절의 조건식으로 사용 가능 

 

 - 서브쿼리의 종류 : 스칼라 서브쿼리 | 인라인 뷰

  • 메인쿼리와 서브쿼리가 서로 연관성이 있는 경우 ( 주로 JOIN 관계 )

  • 메인쿼리와 서브쿼리가 서로 연관성이 없는 경우  

 

   ① 스칼라 서브쿼리

     - 서브쿼리의 결과가 메인쿼리에 컬럼이나 표현식으로 사용되는 경우 

     - '스칼라'라는 말에서 서브쿼리 SELECT절에서 단 하나의 컬럼이나 표현식만 가지는 경우로, 일치하는 값이 없을 경우 NULL을 반환 

     - 컬럼이나 표현식은 결합연산자( '+' 또는 || ) 연산자로 연결해 하나의 컬럼으로 사용 가능 

     - 예시 

       SELECT

              a.emp_id, a.emp_name, a.gender, a.age, a.dept_id,

             (SELECT b.dept_name FROM tbl_dept b WHERE a.dept_id = b.dept_id) AS dept_name 

       FROM tbl_employee a ;

 

SELECT te.EMP_ID , te.EMP_NAME , te.DEPT_ID , 
(SELECT td.dept_name FROM TBL_DEPT td WHERE te.DEPT_ID = td.dept_id) AS dept_name
FROM TBL_EMPLOYEE te
ORDER BY te.EMP_ID ;

 

 

→ 위의 쿼리를 JOIN를 이용해 변경 

SELECT te.EMP_ID , te.EMP_NAME , te.DEPT_ID , td.DEPT_NAME 
FROM TBL_EMPLOYEE te , TBL_DEPT td 
WHERE te.DEPT_ID = td.DEPT_ID (+)
ORDER BY te.EMP_ID ;

 

 

 

   ② 인라인 뷰 

     - SELECT절의 결과를 FROM절에서 하나의 테이블처럼 사용하고 싶은 경우에 사용 

     - MAIN 쿼리의 FROM절에서 사용하는 서브 쿼리 

     - 스칼라 쿼리와 달리 여러 컬럼 또는 여러 레코드 반환 가능 

     - 구문 

        SELECT 컬럼리스트 

                 FROM 테이블 또는 ( SELECT 서브쿼리_컬럼리스트 FROM 서브쿼리_테이블 ) ;

 

 

( 예시 )

  취미활동을 하지 않는 사원들을 검색 

   ➊ SELECT 이름 FROM 여가활동
   ❷ SELECT * FROM 사원 WHERE 이름 NOT IN ( SELECT 이름 FROM 여가활동 ) ;
            * NOT IN( ) : 포함되지 않는 데이터를 의미 
  
   ‘망원동’에 거주하는 사원들의 ‘기본급’보다 적은 ‘기본급’을 받는 사원의 정보 검색
   ➊ SELECT 기본급 FROM 사원 WHERE 주소 = ‘망원동’ 
   ❷ SELECT 이름, 기본급, 주소 FROM 사원 WHERE 기본급 < ALL ( SELECT 기본급
       FROM 사원 WHERE 주소 = ‘망원동’ ) ;
       * ALL( ) : 하위 질의로 검색된 범위를 기본 질의의 조건으로 사용함 
                      → ➊에서 추출된 ‘기본급’의 모든(ALL)의 범위인 120, 90보다 작은 범위