-
Notifications
You must be signed in to change notification settings - Fork 10
Expand file tree
/
Copy path20210611_join.sql
More file actions
138 lines (95 loc) · 2.39 KB
/
20210611_join.sql
File metadata and controls
138 lines (95 loc) · 2.39 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
-- 2021.06.11
-- JOIN : 스키마의 합
-- CROSS JOIN
-- 스키마의 합 : 집합의 곱 연산과 같다
select *
from emp, dept
;
-- ansi cross join
select *
from emp cross join dept
;
-- EQUI JOIN
select *
from emp, dept
where emp.deptno=dept.deptno
;
select * from orders;
select * from customer;
select * from book;
select o.orderid, c.name, b.bookname
from orders o, customer c, book b
where o.custid=c.custid and o.bookid=b.bookid
order by o.orderid
;
-- ansi -> inner join
-- 오라클 EQUI JOIN
select *
from emp, dept
where emp.deptno=dept.deptno
;
select *
from emp e inner join dept d
on e.deptno=d.deptno
;
-- 이름이 SCOTT인 사람의 where e.ename='SCOTT'
-- 부서명을 출력해봅시다.
-- 출력해야하는 컬럼을 가지는 테이블을 확인해보자
-- 이름 : emp, 부서명 : dept
select e.ename, d.dname , d.deptno
from emp e, dept d
where e.deptno = d.deptno --and ename='SCOTT'
and e.ename='SCOTT'
order by d.dname, e.ename
;
select *
from emp e inner join dept d
on e.deptno=d.deptno
where e.ename='SCOTT'
;
-- 비교하는 컬럼의 이름이 같을 때
-- on 절을 생략하고 -> using 을 이용하면 조건식을 간략하게 처리
select *
from emp e inner join dept d
--on e.deptno=d.deptno
using (deptno)
where e.ename='SCOTT'
;
-- NATURAL JOIN
select *
from emp NATURAL JOIN dept
;
-- self JOIN : 자기 자신을 조인 ( 참조 )
-- emp -> mgr 컬럼이 emp.empno 참조
select e.empno, e.ename, e.mgr , m.ename
from emp e, emp m
where e.mgr=m.empno(+)
;
-- outer JOIN
select e.empno, e.ename, e.mgr , m.ename
from emp e, emp m
where e.mgr=m.empno(+) --> 표현하고자 하는 컬럼이 테이블의 반대쪽, null 값을 가지는 위치에 (+)
;
-- [left | right | full] outer join
select e.ename, e.mgr, m.ename as "관리자"
--from emp e left outer join emp m
from emp m right outer join emp e
on e.mgr=m.empno
;
-- NON EQUI JOIN
-- 동등 비교가 아닌 크다 작다와 같은 비교로 조인의 조건을 작성
select * from salgrade;
select e.ename, e.sal, s.grade, s.losal, s.hisal
from emp e, salgrade s
--where e.sal>=s.losal and e.sal<=s.hisal
where sal between s.losal and s.hisal
order by e.ename
;
-- 회원별 구매 내역출력
select c.name--, count(o.orderid) as "구매횟수", avg(saleprice)
from orders o , customer c
where o.custid(+)=c.custid
group by c.name
order by c.name
;
select * from customer;