Database Design 41 - Introduction to Joins — Transcript

Full transcript

1. 0:00hey you slacker you skipping class now
2. 0:06you need someone to help you through the
3. 0:07join process and you're all confused
4. 0:10well luckily for you I'm here to help
5. 0:13you so this video we'll be talking about
6. 0:15joins and in fact the next whole group
7. 0:18of videos will be about joins so it's
8. 0:21going to be a lot now joins are a
9. 0:24complex subject they're they could be
10. 0:26confusing and tricky and uh very
11. 0:30syntactical I don't even know if that's
12. 0:31a word but basically the syntax from
13. 0:34relational database management to
14. 0:36another relational database management
15. 0:38system can be confusing and complex but
16. 0:43rather than diving in just learning all
17. 0:46that stuff we're going to be talking
18. 0:47about the concepts of what the types of
19. 0:51joins are and I know you might not even
20. 0:53know what a join is yet so you're
21. 0:54probably thinking I don't know what
22. 0:55you're talking about well that's what
23. 0:57we're talking about today so thank you
24. 0:59for joining me let's begin so a join is
25. 1:04when you take something that's
26. 1:06structured in a database in a confusing
27. 1:08non- userfriendly way and you're
28. 1:10presenting it in a userfriendly
29. 1:13way now this is done so that way we can
30. 1:17store our data in the database in a way
31. 1:19that's the best structured to where we
32. 1:22protect our data integrity and it's
33. 1:24normalized and separated across tables
34. 1:26with the proper relationships and data
35. 1:29types and keys and indexes and
36. 1:32everything but when we actually want to
37. 1:35present that data for example on a web
38. 1:37page or in a program or to the um
39. 1:41analyzer person who goes and analyzes
40. 1:44and does a calculations on our data or
41. 1:47just views those calculations after the
42. 1:49database does all the work with the
43. 1:52analyzation well that end result is
44. 1:55often in a way and it looks in a way
45. 1:57that uh is organized and structured and
46. 1:59makes sense to us that's the purpose of
47. 2:02a join so it takes a mess and puts it
48. 2:05out in a way that looks beautiful and
49. 2:07this can be done over multiple tables so
50. 2:10for example we could have a table
51. 2:13here a table here and a table here and
52. 2:16these could all be three different
53. 2:18tables with relationships between the
54. 2:20tables we won't get into any specifics
55. 2:23of what kind of tables but basically a
56. 2:25joint is going to take all of
57. 2:27this and it's going to present us with
58. 2:31a generated table that is more
59. 2:35pretty it's more beautiful and more
60. 2:38structured and it makes more sense now
61. 2:41the way this is done is using foreign
62. 2:43key primary key connections so when we
63. 2:47have these relationships between these
64. 2:49tables where I drew these arrows here
65. 2:52that represents a a a foreign key
66. 2:55connection that references a primary key
67. 2:58so one of these t tables is the parent
68. 3:00one of them is the child and we want the
69. 3:03end result to combine them together into
70. 3:07one now when you do this these columns
71. 3:11that are connected are going to be
72. 3:12indexed to make it much faster now uh
73. 3:15when you join tables often you'll
74. 3:18replace names of things with more
75. 3:23userfriendly names so for example we
76. 3:25could have you know let's say we had a
77. 3:28comment on a website
78. 3:30and it says
79. 3:33wow
80. 3:35lame and then this is posted by uh
81. 3:40Caleb well you can see that if you have
82. 3:43that username here it it might actually
83. 3:46be a user ID in a comment table so for
84. 3:49example we could say this up here is the
85. 3:52comment table this is the user table and
86. 3:55there's a foreign key from the user ID
87. 3:58referencing the user ID ID in the user
88. 4:00table well we when we present that data
89. 4:03on a database we will want to replace
90. 4:05that user ID with the actual username so
91. 4:09rather than having you know we could
92. 4:11have it to where it's like user ID of a
93. 4:164,582 and then you're like oh I don't
94. 4:19really know what that means well since
95. 4:22this is all within the comments table
96. 4:23now we're going to take information from
97. 4:25the user table and put that username
98. 4:28instead of the user
99. 4:35ID that is how a join works
100. 4:39conceptually that's another important
101. 4:41thing all this think of it conceptually
102. 4:44these videos are going to be helping you
103. 4:45understand the concepts of the different
104. 4:47kinds of joins and throughout it I'll be
105. 4:49giving you some examples of how you
106. 4:51would type that in SQL maybe but I'm not
107. 4:54going to be getting into the specifics
108. 4:57that's because joints are done
109. 4:59differently from database management
110. 5:01system to database management system so
111. 5:04what you just need to know is how the
112. 5:06joins work and the expected
113. 5:11results and then when you start working
114. 5:13with mySQL or SQL server or Oracle
115. 5:16database or whatever else then you can
116. 5:19just uh figure out how to do their joins
117. 5:22you'll already know what the results are
118. 5:24going to be because you understand the
119. 5:25joins and then you just type it out boom
120. 5:28you got a join there
121. 5:30now this is one other thing to know
122. 5:32that's important is all of this is uh
123. 5:35data manipulation most of what we've
124. 5:37been talking about before is data
125. 5:39definition so that was in um basically
126. 5:42ddl but now we're going on to
127. 5:47DML so ddl data definition language and
128. 5:52data manipulation language these are two
129. 5:57parts of SQL we all we all talked about
130. 6:00this in uh the older videos but we're on
131. 6:03like way farther now so you guys might
132. 6:05have forgot this but this is important
133. 6:07to know because now we already have the
134. 6:10definition the structure of the database
135. 6:14the database might already be completely
136. 6:15designed and structured with the columns
137. 6:17and the rows but now we're manipulating
138. 6:21that data to look a certain way so by
139. 6:24doing joints you're not actually
140. 6:27changing the structure of the database
141. 6:30you are changing the presentation of
142. 6:34that database that is extremely
143. 6:36important to know now you may be
144. 6:39wondering why exactly do I need to know
145. 6:42this because if I'm database designer or
146. 6:46whatever really all I need to do is
147. 6:49design the structure of the database and
148. 6:50then the database administrator or the
149. 6:53software engineer person or whoever can
150. 6:56go in there and use my database that I
151. 6:59designed and created and they can do all
152. 7:01the data manipulation stuff to fit their
153. 7:03application right well maybe but not
154. 7:07likely that's because the joins are also
155. 7:11very important to understand because
156. 7:14when you design your
157. 7:15database you need to think of it in a
158. 7:19userfriendly
159. 7:20way and then normalize it make it
160. 7:24confusing and then present it in a
161. 7:26userfriendly way it's kind of confusing
162. 7:29because
163. 7:29you start with let's say you really bad
164. 7:32at database design and you're
165. 7:34structuring a table and you have it to
166. 7:36where it's like comments a comment
167. 7:40table and within this you have a
168. 7:43user
169. 7:45name and then you have the the
170. 7:49comment and then maybe the blog post or
171. 7:52video whatever it doesn't really matter
172. 7:53you can have some IDs in there basically
173. 7:56you don't know what you're doing and you
174. 7:58put the username
175. 8:00we'll just go with Caleb C comment lame
176. 8:04I got another one from Caleb C
177. 8:07cool Caleb
178. 8:09C
179. 8:13awesome when you look at this and you
180. 8:16show it to your grandma she understands
181. 8:18it she's like okay she might not
182. 8:22understand it but she gets if you
183. 8:23explained it she might Caleb C posted
184. 8:26the comment lame Caleb C also posted the
185. 8:29comment cool Caleb also posted the
186. 8:31comment oops Caleb C also posted the
187. 8:33comment
188. 8:34awesome but this isn't the best way to
189. 8:37do this because we learned about data
190. 8:38integrity and also repeating data
191. 8:41because now we have this in here three
192. 8:43times so instead we use a user ID we get
193. 8:47rid of Caleb C and we get rid of the
194. 8:50username we have a user
195. 8:53ID and then in here we have the user
196. 8:57id6 ah let's not use that number number
197. 9:007 77 and then over here we have a user
198. 9:05table with the a user
199. 9:09ID column which has all of our user IDs
200. 9:13for each individual person so we started
201. 9:16off with
202. 9:18this friendly design but in reality it
203. 9:21doesn't work because it's not normalized
204. 9:23there's repeating data but it looks
205. 9:25pretty once we have that then we can
206. 9:27break it up and normalize it so that way
207. 9:31we have multiple tables so we have this
208. 9:33table and then we have this table over
209. 9:36here but that's not the only thing we do
210. 9:39the next thing we have to do is we have
211. 9:41to take up those pieces of the puzzle
212. 9:44and put them back together in a join to
213. 9:47get the final result so we basically
214. 9:50want to replicate the original table we
215. 9:52had here by using a join of our
216. 9:55normalized database and recreating it so
217. 9:59what we're going to do is we're going to
218. 10:01take the user ID column and we're going
219. 10:03to take uh the user
220. 10:06ID can you guys see that all all good
221. 10:09it's over there this this will likely
222. 10:11also have a username column so I kind of
223. 10:14ran out of room here but
224. 10:17username and that usern name user ID is
225. 10:20seven will be connected to Caleb
226. 10:22C using a
227. 10:24join we are going to connect these two
228. 10:27tables by basically
229. 10:29using this foreign key which points to
230. 10:31the
231. 10:32seven and finding the associated
232. 10:35username with that user ID and our join
233. 10:38will look like
234. 10:40this
235. 10:43username
236. 10:45comment and the the name of the table
237. 10:48can be I mean whatever you want it could
238. 10:49be generated table or it doesn't even
239. 10:52need a name if you're just putting it on
240. 10:54a website or something and then under
241. 10:56the username column it'll be a
242. 10:59Caleb
243. 11:01C and then Caleb
244. 11:03C and then Caleb
245. 11:06C and then under the comment you're
246. 11:08going to have
247. 11:10lame and uh
248. 11:12cool and
249. 11:15awesome but this wasn't just like this
250. 11:18to begin with we got the username from
251. 11:21this table and we got the comment from
252. 11:24this
253. 11:25table so not only do you have to design
254. 11:29the structure of the database but you
255. 11:30have to structure the joins to basically
256. 11:34put our database back into simple
257. 11:38language or Grandma readable tables okay
258. 11:43that that's basically what we want to do
259. 11:45so let's say you get a job or you're
260. 11:47working for a developer or something and
261. 11:49they say okay on my website I want a
262. 11:52table on this uh users page where I can
263. 11:56sign in with my username password and I
264. 11:59can see their username their password
265. 12:02their email their uh date they signed up
266. 12:06and everything like that and if I want I
267. 12:08can go in there and edit it that way
268. 12:10they don't have to go in and type SQL
269. 12:12you're you're going to have that
270. 12:14programmed in the
271. 12:16um in the uh actual application to send
272. 12:21that SQL but we need to structure the
273. 12:23tables we need to make a join that's
274. 12:25going to be able to combine those all to
275. 12:27one pretty table which that's the job of
276. 12:30the database designer likely I mean if
277. 12:33the application or the database is huge
278. 12:36you might have someone who does the the
279. 12:38um designing and then someone who does
280. 12:41the implementation and then someone who
281. 12:44does the joints and then someone who
282. 12:45does this other junk but I mean if
283. 12:48you're just working on your own and you
284. 12:50got to do everything you're also going
285. 12:51to have to design the joints and I
286. 12:53highly highly highly recommend you do
287. 12:55that before you go in and programming
288. 12:58your app application and then you're
289. 12:59like oh crap I don't know how to do this
290. 13:01or oh my database is structured wrong
291. 13:04you want to do this all beforehand don't
292. 13:06rush into things because then you're
293. 13:08going to be like dude dog my application
294. 13:11ain't working right and the reason is
295. 13:13because you didn't take the time to
296. 13:14structure your database the right way
297. 13:16and develop your
298. 13:18joints man okay once you got that you're
299. 13:22good to go and yeah so there are a
300. 13:25couple different kinds of joins there's
301. 13:27inner joins outer joints joins cross
302. 13:29joins unions there's all kinds of
303. 13:32different things that we're going to be
304. 13:34talking about and we're just going to be
305. 13:36scratching the surface of each and every
306. 13:38one so after these uh next group of
307. 13:41videos you will be sufficient in Joins
308. 13:44you can be like
309. 13:46joining good so yeah catch you in the
310. 13:49next video and stay in school or or
311. 13:53watch my videos one of the two peace out