forked from Jeanhwea/mysql-crash-course
-
Notifications
You must be signed in to change notification settings - Fork 0
/
15-joining-tables.sql
180 lines (173 loc) · 8.13 KB
/
15-joining-tables.sql
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
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
-- 第15章 联结表
-- create join
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
-- +-------------+----------------+------------+
-- | vend_name | prod_name | prod_price |
-- +-------------+----------------+------------+
-- | ACME | Bird seed | 10.00 |
-- | ACME | Carrots | 2.50 |
-- | ACME | Detonator | 13.00 |
-- | ACME | Safe | 50.00 |
-- | ACME | Sling | 4.49 |
-- | ACME | TNT (1 stick) | 2.50 |
-- | ACME | TNT (5 sticks) | 10.00 |
-- | Anvils R Us | .5 ton anvil | 5.99 |
-- | Anvils R Us | 1 ton anvil | 9.99 |
-- | Anvils R Us | 2 ton anvil | 14.99 |
-- | Jet Set | JetPack 1000 | 35.00 |
-- | Jet Set | JetPack 2000 | 55.00 |
-- | LT Supplies | Fuses | 3.42 |
-- | LT Supplies | Oil can | 8.99 |
-- +-------------+----------------+------------+
-- Cartesian product, join table returns Cartesian product
SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;
-- +----------------+----------------+------------+
-- | vend_name | prod_name | prod_price |
-- +----------------+----------------+------------+
-- | ACME | .5 ton anvil | 5.99 |
-- | ACME | 1 ton anvil | 9.99 |
-- | ACME | 2 ton anvil | 14.99 |
-- | ACME | Bird seed | 10.00 |
-- | ACME | Carrots | 2.50 |
-- | ACME | Detonator | 13.00 |
-- | ACME | Fuses | 3.42 |
-- | ACME | JetPack 1000 | 35.00 |
-- | ACME | JetPack 2000 | 55.00 |
-- | ACME | Oil can | 8.99 |
-- | ACME | Safe | 50.00 |
-- | ACME | Sling | 4.49 |
-- | ACME | TNT (1 stick) | 2.50 |
-- | ACME | TNT (5 sticks) | 10.00 |
-- | Anvils R Us | .5 ton anvil | 5.99 |
-- | Anvils R Us | 1 ton anvil | 9.99 |
-- | Anvils R Us | 2 ton anvil | 14.99 |
-- | Anvils R Us | Bird seed | 10.00 |
-- | Anvils R Us | Carrots | 2.50 |
-- | Anvils R Us | Detonator | 13.00 |
-- | Anvils R Us | Fuses | 3.42 |
-- | Anvils R Us | JetPack 1000 | 35.00 |
-- | Anvils R Us | JetPack 2000 | 55.00 |
-- | Anvils R Us | Oil can | 8.99 |
-- | Anvils R Us | Safe | 50.00 |
-- | Anvils R Us | Sling | 4.49 |
-- | Anvils R Us | TNT (1 stick) | 2.50 |
-- | Anvils R Us | TNT (5 sticks) | 10.00 |
-- | Furball Inc. | .5 ton anvil | 5.99 |
-- | Furball Inc. | 1 ton anvil | 9.99 |
-- | Furball Inc. | 2 ton anvil | 14.99 |
-- | Furball Inc. | Bird seed | 10.00 |
-- | Furball Inc. | Carrots | 2.50 |
-- | Furball Inc. | Detonator | 13.00 |
-- | Furball Inc. | Fuses | 3.42 |
-- | Furball Inc. | JetPack 1000 | 35.00 |
-- | Furball Inc. | JetPack 2000 | 55.00 |
-- | Furball Inc. | Oil can | 8.99 |
-- | Furball Inc. | Safe | 50.00 |
-- | Furball Inc. | Sling | 4.49 |
-- | Furball Inc. | TNT (1 stick) | 2.50 |
-- | Furball Inc. | TNT (5 sticks) | 10.00 |
-- | Jet Set | .5 ton anvil | 5.99 |
-- | Jet Set | 1 ton anvil | 9.99 |
-- | Jet Set | 2 ton anvil | 14.99 |
-- | Jet Set | Bird seed | 10.00 |
-- | Jet Set | Carrots | 2.50 |
-- | Jet Set | Detonator | 13.00 |
-- | Jet Set | Fuses | 3.42 |
-- | Jet Set | JetPack 1000 | 35.00 |
-- | Jet Set | JetPack 2000 | 55.00 |
-- | Jet Set | Oil can | 8.99 |
-- | Jet Set | Safe | 50.00 |
-- | Jet Set | Sling | 4.49 |
-- | Jet Set | TNT (1 stick) | 2.50 |
-- | Jet Set | TNT (5 sticks) | 10.00 |
-- | Jouets Et Ours | .5 ton anvil | 5.99 |
-- | Jouets Et Ours | 1 ton anvil | 9.99 |
-- | Jouets Et Ours | 2 ton anvil | 14.99 |
-- | Jouets Et Ours | Bird seed | 10.00 |
-- | Jouets Et Ours | Carrots | 2.50 |
-- | Jouets Et Ours | Detonator | 13.00 |
-- | Jouets Et Ours | Fuses | 3.42 |
-- | Jouets Et Ours | JetPack 1000 | 35.00 |
-- | Jouets Et Ours | JetPack 2000 | 55.00 |
-- | Jouets Et Ours | Oil can | 8.99 |
-- | Jouets Et Ours | Safe | 50.00 |
-- | Jouets Et Ours | Sling | 4.49 |
-- | Jouets Et Ours | TNT (1 stick) | 2.50 |
-- | Jouets Et Ours | TNT (5 sticks) | 10.00 |
-- | LT Supplies | .5 ton anvil | 5.99 |
-- | LT Supplies | 1 ton anvil | 9.99 |
-- | LT Supplies | 2 ton anvil | 14.99 |
-- | LT Supplies | Bird seed | 10.00 |
-- | LT Supplies | Carrots | 2.50 |
-- | LT Supplies | Detonator | 13.00 |
-- | LT Supplies | Fuses | 3.42 |
-- | LT Supplies | JetPack 1000 | 35.00 |
-- | LT Supplies | JetPack 2000 | 55.00 |
-- | LT Supplies | Oil can | 8.99 |
-- | LT Supplies | Safe | 50.00 |
-- | LT Supplies | Sling | 4.49 |
-- | LT Supplies | TNT (1 stick) | 2.50 |
-- | LT Supplies | TNT (5 sticks) | 10.00 |
-- +----------------+----------------+------------+
-- inner join
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
-- +-------------+----------------+------------+
-- | vend_name | prod_name | prod_price |
-- +-------------+----------------+------------+
-- | Anvils R Us | .5 ton anvil | 5.99 |
-- | Anvils R Us | 1 ton anvil | 9.99 |
-- | Anvils R Us | 2 ton anvil | 14.99 |
-- | LT Supplies | Fuses | 3.42 |
-- | LT Supplies | Oil can | 8.99 |
-- | ACME | Detonator | 13.00 |
-- | ACME | Bird seed | 10.00 |
-- | ACME | Carrots | 2.50 |
-- | ACME | Safe | 50.00 |
-- | ACME | Sling | 4.49 |
-- | ACME | TNT (1 stick) | 2.50 |
-- | ACME | TNT (5 sticks) | 10.00 |
-- | Jet Set | JetPack 1000 | 35.00 |
-- | Jet Set | JetPack 2000 | 55.00 |
-- +-------------+----------------+------------+
-- join multiple tables
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
-- +----------------+-------------+------------+----------+
-- | prod_name | vend_name | prod_price | quantity |
-- +----------------+-------------+------------+----------+
-- | .5 ton anvil | Anvils R Us | 5.99 | 10 |
-- | 1 ton anvil | Anvils R Us | 9.99 | 3 |
-- | TNT (5 sticks) | ACME | 10.00 | 5 |
-- | Bird seed | ACME | 10.00 | 1 |
-- +----------------+-------------+------------+----------+
-- difference: join and sub-query
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN ( SELECT cust_id
FROM orders
WHERE order_num IN ( SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'
)
);
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orders.order_num = orderitems.order_num
AND orderitems.prod_id = 'TNT2';
-- +----------------+--------------+
-- | cust_name | cust_contact |
-- +----------------+--------------+
-- | Coyote Inc. | Y Lee |
-- | Yosemite Place | Y Sam |
-- +----------------+--------------+