KH 47일차 - ANSI JOIN / 서브쿼리 (*****)

-- -------------------------------------------

-- + Oracle Join 응용

-- -------------------------------------------

-- 이 쿼리의 목적은 각 사원의 "관리자의 관리자"가 누구인지 찾아내기 위해

-- 셀프조인을 2번하는 것이다.

SELECT

    e.last_name AS 사원명,

    m.last_name AS 관리자명,

    n.last_name AS "관리자의 관리자명"

FROM

    employees e,        -- 사원

    employees m,        -- 관리자

    employees n         -- 관리자의 관리자

WHERE

    e.manager_id = m.employee_id

    AND m.manager_id = n.employee_id;

-- 사원의 관리자 id가 관리자의 사번과 같고, 관리자의 관리자 id가 관리자의 관리자 사번과 같다고 동등 조인을 하여 각자의 이름이 출력된다.

-- 이 경우 만약 관리자가 없거나, 관리자의 관리자가 없는 행의 경우에는 탈락되게 된다.

-- -------------------------------------------

SELECT

    e.last_name AS 사원명,

    m.last_name AS 관리자명,

    n.last_name AS "관리자의 관리자명"

FROM

    employees e,        -- 사원

    employees m,        -- 관리자

    employees n         -- 관리자의 관리자

WHERE

    e.manager_id = m.employee_id (+)            -- LEFT Outer Join

    AND m.manager_id = n.employee_id (+);       -- LEFT Outer Join

-- 이 경우 관리자가 없거나, 관리자의 관리자가 없는 행도 외부조인으로 되살려서 출력하게 된다.

-- 이때 (+)가 붙은 쪽은 NULL로 출력되게 된다.

-- -------------------------------------------

SELECT

    -- 사원 테이블의 칼럼들

    e.employee_id,

    e.last_name,

    e.department_id,

    -- 부서 테이블의 칼럼들

    d.department_id,

    d.department_name

FROM

    employees e,

    departments d

WHERE

    -- e.department_id = d.department_id;      -- Equi Join

    e.department_id = d.department_id(+);      -- Left Outer Join

-- Left 아우터 조인을 통해서 부서가 없는 직원까지 NULL로 출력하게 작성하였다.

 

---

-- -------------------------------------------

-- * ANSI JOIN 특징

-- -------------------------------------------

-- 가. Oracle 이외의 관계형 데이터베이스에서도 사용가능한 표준

-- 나. 여러 테이블의 조인 조건을, WHERE 절에 명시하지 않고,

--     다른 방법을 통해(주로, FROM절에 기재) 기술 (***)

-- 다. 검색조건을 WHERE 절에 기재(조인조건과 검색조건을 분리) (**)

-- 라. 가독성 향상

-- + ANSI JOIN은 ORACLE JOIN보다 잘 사용되지 않는다.

-- -------------------------------------------

---

-- -------------------------------------------

-- 1. ANSI JOIN

-- -------------------------------------------

--  a. Cross Join

--      == The same as Oracle Cartesian Product.

--  b. Natural Join    

--      == The same as Oracle Equal Join

--         with implicit columns automatically searched.

--  c. USING(Common Columns) or ON <Join Condition> (***)

--      == The same as Oracle Equal Join

--         with explicit columns manually determined.

--  d. JOIN ~ ON

--      == The same as Oracle Non-equal Join.

--  e. { LEFT | RIGHT | FULL } OUTER JOIN

--      == The same as Oracle Outer Join.

--  f. Self Join

--      == The same as Oracle Self Join.

-- -------------------------------------------

---

-- -------------------------------------------

-- A. Cross Join ( = Cartesian Product )

-- -------------------------------------------

-- The same as Oracle Cartesian Product.

-- 조인에 참여한 각 테이블의 레코드의 갯수를 모두 곱한 결과 반환

--

-- * 조인결과: 테이블1 x ... x 테이블n 개의 레코드 생성

-- -------------------------------------------

-- Basic Syntax)

--  SELECT 테이블1.컬럼 , 테이블2.컬럼

--  FROM 테이블1 CROSS JOIN 테이블2

-- -------------------------------------------

SELECT

    count(*)

FROM

    employees;

-- 사원 테이블에서 사원이 총 몇 명인지 확인한다. ( 107 )

SELECT

    count(*)

FROM

    departments;

-- 부서 테이블에서 부서의 개수를 확인한다. ( 27 )

SELECT

    count(*)

FROM

    employees CROSS JOIN departments;

-- 부서와 직원의 테이블의 크로스 조인 결과물을 보여준다. ( 총 2889개 )

SELECT

    last_name,

    department_name

FROM

    employees CROSS JOIN departments;

-- 부서와 직원의 테이블의 크로스 조인 결과물에서 이름과 부서이름을 출력한다.

SELECT

    -- employees t1에 있는 컬럼

    t1.last_name,

    last_name,

    -- departments t2에 있는 컬럼

    t2.department_name,

    department_name,

    -- employees t1와 departments t2 모두에 있는 공통 컬럼

    t1.manager_id,

    -- employees t1와 departments t2 모두에 있는 공통 컬럼

    t2.department_id

FROM

    employees t1 CROSS JOIN departments t2;

-- 이를 통해서 별칭을 사용해도 / 사용하지 않아도 정상출력되고 있음을 알 수 있다. ( 별칭이 필수가 아니다. )

-- 그러나, 만약 컬럼의 모호성이 있을 경우에는 반드시 별칭을 사용해야 한다.

 

---

-- -------------------------------------------

-- B. Natural Join ( Oracle Equal Join과 비슷 )

-- -------------------------------------------

-- ** 자연조인 = 동등조인 + 공통컬럼의 중복제거 (***)

-- -------------------------------------------

-- 가. 두 테이블의 같은 이름을 가진 컬럼에 기반하여 동작.

-- 나. 두 테이블에 반드시 하나의 공통컬럼이 있어야 함.

-- 다. (*주의*) 만일, 두 개 이상의 공통컬럼이 존재하는 경우,

--     엉뚱한 결과를 생성할 수 도 있음.

--     즉, 두 개 이상의 공통컬럼 값이 동일한 레코드만 조회. (***)

-- 라. 테이블 별칭(Table Alias)도 사용가능.

-- 마. (*주의*) SELECT절에 컬럼 나열시, 두 테이블의 공통컬럼을

--     나열할 때, 테이블명(또는 테이블별칭)을 사용하는 경우 오류발생

--

--     ** ORA-25155: NATURAL 조인에 사용된 열은 식별자를 가질 수 없음

-- -------------------------------------------

-- Basic Syntax) FROM절에, NATURAL JOIN 키워드 사용

--

--  SELECT 테이블1.컬럼 , 테이블2.컬럼

--  FROM 테이블1 NATURAL JOIN 테이블2

--  [WHERE 검색조건];

-- -------------------------------------------

-- 1 ) Oralce Equal Join

SELECT

    last_name,

    t1.department_id,

    t2.department_id,

    t1.manager_id,

    t2.manager_id,

    department_name

FROM

    employees t1,

    departments t2

WHERE

    t1.department_id = t2.department_id;

-- 오라클 동등조인에서는 각각의 테이블에서 동등 칼럼을 지정해 줘야 한다.

-- 칼럼 이름이 같다고 해서 무조건 동등조인으로 연결하는 것이 아니라 속성의 성질이 같은지도 확인해야 한다.(**)

-- 2 ) ANSI Natural Join

SELECT

    last_name,

    department_id,      -- 공통 칼럼

    manager_id,         -- 공통 칼럼...?

    department_name

FROM

    employees NATURAL JOIN departments;

-- ANSI 네츄럴 조인에서는 조인할 컬럼을 지정하는게 아니라, 같은 이름의 컬럼을 자동으로 조인하여 중복을 제거한다. (**)

-- 이로 인해서 PK와 FK의 관계가 아니라 단순히 이름이 같은 컬럼들이 조인되어버려 잘못된 결과를 출력할 가능성이 높다. (***)

-- 이 경우에도 manager_id가 PK와 FK의 관계가 아닌데, 이름이 같다는 이유로 조인되어버려 잘못된 결과가 나왔다.

--  * ANSI Natural Join에서는 칼럼을 작성할때 employees.manager_id처럼 테이블명을 앞에 붙이면 오류를 발생하게 된다. (**)

-- 3 ) ANSI Natural Join - 별칭을 사용하는 경우

-- SELECT 절에, 테이블 별칭(table alias)이 적용된, 두 테이블의 컬럼 나열시,

-- 테이블명.컬럼 형식으로 나열하면 오류발생 (테이블 별칭이 적용되었으면, 테이블 별칭 사용가능(옵션))

SELECT

    last_name,

    t1.last_name,

    department_name,

    t2.department_name,

    manager_id

    -- t1.manager_id : 오류가 발생 ( 모호성을 제거해서는 안된다. )

FROM

    employees t1 NATURAL JOIN departments t2;

-- 별칭의 경우에는 t2.department_name처럼 칼럼 앞에 별칭을 붙여도 정상출력되고 있음을 알 수 있다. ( 테이블명.칼럼은 불가능 )

-- 자연조인은 중복된 칼럼을 제거하고 1번만 나오게 해주기 때문에,

-- 그 결과로 모든 칼럼이 고유한 이름이기에, 소속을 밝힐 이유가 없다.

-- 즉, 일반 칼럼들에 대해서는 별칭을 사용해도 괜찮지만, 공통칼럼은 작성해서는 안된다. (***)

SELECT

    last_name,

    department_name,

    department_id

FROM

    employees t1 NATURAL JOIN departments t2

WHERE

    department_id = 90;

-- 검색조건을 추가하여 90번 부서에 속한 직원만 출력하게 하였다. ( 하지만 잘못 조인되어 있어 이상한 결과가 출력된다. )

 

---

-- -------------------------------------------

-- C. USING(Common Columns) or ON <Join Condition> ( == Oracle Equal Join ) (***)

-- -------------------------------------------

-- The same as Oracle Equal Join

-- with explicit columns manually determined.

-- -------------------------------------------

-- 가. Natural Join 에서 발생했엇던, 두 개 이상의 공통컬럼에 의해

--    발생가능한 엉뚱한 결과를 예방하기 위해, 명시적으로 조인할 컬럼

--    을 지정하는 방식의 조인

-- 나. Natural Join 과 마찬가지로, 두 테이블의 공통컬럼을 SELECT

--    절에 나열시, 테이블 별칭(Table Alis)이나 테이블명을 앞에

--    붙이는 경우, 오류발생

-- 다. USING(Common Columns):

--    반드시 공통컬럼 값이 일치하는 동등조인(Equal Join) 형식으로

--    실행된다.

-- 라. ON <Join condition>:

--    Non-equal Join 이나, 임의의 조건으로 Join 할 경우에 사용

-- -------------------------------------------

-- Basic Syntax1) USING(Common Columns):

--  FROM절에, [INNER] JOIN / USING 키워드 사용

--

--  SELECT 테이블1.컬럼 , 테이블2.컬럼

--  FROM 테이블1 [INNER] JOIN 테이블2 USING(공통컬럼)

--  [WHERE 검색조건];

-- -------------------------------------------

-- Basic Syntax2) ON <Join condition>:

--  FROM절에, [INNER] JOIN / ON 키워드 사용

--

--  SELECT 테이블1.컬럼 , 테이블2.컬럼

--  FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건

--  [WHERE 검색조건];

-- -------------------------------------------

---

-- -------------------------------------------

-- 1 ) USING(Common Column1, Common Column1, ..)

-- -------------------------------------------

-- a. 반드시 공통컬럼값이 일치하는 동등조인(Equal Join) 형식으로 실행

-- b. column part of USING clause cannot have qualifier ( 공통칼럼은 컬럼 식별자 사용 불가능 )

-- -------------------------------------------

SELECT

    t1.last_name,

    t2.department_name,

    department_id

FROM

    employees t1 INNER JOIN departments t2

    USING(department_id);

-- 오라클의 동등조인과 같은 역할을 USING(department_id)가 해주기에 department_id 칼럼이 동등조인이 된다.

-- USING 절에 기재한 공통칼럼의 중복이 제거되는 것이다. ( USING 안에 적힌 공통칼럼은 식별자를 사용해서는 안된다. )

-- INNER JOIN에서 INNER은 생략이 가능하다.

-- INNER JOIN을 사용하면 정확한 결과가 나온다.

-- 테이블의 별칭이 생성되었으면, 별칭을 사용해야지 테이블의 이름을 사용해서는 안된다.

-- USING절에 사용하지 않는 공통칼럼의 경우에는 식별자로 구분해야 한다. (****)

SELECT

    last_name,

    department_name,

    -- 두 테이블의 공통칼럼은 식별자를 가질 수 없다.

    department_id,

    t1.manager_id

FROM

    employees t1 INNER JOIN departments t2

    USING(department_id)

WHERE

    department_id = 90;

-- Oracle Join과 다르게 ANSI의 JOIN은 공통칼럼이란 이름이 같은 칼럼을 의미한다.

-- USING 절에는 SELECT절에 적힌 칼럼중 공통칼럼은 모두 작성해야 한다.

-- USING절에 사용하지 않는 공통칼럼의 경우에는 식별자로 구분해야 한다. (****)

 

---

-- -------------------------------------------

-- D. JOIN ~ ON

-- -------------------------------------------

SELECT

    t1.last_name,

    t2.department_name,

    t1.department_id

FROM

    employees t1 INNER JOIN departments t2

    ON t1.department_id = t2.department_id;

-- ON절을 통해서 명시적으로 조인조건을 지정한다.

SELECT

    last_name,

    department_name,

    t1.department_id

FROM

    employees t1 INNER JOIN departments t2

    ON t1.department_id = t2.department_id

WHERE

    t1.department_id = 90;

-- ON절은 명시적으로 조건을 작성할 때 사용한다.

SELECT

    last_name,

    department_name,

    t1.department_id

FROM

    employees t1 INNER JOIN departments t2

    ON t1.department_id = t2.department_id

    AND t1.department_id = 90;

-- WHERE절에 작성하였던 검색조건을 ON절에 적어도 정상적으로 출력은 가능하지만, 가독성이 떨어진다.

-- ON절을 활용한 self join

SELECT

    e.last_name AS 사원명,

    m.last_name AS 관리자명

FROM

    employees e INNER JOIN employees m

    ON e.manager_id = m.employee_id;

-- ANSI에서도 self join은 테이블을 복제하여 각각의 별칭을 붙여준 후 사용하게 된다.

-- 단, self join은 2개의 테이블의 속성과 성질이 동일하지만 목적이 다르다.

-- 3개의 테이블 조인

SELECT

    e.last_name AS 사원명,

    d.department_name AS 부서명,

    g.grade_level AS 등급

FROM

    employees e INNER JOIN departments d

    -- ON e.department_id = d.department_id

    USING(department_id)

    INNER JOIN job_grades g

    ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

-- Equal 조인은 ON절이나 USING절 중 선택해서 사용해도 괜찮으나, Non-Equal조인은 ON절에서만 가능하다.(***)

 

---

-- -------------------------------------------

-- E. { LEFT | RIGHT | FULL } OUTER JOIN

-- ---------------------------------------------

--  The same as Oracle Outer Join.

--

-- 가. Oracle Outer Join에서는, (+) 연산자 사용

--     반드시, 한 쪽 테이블에서만 사용가능

-- 나. ANSI Outer Join에서는, LEFT / RIGHT / FULL 키워드 사용

--     어느 한 쪽 테이블 또는 양 쪽 테이블에서 모두 사용가능

-- 다. LEFT OUTER JOIN :

--      LEFT로 지정된 테이블1의 데이터를, 테이블2의 조인조건의

--      일치여부와 상관없이 모두 출력

-- 라. RIGHT OUTER JOIN :

--      RIGHT로 지정된 테이블2의 데이터를, 테이블1의 조인조건의

--      일치여부와 상관없이 모두 출력

-- 마. FULL OUTER JOIN :

--      LEFT OUTER JOIN + RIGHT OUTER JOIN

--      양쪽 테이블의 데이터를, 조인조건 일치여부와 상관없이 모두 출력

-- 바. Oracle Outer Join 보다 향상

-- 사. 조인조건 명시할 때, ON절 또는 USING절 사용가능

-- -------------------------------------------

-- Basic Syntax)

--

--  SELECT 테이블1.컬럼 , 테이블2.컬럼

--  FROM 테이블1 { LEFT|RIGHT|FULL } OUTER JOIN 테이블2

--  ON 조인조건 | USING(컬럼)

--  [WHERE 검색조건];

-- -------------------------------------------

SELECT

    e.last_name AS 사원명,

    m.last_name AS 관리자명

FROM

    -- 일치하는 데이터가 없는 테이블의 별칭이 e를 가진 Left이기 때문에,

    -- Left Outer Join을 지정하고 ON절을 사용하여 조인조건 지정

    employees e  LEFT OUTER JOIN employees m

    -- employees e  LEFT JOIN employees m

    -- employees e  RIGHT OUTER JOIN employees m

    -- employees e  FULL OUTER JOIN employees m

    ON e.manager_id = m.employee_id;

-- OUTER은 생략이 가능하다.

-- FULL OUTER JOIN(196)의 결과셋에서 동등조인했을때와 비교해 늘어난 수는, LEFT OUTER JOIN(107)과 RIGHT OUTER JOIN(195)할때 동등조인에 비해 늘어난 수를 합친것과 같다.

-- 즉, 동등조인했을 때에는 106개가 출력이 되었는데, Left에서 107개가 출력되어 1개가 늘었고, Right에서 196이 나와 89개 늘어났다.

-- 이때 FULL을 보면 동등조인과 비교해 1 + 89인 90개가 늘은것을 알 수 있다.

 

---

-- ------------------------------------------------------

-- 2. Sub-query (부속질의) (***)

-- ------------------------------------------------------

--  가. Join은 한 개 이상의 테이블에서, 원하는 데이터를 조회

--  나. 서브쿼리(=부속질의)는, 하나의 SELECT 문장 만으로 원하는

--      데이터를 조회할 수 없을 때 사용하는 방법

--  다. 한 개 이상의 SELECT문장을, 하나로 합쳐서, 하나의 실행가능

--      한 문장으로 만들어, 원하는 데이터를 조회하는 방법

--  라. 종류:

--      (1) 상관 부속질의

--          - 서브쿼리(=부속질의)의 단독 수행이 불가능한 경우

--          - 메인쿼리의 데이터가 있어야 실행가능한 경우로,

--          - 메인쿼리가 먼저 수행되고, 차례대로 서브쿼리가 수행됨

--          - 메인쿼리의 결과가 서브쿼리에 전달되어, 서브쿼리 수행

--      (2) 비상관 부속질의

--          - 서브쿼리(=부속질의)의 단독 수행이 가능한 경우로,

--          - 서브쿼리가 먼저 수행된 다음에, 메인쿼리가 수행됨

--          - 서브쿼리의 결과가 메인쿼리에 전달되어 실행됨

--  마. 사실상, 서브쿼리(=부속질의)는, SELECT 문의 모든 절(clause)

--      에서 뿐만 아니라, 모든 DML문장에서도 사용가능하다!!! (*주의*)

--  사. 반드시 소괄호()를 사용해야 함.

--  아. 구분: 서브쿼리가 실행되어 반환된 행의 개수에 따라,

--      (1) 단일 행 서브쿼리

--          - 서브쿼리 실행 결과가 한 개의 행 반환.

--          - 메인쿼리에서 사용가능 연산자:

--              *** 비교 연산자 (=, !=, <, >, <=, >=) ***

--      (2) 복수 행 서브쿼리

--          - 서브쿼리 실행 결과가 복수 개의 행 반환.

--          - 메인쿼리에서 사용가능 연산자:

--              *** IN, ANY, ALL, EXISTS 등 ***

--  자. 서브쿼리 사용시, **가장 주의해야** 할 점: 사용할 연산자

--      사용된 연산자에 따라, 단일이나 복수 행 반환

-- ------------------------------------------------------

-- Basic Syntax)

--

--  SELECT select_list       -- 메인쿼리(바깥쪽 SELECT 문장) (***)

--  FROM 테이블

--  WHERE 컬럼명 연산자 (

--      SELECT select_list FROM 테이블    -- 서브쿼리(안쪽 SELECT 문장) (***)

--  );

-- ------------------------------------------------------

-- ------------------------------------------------------

-- 사원정보에서, 'Whalen'보다 많은 월급을 받는 사원조회 #1

-- ------------------------------------------------------

-- * 하나의 SELECT문장만으로는 조회불가:

--   가. 먼저, 'Whalen'의 월급이 얼마나인지 알아야 함. (SELECT 1 - Whalen의 월급이 얼마인지 모르기에)

--   나. 조회한 'Whalen'의 월급보다 많은 월급을 받는 사원조회가능 (SELECT 2)

-- ------------------------------------------------------

-- SELECT 1

SELECT

    salary

FROM

    employees

WHERE

    last_name = 'Whalen';       -- 1차 필터링

-- 이를 통해서 Whalen의 월급이 4400임을 알 수 있다.

-- SELECT 2

SELECT

    last_name,

    salary

FROM

    employees

WHERE

    salary >= 4400;

-- select1에서 구한 월급인 4400을 기준으로 월급이 4400이상인 직원이 61명임을 알 수 있다.

-- ------------------------------------------------------

-- 사원정보에서, 'Whalen'보다 많은 월급을 받는 사원조회 #2 ( 서브쿼리 활용 )

-- ------------------------------------------------------

-- 위 #1과 같이, 하나 이상의 SELECT 문장이 있어야만, 원하는 결과

-- 를 조회가능한 경우에, 서브쿼리(=부속질의) 사용가능.

--

-- 즉, 서브쿼리는, 여러 번의 SELECT 문장을 수행해야 얻을 수 있는

-- 결과를, 하나의 중첩된(nested) SELECT 문장으로 만들어, 원하는

-- 결과를 쉽게 조회가능하게 함. (***)

-- ------------------------------------------------------

SELECT                  -- 메인 쿼리

    last_name,

    salary

FROM

    employees

WHERE

    salary >= (        -- 서브 쿼리

        SELECT

            salary

        FROM

            employees

        WHERE

            last_name = 'Whalen'

    );

-- 서브쿼리 ( = 비상관 부속질의 ) : 단독수행에 지장이 없으며, Whalen의 월급이 메인쿼리에 전달된다. (**)

-- 서브쿼리는 값을 1개만 발생해야 한다.

-- 서브쿼리의 유형 : 1. 비상관 부속질의(메인쿼리보다 먼저 수행) 2. 상관 부속질의(메인쿼리가 먼저 수행)

-- "서브쿼리만" 단독으로 보았을 때, 서브쿼리가 따로 작동이 가능하면 비상관 부속질의라고 한다. (**) <- 서브쿼리가 먼저 수행된다.

 

---

-- ------------------------------------------------------

-- 3. 단일 행 Sub-query (부속질의)

-- ------------------------------------------------------

-- 가. 하나의 행을 반환

-- 나. 반드시 단일 행 서브쿼리를 사용해야만 하는 경우:

--      - 기본키(Primary Key)를 이용하는 경우

--      - 그룹함수(MAX, MIN, SUM 등)를 이용하는 경우

-- ------------------------------------------------------

-- (1) 평균 월급여보다 많은 월급을 받는 사원조회 (단일 서브쿼리)

-- ------------------------------------------------------

SELECT

    last_name,

    salary,

    ( SELECT avg(salary) FROM employees ) AS 평균급여

FROM

    employees

WHERE

    -- 메인쿼리 : 단일행 서브쿼리가 사용되었으므로, 단일값과 비교가능한 비교연산자 사용가능

    salary >= (

        -- 단일행 비상관 서브쿼리 : 모든 사원의 평균 월급을 메인쿼리에 반환

        SELECT

            avg(salary)

        FROM

            employees

    );

-- 서브쿼리의 위치는 자유로운 편이기에 WHERE절에만 나오는 것이 아니라, SELECT절에도 나올 수 있다.

 

---

-- ------------------------------------------------------

-- 4. 복수 행 Sub-query (*****)

-- ------------------------------------------------------

-- 가. 하나 이상의 행을 반환

-- 나. 메인쿼리에서 사용가능한 연산자는 아래와 같음:

--      (1) IN : 메인쿼리와 서브쿼리가 IN 연산자로 비교수행.

--               서브쿼리 결과값이 복수 개인 경우에 사용.

--      (2) ANY : 서브쿼리에서, > 또는 < 같은 비교 연산자를 사용하고자

--                할 때 사용. 검색조건이 하나라도 일치하면 참.

--      (3) ALL : 서브쿼리에서 > 또는 < 같은, 비교 연산자를 사용하고자

--                할 때 사용. 검색조건의 모든 값이 일치하면 참.

--      (4) EXISTS : 서브쿼리의 반환값이 존재하면, 메인쿼리를 실행하고,

--                  반환값이 없으면 메인쿼리를 실행하지 않음.

-- ------------------------------------------------------

---

-- ------------------------------------------------------

-- (1) IN 연산자 사용한 복수행 서브쿼리

-- ------------------------------------------------------

--  가. 서브쿼리 반환값이 복수 개.

--  나. 메인쿼리와 동등비교연산자(=) 방식으로 비교할 때 사용.

-- ------------------------------------------------------

-- 'Whalen' or 'Fay'와 동일한 월급여를 받는 사원조회

SELECT

    last_name,

    salary

FROM

    employees

WHERE

    salary IN(

        SELECT

            salary

        FROM

            employees

        WHERE

            last_name IN ('Whalen', 'Fay')

    );

-- 서브쿼리와 IN연산자를 활용해여 Whalen이나 Fay와 같은 월급을 받고 있는 직원을 출력하게 하였다.

-- 메인쿼리에서 복수행 서브쿼리가 사용되었으므로, 복수값과 연산가능한 연산자 IN이 사용가능하다.

-- 비교 연산자의 경우에는 단일행 서브쿼리를 사용해야 한다.

-- 월 급여를 13000 보다 많이 받는 부서에 속한 모든 사원 조회

SELECT

    last_name,

    department_id,

    salary

FROM

    employees

WHERE

    department_id IN (

        SELECT

            department_id

        FROM

            employees

        WHERE

            salary > 13000

    );

-- 메인쿼리에서 복수행 서브쿼리가 사용되었기에 복수 값과 연산가능한 연산자 사용이 가능하다.

 

---

-- ------------------------------------------------------

-- (2) ALL 연산자 사용한 복수행 서브쿼리

-- ------------------------------------------------------

--  가. 서브쿼리 반환값이 복수 개.

--  나. 메인쿼리에서, < or > 같은 비교연산자 사용하고자 할 때 사용.

--  다. 메인쿼리의 비교연산자는 단일 행 연산자이기 때문에,

--      메인쿼리의 복수행 연산자에 ALL 키워드 없이 사용하면 오류발생.

--  라. ALL 연산자:

--      서브쿼리에서 반환된 전체 행들에 대해서, 조건이 모두(all)

--      만족해야 된다는 것을 의미. (**)

--  마. 구분:

--      (1) > ALL (서브쿼리)

--          - 서브쿼리에서 반환된 모든 데이터보다 큰 데이터 조회

--          - 결국, 서브쿼리에서 반환된 모든 데이터 중,

--            *최대값보다 큰* 데이터 조회 (***)

--

--      (2) < ALL (서브쿼리)

--          - 서브쿼리에서 반환된 모든 데이터보다 작은 데이터 조회

--          - 결국, 서브쿼리에서 반환된 모든 데이터 중,

--            *최소값보다 작은* 데이터 조회 (***)

-- ------------------------------------------------------

-- 직책이 IT_PROG인 사원 월급 조회

SELECT

    salary

FROM

    employees

WHERE

    job_id = 'IT_PROG'

ORDER BY

    salary ASC;

-- 직책이 IT_PROGS인 직원의 월급을 오름차순으로 정렬하였다.

-- 직책이 IT_PROG인 사원의 월급보다 *작은* 사원 정보 조회

SELECT

    last_name,

    department_id,

    salary

FROM

    employees

WHERE

    salary < ALL (  -- 최소값 보다 작은 직원 필터링

        SELECT

            salary

        FROM

            employees

        WHERE

            job_id = 'IT_PROG'

    )

ORDER BY

    salary DESC;

-- < ALL의 의미는 오른쪽 ( )에 나온 모든 값보다 작은 값을 출력해라라는 의미이다.

-- 즉, IT_PROG의 직원월급 중 가장 작은 직원의 월급을 가져와서 다른 직원과 월급을 비교하게 된다.

-- 직책이 IT_PROG인 사원의 월급보다 *많은* 사원 정보 조회

SELECT

    last_name,

    department_id,

    salary

FROM

    employees

WHERE

    salary > ALL (

        SELECT

            salary

        FROM

            employees

        WHERE

            job_id = 'IT_PROG'

    )

ORDER BY

    salary ASC;

-- 서브쿼리에서 모든 IT_PROG 사원의 월급을 가지고 오지만, 비교 연산자가 >이기에 결국 월급이 가장 큰 직원을 기준으로 필터링하게 된다.

 

---

-- ------------------------------------------------------

-- (3) ANY 연산자 사용한 복수행 서브쿼리

-- ------------------------------------------------------

--  가. 서브쿼리 반환값이 복수 개.

--  나. 서브쿼리에서, < or > 같은 비교연산자 사용하고자 할 때 사용.

--  다. 메인쿼리의 비교연산자는 단일 행 연산자이기 때문에,

--      메인쿼리의 단일행 연산자에 ANY 키워드 없이 사용하면 오류발생.

--  라. ANY 연산자:

--      서브쿼리에서 반환된 전체 행들에 대해서, 조건이 하나라도(any)

--      만족해야 된다는 것을 의미.

--  마. 구분:

--      (1) > ANY (서브쿼리)

--          - 서브쿼리에서 반환된 모든 데이터보다 큰 데이터 조회

--          - 결국, 서브쿼리에서 반환된 모든 데이터 중,

--            *최소값보다 큰* 데이터 조회

--

--      (2) < ANY (서브쿼리)

--          - 서브쿼리에서 반환된 모든 데이터보다 작은 데이터 조회

--          - 결국, 서브쿼리에서 반환된 모든 데이터 중,

--            *최대값보다 작은* 데이터 조회

-- ------------------------------------------------------

-- 직책이 IT_PROG인 사원의 *최소 월급여보다 큰* 사원 정보 조회

SELECT

    last_name,

    department_id,

    salary

FROM

    employees

WHERE

    salary > ANY (

        SELECT

            salary

        FROM

            employees

        WHERE

            job_id ='IT_PROG'

    )

ORDER BY

    salary ASC;

-- ALL과는 다르게 IT_PROG에 속한 직원의 월급 중 1명의 월급이라도 자신이 월급이 보다 크면 출력한다.

-- 즉, 복수행 서브쿼리가 반환한 모든 값 중에서 최소값보다 큰 사원을 필터링하게 된다. (***)

-- 직책이 IT_PROG인 사원의 *최대 월급여보다 작은* 사원 정보 조회

SELECT

    last_name,

    department_id,

    salary

FROM

    employees

WHERE

    salary < ANY (

        SELECT

            salary

        FROM

            employees

        WHERE

            job_id = 'IT_PROG'

    )

ORDER BY

    salary DESC;

-- 이 경우에는 IT_PROG의 최대값보다 작은 사원을 필터링하게 된다.

-- ALL은 최대값보다 크거나, 최솟값보다 작은 값을 ANY는 최대값보다 작거나, 최소값보다 큰 값을 출력한다. (******)