Bohemian life

- 아래 두 쿼리를 결합하고 싶어요.

    : 문제는 두 쿼리의 컬럼 개수는 같으나 데이터 타입이 다릅니다.

SELECT department_id, hire_date

FROM   employees;

SELECT department_id, location_id

FROM   departments;

↓ ↓ ↓

SELECT department_id, hire_date, to_number(null) as location

FROM   employees

UNION

SELECT department_id, to_date(null), location_id

FROM   departments;

MINUS

MINUS는 첫 번째 SELECT문에 의해 반환되는 행 중에서 두 번째 SELECT문에 의해 반환되는 행에 존재하지 않는 행들을 반환 한다.

INTERSECT

INTERSECT는 두 행의 집합중 공통된 행을 반환 한다.

UNION ALL

UNION과 같으나 두 테이블의 중복되는 값 까지 반환 한다.

UNION

UNION은 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환 한다.

 -  정리해 주세요

  -

  - ORDER BY 절은 집합 연산자를 적용한 쿼리의 제일 마지막에 나와야 합니다.

  - UNION ALL만 중복 허용

  - 결과의 컬럼 헤딩은 첫번째 select-list의 이름이 표시됩니다.

  - UNION ALL을 제외하고 기본적으로 오름차순으로 정렬

UNION Operator

아무런 상관관계가 없는 테이블을 위아래로 붙히는것
열숫자가 같아야하고 데이터타입이 같아야함

select
employee_id,job_id
from employees
union                    <--중복제거 all 넣으면 중복허용  UNION all
select
employee_id,job_id
from job_history
/

INTERSECT Operator

select
employee_id, job_id
from employees
intersect
select
employee_id, job_id
from job_history
/

MINUS Operator

select
employee_id, job_id
from employees
minus
select
employee_id, job_id
from job_history
/

--------------  열을맞추기위해 데이터타입이같은 null값열을 만들어 이용할때
select
department_id
,to_number(null) location
,hire_date
from employees
union
select
department_id
,location_id
,to_date(null)
from departments
/

------------------ 유격이안맞을때  0을이용해서 붙힐때
select
employee_id
,job_id
,salary
from employees
union
select 
employee_id
,job_id
,0
from job_history
/

----------------------- 유니온 2개 3개를 합치는것

COLUMN a_dummy NOPRINT -> a_dummy 열을 출력하지말아라.
SELECT 'sing' AS "My dream", 3 a_dummy
FROM dual
UNION
SELECT 'I''d like to teach', 1 a_dummy
FROM dual
UNION
SELECT 'the world to', 2 a_dummy
FROM dual
ORDER BY a_dummy;

 문제) 입사가 가장 빠른 5명?

    select *

    from (select *

          from emp

          order by hiredate)

    where rownum <= 5;

  문제) 입사가 가장 빠른 5명 이후의 입사자들

    select *

    from (select *

          from emp

          order by hiredate)

    where rownum > 5;

                  --> 엉터리

    select rownum, e.*

    from emp e

    order by hiredate;

↓ ↓ ↓ 

    select rownum, e.*

    from (select *

  from emp

  order by hiredate) e;

↓ ↓ ↓ 

    select *

    from (select rownum as no, e.*

          from (select *

        from emp

        order by hiredate) e

         )

    where no > 5;

  문제) employees 테이블의 부서 중에서 평균 급여가 가장 적은 부서는?

    방법 1) subquery + having

  select min(avg(salary))

  from employees

  group by department_id;

      ↓ ↓

  select department_id, avg(salary)

  from employees

  group by department_id

  having avg(salary) = (select min(avg(salary))

from employees

group by department_id);

    방법 2) subquery + rownum

  select *

  from (select department_id, avg(salary)

   from employees

   group by department_id

   order by 2)

  where rownum = 1;

문제) 월급 상위 3명?

  select *

  from emp

  where rownum <= 3

  order by sal desc;   --> 엉터리 (select max(sal) from emp; ==> 5000)

     ↑ ↑

1)  select rownum, e.*

    from emp e;

2)  select rownum, e.*

    from emp e

    where rownum <= 3;

3)  select rownum, e.*

    from emp e

    order by sal desc;

4)  select rownum, e.*

    from emp e

    where rownum <= 3

    order by e.sal desc;

==> 정렬 이후가 아니라 정렬 한참 전인 WHERE 절을 통과한 순서대로 행 순서(ROWNUM)가 정해짐

    select rownum as no, e.*

    from emp e

    order by e.sal desc;

==> rownum이 먼저 결정되고 난 뒤 정렬이 이루어집니다.

   따라서 서브쿼리에서 rownum을 쓸 이유가 없습니다.

  해결책) 서브쿼리내에서 order by 사용하고 메인쿼리의 WHERE 절에서 rownum 사용

    select *

    from (select * -- in-line view

          from emp

          order by sal desc)

    where rownum <= 3;

# Pseudocolumn ==> http://goo.gl/NKgL8  참고하세요.

   : behaves like a table column, but is not actually stored in the table

  select rownum as no, empno, ename, sal

  from emp;

  select rownum as no, empno, ename, sal

  from emp

  where rownum <= 3;

  select rownum as no, rownum*2 as no2, rownum*10 as no3

  from emp

  where rownum <= 3;

  select rownum, rowid, empno, ename, sal

  from emp;

  SELECT salary

  FROM   employees

  WHERE  job_id = 'IT_PROG';

  SELECT employee_id, last_name, job_id, salary

  FROM   employees

  WHERE  salary < ANY (SELECT salary

                       FROM   employees

                       WHERE  job_id = 'IT_PROG')

  AND    job_id <> 'IT_PROG';

        salary <> ALL (9000, 6000, 4200)

        ==>     (salary <> 9000)   ==> salary NOT IN (9000, 6000, 4200)

            AND (salary <> 6000)

            AND (salary <> 4200)

  * 만약에 서브쿼리의 리턴 값 중에 null이 하나라도 있다면 <>ALL은 어떻게 될까요?

     => 조건이 N, F이 됩니다. 

     => 로우가 하나도 리턴되지 않습니다.

    salary := 8000 이라면

        where salary <> ALL (9000, null, 4200)

               ↓ ↓

        where salary NOT IN (9000, null, 4200) 

               ↓ ↓

        where (salary <> 9000) AND (salary <> null) AND (salary <> 4200)

                       T                    N                     T

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

  drop table t1 purge;

  drop table t2 purge;

  create table t1 (col1 number);

  insert into t1 values (1000);

  insert into t1 values (2000);

  insert into t1 values (3000);

  create table t2 (col1 number);

  insert into t2 values (1500);

  insert into t2 values (2500);

  commit;

  select * from t1

  where col1 > all (select col1 from t2);

         ↓ ↓

  select * from t1

  where col1 > all (1500, 2500);

  select * from t1

  where col1 > (select max(col1) from t2);

      ==> >ALL은 more than the maximum

SELECT last_name, salary, department_id

   FROM   employees

   WHERE  salary IN (SELECT   MIN(salary)

                     FROM     employees

                     GROUP BY department_id);

            ↓ ↓

   SELECT last_name, salary, department_id

   FROM   employees

   WHERE  salary IN (  7000,

     17000,

      6000,

      8300,

      2500,

      8600,

      4200,

      4400);

    * deptno IN (10, 20, 30)     === (deptno = 10) OR (deptno = 20) OR (deptno = 30)

      deptno NOT IN (10, 20, 30) === (deptno <> 10) AND (deptno <> 20) AND (deptno <> 30)

  cf.) 부서별 최소 급여 사원

SQL> SELECT last_name, salary, department_id

  2  FROM   employees

  3  WHERE  (department_id, salary) IN (SELECT   department_id, MIN(salary)

  4                                     FROM     employees

  5                                     GROUP BY department_id);

SELECT employee_id, last_name

FROM   employees

WHERE  salary = (SELECT MIN(salary)

FROM     employees

GROUP BY department_id);

==> 하나가 아닌 여러 행이 리턴되므로 =(single-row operator)를 사용할 수 없습니다.

SELECT   MIN(salary)

FROM     employees

GROUP BY department_id;

* 해결책: =을 in으로 변경

Having clause

select department_id, min(salary)

from employees

group by department_id;

↓ ↓ ↓

select department_id, min(salary)

from employees

group by department_id

having min(salary) > (select min(salary)

                     from employees

                     where department_id = 20);