Bohemian life

SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e FULL OUTER JOIN departments d

  ON   (e.department_id = d.department_id) ;

      ↓ ↓

<Oracle Syntax>

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e, departments d

  WHERE  e.department_id (+) = d.department_id (+);

SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e RIGHT OUTER JOIN departments d

  ON   (e.department_id = d.department_id) ;

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e JOIN departments d

  ON   (e.department_id = d.department_id) ;

     ==> 위의 OUTER JOIN과 출력결과를 비교해 보세요.

      ↓ ↓

<Oracle Syntax> ==> outer join할 때는 (+) 기호를 WHERE 조건에 사용합니다.

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e, departments d

  WHERE  e.department_id (+) = d.department_id;

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e LEFT OUTER JOIN departments d

  ON   (e.department_id = d.department_id) ;

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e JOIN departments d

  ON   (e.department_id = d.department_id) ;

     ==> 위의 OUTER JOIN과 출력결과를 비교해 보세요.

      ↓ ↓

  <Oracle Syntax> ==> outer join할 때는 (+) 기호를 WHERE 조건에 사용합니다.

  SELECT e.last_name, e.department_id, d.department_name

  FROM   employees e, departments d

  WHERE  e.department_id = d.department_id (+);

; 조인 조건이 동등 조건이 아닌 조인 방식

  set lines 120

  select *

  from emp e join salgrade g

  on e.sal between g.losal and g.hisal;

      ↓ ↓

<Oracle Syntax>

  select *

  from emp e, salgrade g

  where e.sal between losal and hisal;

  SELECT employee_id, city, department_name

  FROM   employees e 

  JOIN   departments d

  ON     d.department_id = e.department_id 

  JOIN   locations l

  ON     d.location_id = l.location_id;

      ↓ ↓

<Oracle Syntax>

  SELECT employee_id, city, department_name

  FROM   employees e, departments d, locations l

  WHERE  d.department_id = e.department_id 

  AND    d.location_id = l.location_id;

SELECT e.employee_id, e.last_name, e.department_id, 

         d.department_id, d.location_id

  FROM   employees e JOIN departments d

  ON     (e.department_id = d.department_id)

  AND    e.manager_id = 149 ;

  ==> AND 대신 WHERE를 써도 됩니다.

쉬어가는 문제) 부서별 평균급여와 부서이름을 출력하는 쿼리를 작성하세요.

- Join On 사용

  select e.empno, e.ename, m.empno, m.ename

  from emp e

  join emp m

  on (e.mgr = m.empno);

↓ ↓ ↓

<Oracle Syntax>

  select e.empno, e.ename, m.empno, m.ename

  from emp e, emp m

  where e.mgr = m.empno;

select e.employee_id, e.last_name, e.department_id,

         d.department_name, d.location_id

  from employees e 

  join departments d

  on (e.department_id = d.department_id);

↓ ↓ ↓

<Join Using>

  select e.employee_id, e.last_name, department_id,

         d.department_name, d.location_id

  from employees e 

  join departments d

  using (department_id);

↓ ↓ ↓

<Oracle Syntax>

  select e.employee_id, e.last_name, e.department_id,

         d.department_name, d.location_id

  from employees e, departments d

  where e.department_id = d.department_id;

- benefits

1. 쿼리를 심플하게

2. 성능 향상 - 1) 조인 조건에 해당하는 컬럼을 빨리 찾을 수 있음

2) 메모리 절약

       3) 네트워크 부하 감소

  - Guideline

    ①

    ② 테이블 별칭을 사용하면 select 문에서 테이블 이름과 연계하여 컬럼을 구분할 때

       테이블 이름 대신 반드시 테이블 별칭을 써야 합니다. 

select emp.empno, e.ename, d.dname --> ORA-00904: "EMP"."EMPNO": 부적합한 식별자

from emp e join dept d

on e.deptno = d.deptno;

    ③

    ④

- 테이블 이름을 컬럼이름과 함께 나열

--> 테이블 별칭(alias)가 FROM 절에 정의될 경우 식별자(qualifier)로 

   테이블 이름 대신 테이블 별칭을 반드시 써야 합니다.

  - natural join에서 조인에 자동으로 사용된 컬럼과

    using 절에 사용된 컬럼에는 식별자(테이블 이름)을 붙이지 마세요.

select department_id, department_name, location_id, city

  from departments 

  join locations

  using (location_id);

<Oracle Syntax>

  select department_id, department_name, departments.location_id, city

  from departments, locations

  where departments.location_id = locations.location_id;

  cf.)  natural join, join using 에선 

조인대상이 되는 컬럼에 테이블 이름을 쓸 수 없습니다.

  select department_id, department_name, departments.location_id, city

  from departments

  natural join locations;

  select department_id, department_name, departments.location_id, city

  from departments 

  join locations

  using (location_id);

  select department_id, department_name, location_id, city

  from departments 

  join locations

  using (departments.location_id);

: 이름과 타입이 같은 모든 컬럼을 기준으로 조인을 수행

  : 문제점 (무조건 같은 이름을 기준으로 조인)

    - 속도가 느리다

    - 원치 않는 조인이 이루어질 가능성이 있다.

  : 문제해결 -> join using, join on을 사용

  select department_id, department_name, location_id, city

  from departments

  natural join locations;

<Oracle Syntax>

  select department_id, department_name, departments.location_id, city

  from departments, locations

  where departments.location_id = locations.location_id;

select department_id, department_name, location_id, city

from departments

natural join locations

where department_id in (20, 50);

↓ ↓ ↓

select department_id, department_name, departments.location_id, city

from departments, locations

where departments.location_id = locations.location_id --> join predicate

and   department_id in (20, 50); --> non-join predicate

• Join - Inner Join - Natural joins    『               

                             - Join Using          =>equijoin

     - Join On                             』

  - Outer Join - Left Join

  - Right Join

  - Full Join

  - Cross Join

• Join Predicate - equi Join 동등(=) 

                          - non equi Join 비동등(>,<....)

※Oracle join 문법

From T1,T2

Where 조인조건  and 조인조건

and 비조인조건

 ①From T1 Natural Join T2

 ②From T1 Join T2 Using(컬럼 이름)

 ③From T1 Join T2 ON(조인 조건)

 ④From employees e left outer join departments d

문제) 부서별 급여합을 아래와 같이 가로 형태로 구하기

    TOTAL        D10        D20        D30        D40

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

    29025       8750      10875       9400          0

  해답)

select deptno, sal, sal, sal, sal, sal

from emp;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , sum(sal) as d10, sum(sal) as d20, sum(sal) as d30, sum(sal) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , decode(deptno, 10, sum(sal)) as d10

    , sum(sal) as d20, sum(sal) as d30, sum(sal) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select deptno, sum(sal) as total

    , decode(deptno, 10, sum(sal)) as d10

    , decode(deptno, 20, sum(sal)) as d20

    , decode(deptno, 30, sum(sal)) as d30

    , decode(deptno, 40, sum(sal)) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select sum(sum(sal)) as total

    , max(decode(deptno, 10, sum(sal))) as d10

    , max(decode(deptno, 20, sum(sal))) as d20

    , max(decode(deptno, 30, sum(sal))) as d30

    , max(decode(deptno, 40, sum(sal))) as d40

from emp

group by deptno;

↓ ↓ ↓ 

select sum(sum(sal)) as total

    , nvl(max(decode(deptno, 10, sum(sal))), 0) as d10

    , nvl(max(decode(deptno, 20, sum(sal))), 0) as d20

    , nvl(max(decode(deptno, 30, sum(sal))), 0) as d30

    , nvl(max(decode(deptno, 40, sum(sal))), 0) as d40

from emp

group by deptno;

  문제) 부서별 직무별 급여합 구하기 (아래와 같이 나타내시오.)

JOB            TOTAL        D10        D20        D30        D40

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

ANALYST         6000          0       6000          0          0

CLERK           4150       1300       1900        950          0

MANAGER         8275       2450       2975       2850          0

PRESIDENT       5000       5000          0          0          0

SALESMAN        5600          0          0       5600          0

 cf.) select deptno, job, sum(sal)

from emp

group by deptno, job

  select job, nvl(decode(deptno, 10, sal), 0) as d10

            , nvl(decode(deptno, 20, sal), 0) as d20

            , nvl(decode(deptno, 30, sal), 0) as d30

            , nvl(decode(deptno, 40, sal), 0) as d40

  from emp;

↓ ↓ ↓ 

  select job, sum(nvl(decode(deptno, 10, sal), 0)) as d10

            , sum(nvl(decode(deptno, 20, sal), 0)) as d20

            , sum(nvl(decode(deptno, 30, sal), 0)) as d30

            , sum(nvl(decode(deptno, 40, sal), 0)) as d40

  from emp

  group by job;

  문제) 연도별 입사자수 구하기

    TOTAL      Y1983      Y1982      Y1981   PREVIOUS

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

       14          1          2         10          1

  select to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY'), to_char(hiredate, 'YYYY')

  from emp

  order by 1 desc;

  select to_char(hiredate, 'YYYY'), decode(to_char(hiredate, 'YYYY'), '1983', '1983') as Y1983

                                  , decode(to_char(hiredate, 'YYYY'), '1982', '1982') as Y1982

                                  , decode(to_char(hiredate, 'YYYY'), '1981', '1981') as Y1981

                                  , decode(to_char(hiredate, 'YYYY'), '1983', null, '1982', null, '1981', null, 'Previous') as Prev

  from emp

  order by 1 desc;

  select count(decode(to_char(hiredate, 'YYYY'), '1983', '1983')) as Y1983

       , count(decode(to_char(hiredate, 'YYYY'), '1982', '1982')) as Y1982

       , count(decode(to_char(hiredate, 'YYYY'), '1981', '1981')) as Y1981

       , count(decode(to_char(hiredate, 'YYYY'), '1983', null, '1982', null, '1981', null, 'Previous')) as Prev

  from emp

  order by 1 desc;

oj_SQL.txt

- nested to a depth of two. 

  select max(avg(salary))

  from employees

  group by department_id;

  select department_id, max(avg(salary))

  from employees

  group by department_id;