=========
5장 JOIN문
=========
------------------------------
Oracle Syntax
------------------------------
select *
from emp e, dept d -- Join statement
where e.deptno = d.deptno -- Join predicate
and e.sal >= 2000 -- Non-join predicate
and e.job = 'MANAGER' -- Non-join predicate
and d.deptno = 20 -- Non-join predicate (Single row predicate)
-- Equi join
select *
from emp, dept
order by 1;
-- cartesian product를 만든다.
select *
from emp, dept
where emp.deptno = dept.deptno
order by 1;
-- cartesian product 중에서 emp테이블의 deptno컬럼과,
-- dept테이블의 deptno컬럼이 같은 row만 골라낸다.
select *
from emp e, dept d
where e.deptno = d.deptno and e.sal >= 1500
order by 1;
-- emp테이블에 e라는 테이블 alias를 준다.
-- 조건을 여러개 나열
-- Non-equi join
select *
from emp e, salgrade s
order by 1;
-- cartesian product를 만든다.
select *
from emp e, salgrade s
where e.sal >= s.losal and e.sal <= s.hisal
order by 1;
-- join predicate의 조건이 = 이 아니라 <= < > >= 등이 쓰인 조인문
-- Outer join : 조인 조건에 맞는 결과 + 조건에 맞지 않는 결과
select *
from emp e, dept d
where e.deptno (+) = d.deptno
order by 1;
-- e.deptno 와 d.deptno를 이용하여 두 테이블을 join했다.
-- 그런데 dept테이블에 부서정보는 있는데, 아직 사원이 없다.
-- 그래서 emp테이블에 deptno컬럼에는 사원이 없는 부서의 정보는 없다.
-- 이럴 때, 두 테이블의 한쪽은 모든 데이터가 다 나오고,
-- 다른 한쪽은 join predicate에 만족하는 데이터가 select된다.
-- 이때 모든 데이터가 나오는 테이블이 아닌 나머지 하나의 테이블 쪽에
-- where e.deptno (+) = d.deptno 와 같이 (+)를 추가한다.
select * from departments;
select * from employees;
select e.employee_id, e.department_id, d.department_name from employees e, departments d
where e.department_id = d.department_id (+);
-- departments 테이블과 employees 테이블의 department_id를 중심으로 Join
--> 결과
EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------- ---------------
200 10 Administration
202 20 Marketing
201 20 Marketing
144 50 Shipping
143 50 Shipping
142 50 Shipping
141 50 Shipping
124 50 Shipping
107 60 IT
104 60 IT
103 60 IT
176 80 Sales
174 80 Sales
149 80 Sales
102 90 Executive
101 90 Executive
100 90 Executive
206 110 Accounting
205 110 Accounting
178
select e.employee_id, e.department_id, d.department_name from employees e, departments d
where e.department_id (+) = d.department_id;
-- 위의 결과와 비교
--> 결과
EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------- ---------------
200 10 Administration
201 20 Marketing
202 20 Marketing
124 50 Shipping
144 50 Shipping
143 50 Shipping
142 50 Shipping
141 50 Shipping
107 60 IT
104 60 IT
103 60 IT
174 80 Sales
149 80 Sales
176 80 Sales
102 90 Executive
100 90 Executive
101 90 Executive
205 110 Accounting
206 110 Accounting
Contracting
select e.employee_id, e.department_id, d.department_name from employees e, departments d
where e.department_id (+) = d.department_id (+); -- ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
-- 에러난다. 그럼 다 나오게 하려면?...뒤에 나오는 거 같은데.
-- Self join
select * from emp w, emp m
where w.mgr = m.empno;
-- emp 테이블에 KING은 mgr컬럼이 null이다.
-- 그래서 위 결과에는 KING의 정보는 안나온다.
select * from emp w, emp m
where w.mgr = m.empno (+);
-- outer join을 해야 King의 정보가 select 된다.
# 문제 1 : 다음과 같은 결과가 나타나도록 질의문을 만들어 보세요.
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID CITY STATE_PROVINCE CO
------------- ------------------------------ ---------- ----------- ------------------------------ ------------------------- --
10 Administration 200 1700 Seattle Washington US
20 Marketing 201 1800 Toronto Ontario CA
50 Shipping 124 1500 South San Francisco California US
60 IT 103 1400 Southlake Texas US
80 Sales 149 2500 Oxford Oxford UK
90 Executive 100 1700 Seattle Washington US
110 Accounting 205 1700 Seattle Washington US
190 Contracting 1700 Seattle Washington US
-- 해답
select d.*, l.city, l.state_province, l.country_id
from departments d, locations l
where d.location_id = l.location_id
order by 1;
-- 결과의 첫번째 컬럼을 기준으로 오른차순 정렬
# 문제 2 : PROD_ID가 13인 상품의 이름과 판매 내역이 나타나게 하세요.
PROD_NAME PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
-------------------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------- -----------
... 생략 ...
5MP Telephoto Digital Camera 13 3618 24-DEC-01 4 999 1 848.84
5MP Telephoto Digital Camera 13 19017 24-DEC-01 4 999 1 848.84
6002 rows selected.
-- 해답
conn system/oracle
--또 뭐가 문제인지 system으로 계정변경이 안된다.
grant select on sh.products to public;
grant select on sh.customers to public;
grant select on sh.sales to public;
-- system계정으로 들어가서 sh(<-계정? 인스턴스?)의
-- products, customers, sales 테이블의 권한을 public으로 변경
conn test/test
-- 다시 test(실습하고 있는 계정)으로 변경
select p.prod_name, s.*
from sh.products p, sh.sales s
where p.prod_id = 13
and p.prod_id = s.prod_id;
# 문제 3 : CUST_ID가 987인 고객의 이름과 구매 내역이 나타나게 하세요.
CUST_ID CUST_LAST_NAME PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------------------------------------- ---------- ---------- ------------ ---------- ---------- ------------- -----------
... 생략 ...
987 Raimey 30 987 23-FEB-99 3 999 1 10.79
987 Raimey 31 987 23-FEB-99 3 999 1 9.71
180 rows selected.
-- 해답
select c.cust_id, c.CUST_LAST_NAME, s.*
from sh.customers c, sh.sales s
where c.cust_id = s.cust_id
and c.cust_id = 987;
------------------------------
SQL:1999 Syntax
------------------------------
-- Cross join
select * from emp e CROSS JOIN dept d;
-- cartesian product를 만듬-- Natural join : 같은 이름의 컬럼을 이용해서 Equi join 수행
select *
from emp e NATURAL JOIN dept d;
-- 우리가 알고있는 일반적인 join문이 된다. deptno를 기준으로 합쳐짐. select *
from emp e NATURAL JOIN dept d
where e.sal >= 2000
order by deptno, empno;
-- natural join으로 join한 테이블에 추가로 where절에 조건을 사용# Natural join의 문제 : 같은 이름의 컬럼이 무조건 모두 조인 조건에 사용된다는 것!
select *
from employees e NATURAL JOIN departments d;
즉, 위의 문장은 아래와 같은 문장으로 처리된다.
select *
from employees e, departments d
where e.department_id = d.department_id
and e.manager_id = d.manager_id;
-- 사원번호가 100인 사람이 90번 부서를 관리하고 101번 사원도 관리하고 101번 사원은 어떤 사람이고, 90번 부서는 1700번 지역에 있다....잉? 이상한 결과가 나왔다.# Natural join의 문제 해결 방법 1 : -- Join Using (column_name)
-- join using(컬럼명) 을 사용하여 이를 해결하겠다. select *
from employees e JOIN departments d USING (department_id);
-- kimberely라는 놈은 아직 부서가 없는 놈이다. 그래서 19건만 select된다.(employees테이블은 총 20건) 즉, outer조인이 필요하다.
-- ... LEFT OUTER JOIN ... 이라고 해야한다.
-- ... NATURAL JOIN ...과 ...USING ...은 같이 사용불가
# Natural join의 문제 해결 방법 2 : -- Join On (condition)
select *
from employees e JOIN departments d ON (e.department_id = d.department_id)
where e.salary >= 3000;
-- ...USING(컬럼명)으로 사용하고, ... ON (조건) 으로 사용 -- 역시 ... NATURAL JOIN ... ON... 은 사용 불가
select *
from emp e JOIN salgrade s ON (e.sal >= s.losal and e.sal <= s.hisal);
select *
from emp w JOIN emp m ON (w.mgr = m.empno);
-- KING은 PRESIDENT라서 KING의 관리자는 없다. OUTER JOIN이 필요
-- [Left|Right|Full] Outer join
select * from employees e, departments d
where e.department_id = d.department_id (+);
-- ORACLE SYNTAX에서는 (+)를 추가하는 위치가 더 출력되어야 하는 곳의 반대편에 붙인다. 즉, 원하는 결과에서 한쪽 테이블의 정보가 부족하게 되는데 그 부족함을 채워준다는 의미로 이해하면 될 듯하다. 하지만 SQL 1999 SYNTAX에서는 OUTER JOIN을 기준으로 왼쪽의 데이터가 더 필요하면 LEFT, 오른쪽의 정보가 더 필요하면 RIGHT 라고 정확하게 명시한다.
select *
from employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
-- 위의 ORACLE SYNTAX와 동일한 결과를 출력한다. -- OUTER JOIN을 기준으로 employees 테이블에 kimbelery의 정보를 더 출력해야하기 때문에 왼쪽 LEFT의 employees정보가 더 필요하다.
select *
from departments d right OUTER JOIN employees e ON (e.department_id = d.department_id);
-- 만약 employees테이블을 OUTER JOIN의 오른쪽에 두면 RIGHT라고 명시한다.
------------
select * from employees e, departments d
where e.department_id (+) = d.department_id;
-- 위에서는 아직 부서에 배정되지 못한 직원에 대한 정보를 OUTER JOIN으로 출력하였다. -- 여기선 아직 직원이 할당되지 않은 부서에 대한 정보를 OUTER JOIN으로 출력함
-- ORACLE SYNTAX
select *
from employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
-- SQL 1999 SYNTAX
------------
select * from employees e, departments d
where e.department_id (+) = d.department_id (+); -- ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
-- 그럼 전체 직원과 전체 부서에 대한 정보를 모두 출력하려면, 즉, 직원이 할당되지 않은 부서와 아직 부서에 배속되지 못한 직원을 모두 출력하려면 ORACLE SYNTAX에서는 양쪽에 (+)를 표시하면 될까?
-- 에러!
select *
from employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id);
-- SQL 1999 SYNTAX에서는 된다! FULL OUTER JOIN 을 사용
# Cartesian product은 항상 나쁘다??!!
select deptno, job, sum(sal)
from emp
group by deptno, job
union all
select deptno, null, sum(sal)
from emp
group by deptno
union all
select null, null, sum(sal)
from emp
order by 1, 2;
-> 내가 수정해본 것
select deptno, job, sum(sal)
from emp
group by deptno, job
union all
select deptno, '부분합', sum(sal)
from emp
group by deptno
union all
select null, '전체합', sum(sal)
from emp
order by 1, 2;
-- 부서별 부분합, 전체합이 구해졌다. 띠용~
--> 결과
DEPTNO JOB SUM(SAL)
------ ------------------ ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 부분합 8750
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 부분합 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 부분합 9400
전체합 29025
↓↓
select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job;
-- 부분합을 구하기 위한 절차select level as no
from dual
connect by level <= 3;
select *
from (select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job) a,
(select level as no
from dual
connect by level <= 3);
-- 똑같은 값을 번호를 붙여서 3번 출력한다.
select decode(no, 1, deptno, 2, deptno) as deptno, job, sum_sal, no
from (select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job) a,
(select level as no
from dual
connect by level <= 3);
-- 1, 2번일때만 deptno를 출력
select decode(no, 1, deptno, 2, deptno) as deptno,
decode(no, 1, job) as job, sum_sal, no
from (select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job) a,
(select level as no
from dual
connect by level <= 3);
-- 1번일때만 job을 출력
-- 즉, 총 3번에서 1번은 deptno와 job을 모두 출력하고, 2번은 deptno만 출력, 3은 sum_sal만 출력
-- (부분합을 구하기 위한) 계층형태로 만들기 위한 준비
select decode(no, 1, deptno, 2, deptno) as deptno,
decode(no, 1, job) as job,
sum(sum_sal)
from (select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job) a,
(select level as no
from dual
connect by level <= 3)
group by decode(no, 1, deptno, 2, deptno), decode(no, 1, job)
order by 1, 2;
-- 부분합과 전체합이 구해짐
select decode(no, 1, deptno, 2, deptno) as deptno,
decode(no, 1, job) as job,
sum(sum_sal)
from (select deptno, job, sum(sal) as sum_sal
from emp
group by deptno, job) a
CROSS JOIN
(select level as no
from dual
connect by level <= 3)
group by decode(no, 1, deptno, 2, deptno), decode(no, 1, job)
order by 1, 2;
-- CROSS JOIN : SQL SYNTAX에서 cartesian product를 표현함
댓글 없음:
댓글 쓰기