Repository navigation
Expand file tree
/
Copy path20191111.sql
More file actions
92 lines (79 loc) · 1.95 KB
/
Copy path20191111.sql
File metadata and controls
92 lines (79 loc) · 1.95 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
SELECT * FROM orders;
SELECT * FROM book;
SELECT * FROM customer;
SELECT *
FROM orders t1, book t2, customer t3;
SELECT count(* )
FROM orders t1, book t2, customer t3;
-- 똑같은 컬럼이름이 중복돼 있을 경우
SELECT bookid
FROM orders t1, book t2, customer t3;
-- column 이름이 같을 때 구분
SELECT t1.bookid, t2.bookid
FROM orders t1, book t2, customer t3;
SELECT t1.*
FROM orders t1, book t2, customer t3;
SELECT t1.*, t2.*
FROM orders t1, book t2, customer t3;
-- 카티전 곱(카티전 프로덕트 연산)
SELECT *
FROM orders t1, book t2, customer t3;
-- 내부 조인(Inner Join)
SELECT *
FROM orders t1, book t2, customer t3
WHERE t1.bookid = t2.bookid
and t1.custid = t3.custid;
-- 내부 조인(테이블 2개)
-- case 0)
SELECT *
FROM Customer, Orders
WHERE Customer.custid = Orders.custid;
-- case 1)
SELECT *
FROM Customer t1, Orders t2
WHERE t1.custid = t2.custid;
SELECT *
FROM Customer t1, Orders t2
WHERE t1.custid = t2.custid
and t1.name= '박지성';
-- case 2)
SELECT *
FROM customer t1 INNER JOIN orders t2
ON t1.custid = t2.custid;
-- left outer join
-- case1)
SELECT *
FROM customer t1, orders t2
WHERE t1.custid = t2.custid(+); -- +는 부족한 쪽에 붙여준다.
-- case 2)
SELECT *
FROM book t1 left outer join orders t2
on t1.bookid = t2.bookid;
-- right outer join
-- case 1)
SElect *
FROM customer t1, orders t2
WHERE t1.custid(+) = t2.custid;
-- case 2)
SELECT *
FROM book t1 right outer join orders t2
on t1.bookid = t2.bookid;
-- FULL outer join
-- case 1)
SELECT *
FROM book t1, orders t2
WHERE t1.bookid = t2.bookid(+)
union
SELECT *
FROM book t1, orders t2
WHERE t1.bookid(+) = t2.bookid;
-- case 2)
SELECT *
FROM book t1 left outer join orders t2 on t1.bookid = t2.bookid
union
SELECT *
FROM book t1 right outer join orders t2 on t1.bookid = t2.bookid;
-- 안팔린 책 찾기
SELECT bookname, publisher
FROM book t1 left outer join orders t2 on t1.bookid = t2.bookid
WHERE t2.orderid is null;