SQL Comparison

[00:00:00]
>> [MUSIC]

[00:00:04]
>> Jafara Husain: So I think we left off yesterday with some pretty intense code written to some pretty intense expressions here to calculate data to eventually, basically create the data that we want, the collections we want from some data structure, more and more collections that we have. And so I believe we left off on question 24 which was, retrieve each of the video ID, title, middle interesting moment in smallest boxart of every video nested in all of these movie list.

[00:00:32]
So the instant cue, new releases, each one contains videos array and each one of the videos contains two collections. And we found out that we could make that happen by using zip, particularly, I wanna recall that zip is a great operator to use if you've got two array, two collections basically at the same time.

[00:00:51]
So you've got access to two collections. You wanna have them both in scope. One item from each collection and scope at the same time. That's when you use ZIP, right? Whereas map, you use if you want to, where there's an order dependent. So I need to map over all of the video lists before I can retrieve all of the videos inside because they're hierarchical, right?

[00:01:09]
And so I would use map in terms of order, so maps a way of sort of enforcing order of things in an interesting way, right? You're sort of saying hey, I wanna map over all the video lists and then map over all of the videos inside of it.

[00:01:19]
Whereas, in this case we sense a video has both interesting moments and box starts at a parallel, they're parallel, they're sort of siblings in the tree. We have access to both of them at the same time. Zip is a great way of getting both of them in scope simultaneously, because zip's function takes 2 arguments or actually technically the number of arguments that you pass to arrays, right?

[00:01:42]
So our implementation of zip, as I mentioned yesterday just takes two arrays. But you could see an implementation of zip it takes n arrays and then a function that takes n arguments. So it's when you wanted sort of process two collections, not quite in parallel in this in the sense of parallelism because it's JavaScript, right?

[00:01:58]
But effectively and that's the way to think about it, as if you're doing them both at the same time.
>> [INAUDIBLE].
>> Jafara Husain: Sorry.
>> Speaker 2: Bump the font.
>> Jafara Husain: Bump the fan, okay. So, how many people in the room, I'm curious, have experience with SQL? Quite a few. I think we've got a bit of an enterprisy crowd here which is good.

[00:02:17]
Because, has anybody noticed any similarity between SQL and what we've been doing here?
>> Speaker 3: Joins.
>> Jafara Husain: Joins. Interesting. Let's take a look at SQL and let's, even though it has a very different syntax, let's compare it to what we've been doing this far. So, let's say I had a bunch of titles or excuse me a bunch of lists and a bunch of videos, and they were much like in a SQL database bound together by identifier, by ID.

[00:02:48]
So let's say we had a list table.
>> Jafara Husain: So this is list. And let's see it has an ID field and a name field, and then we have a videos table and it has a ID, it also has a listid field
>> Jafara Husain: And it has a name. And so here's an example.

[00:03:25]
>> Jafara Husain: So we have a video inside of horror.
>> Jafara Husain: It's actually a movie I just saw yesterday on Netflix, very good by the way.
>> Jafara Husain: And this one is in thrillers.
>> Jafara Husain: So we can see that in relational databases, information is actually, it's kinda interesting, the difference between trees and relational databases.

[00:03:46]
Can you please tell me what the difference in hierarchical structures and relational databases are? How do they organize information differently? There's one very key obvious difference. In the hierarchical system, parents contain references to children, right? If you look at the previous examples, right, we had thrillers and they had a videos collection, and within that, we had videos.

[00:04:05]
Here, it's the other way around. Videos actually have a references to their parent. Notice the listid there? Sp hierarchical and relational systems are swapped in that particular way. Now, we use SQL to compose together relational systems. So, let's say I wanted to get all of the names of all the videos in all the lists, right?

[00:04:24]
Or actually for every video I wanted to get both the list name and the video name, so I wanted to know if it's Die Hard, I wanna know what list it's inside of. I'm gonna write a little SQL, here so I apologize for those you don't know SQL, but it's really instructive for those people out there who do know SQL.

[00:04:36]
And it's gonna demonstrate the similarity between SQL, for anybody out there who know sequel. And what we've been doing today because the truth is all the concepts we've been learning map, filter, reduce merge, zip, all the same as the concepts that are in SQL. So I wanna make that, I want to take a little time to make that, that, connection in your mind if you already know SQL.

[00:04:54]
So, I'm gonna select the videos.name and lists.name FROM
>> Jafara Husain: Video, it's been a while since my
>> Jafara Husain: My SQLs. It's been a while [LAUGH] from videos.
>> Jafara Husain: And there's a where clause and I basically have to link up these things, so
>> Jafara Husain: video.id is equal to listid.
>> Jafara Husain: Did I do that right?

[00:05:53]
>> Jafara Husain: Bump this font size down just a smidge.
>> Jafara Husain: So, what we're doing here is a join, right? We're sort of saying, hey I've got these two list and they're connected by ID, right? And we're sort of linking up by ID. Now, I'm gonna write the same thing in JavaScript.

[00:06:10]
And we'll see what it looks like.
>> Jafara Husain: So for every map, we're gonna go through each list and for every video that happens to be in that list.
>> Jafara Husain: We are going to map and grab the name of the video
>> Jafara Husain: And the name of the list.
>> Jafara Husain: Can't forget we need parentheses when we use our functions and JSON on the right hand side.

[00:07:10]
>> Jafara Husain: [INAUDIBLE] down just a bit.
>> Jafara Husain: And how many dimensions is this collection?
>> Jafara Husain: How deep are we in the rabbit hole here?
>> Speaker 4: Two.
>> Jafara Husain: Two, right?. Because within each list, for each list, we're returning in the list collection, we're returning another array. Because that's what filter returns.

[00:07:36]
So, all we need to do is apply one concatAll.
>> Jafara Husain: And now we're gonna have videoNames and ListNames
>> Jafara Husain: Is it clear why these two things are the same thing? Cuz SQL is just sugar. It gives you a lot of syntactic sugar for doing precisely this.
>> Jafara Husain: I think [INAUDIBLE] SQL might be off a little bit.

[00:08:15]
It's been awhile since I've done SQL here. Maybe you SQL experts out there can tell me if I'm wrong I want to alias the the item in the table there to video and list.
>> Jafara Husain: So I'm just wanna make the connection between map and select, filter and where, and these two nested FROMs can be thought of as a map nested within a map.

[00:08:39]
Does that makes sense? We're going over, for every item in one list, we're going over every item in the other list. So we go over the first list, we go over all the videos and we check to see which one, if that particular video is in that list by checking it's the ID, right?

[00:08:54]
And, so as we're sort of just going over all the possibilities and looking at the idea. Now this is not actually the most efficient way to do a join, in SQL, right? You'd want to actually use a join operator to do it more efficiently than this, but that's effectively what we're doing when we do this example.