I think to echo some of the thoughts that he just conveyed, like whenever I've spoken to, you know, my peers, friends, mentees, mentors about SQL, I've never needed to really sell SQL or it relates to databases for their importance. Like there's a lot of intuitive understanding about why they are important and why we should learn them. But at the same time opportunities to really dig in and understand they want on an intuitive level, there are not many. Like take for example an average Rails programmer like me many years ago. I spent years using Ruby on Rails with active record and not understanding what it did internally or how it used my SQL or how it used Postgres. And when I had the opportunity to do it, that sort of opened my eyes like hey, I mean this is all available for me and I have not been looking at it. And I've had many of these kind of opening movements throughout my usage of relation databases. And then once I sort of crossed out of understanding the basics, foundations and back to hardness, then I started teaching and talking about it to, you know, other friends and mentors mentees. And then from there, sort of a passion to teach SQL developed. And that's what Alekta was talking about, like we keep exchanging stories, design patterns, like have you done this? Would you do this? What's a good example of this? Or how would you teach this? And that theme has sort of been with me for a while. Coming back to SQL and join, especially, like one of the things that scares people away is joins and not for a good reason, honestly. Like again, like a lot of the reason behind people being scared of joins is lack of exposure and understanding. And some of that I'm going to try and dispel today. Like a first principle approach has long been my favorite way of understanding something. And that is what I'll try to do today. Let me go ahead and share my screen. So we're going to start with, is everyone able to see my screen? Yep, you can. All right, great. Cool. So the topic is fearless joins, the title of the talk. Just enough SQL to be effective. And the reason why I call it fearless is because of that deep rooted fear of joins that a lot of people have. Maybe fear is a little harsh, but at least there is an uneasiness around joining joins. People have these notions like joins will not scale or joins are hard, et cetera. And some of these may be true. If you're Google, it's very right for you to be worried about writing joins, a very different level of problem. Not everybody is Google. The data sets are not Google level databases. And so understanding joins on a very fundamental level gives us a tool of writing and working and understanding data that very few other tools give you. And then a note about just enough SQL. Well, the core of SQL is not very large. It has a very short semantic syntax. The actual syntax is big and wide, but the core of SQL is not very large. However, to cover it in a session of one hour is impossible, which is why I'm taking that workshop later this month. So today, what we'll do is sort of do a kickoff based on joins. Just a little bit about me, already I think we've spoken enough. If you really want to understand about joins or what I do or the workshop, some of the links here, I'm active on Twitter. I'm always happy to chat about all things tech, SQL, Ruby, Python, ping me anytime. But speaking and continuing about that conversation we were having earlier, I have a mission. And that mission is help people outgrow active record, Django, or DB, and friends, and other warrants.  Sure, if you're writing a Django app, you want to use Django or a DB because it's going to make a lot of things easy for you. But then I don't want you to stop there. I want you to go and understand that in-depth, go around it, work around it, understand the semantic meaning of different things the right way, the idioms around designing relationship DBs, idioms around writing queries, and use them. A very eloquent friend of mine once said, there are many common things between vacuum cleaners and ORMs, and the primary common thread being that they both suck. So I sort of echo that thought in some manner. But today's talk is specifically about joints. And if we want to expand on what about joints, well, we're going to try and understand joints. We'll also try to learn how to read and write complex joints. And then the eternal question as to what joint should I use and when should I use which joint? And how do I start writing a query or how do I approach writing a joint? So if you look at documentation, Postgres, for example, has fantastic docs. They'll explain the syntax in-depth. There are examples enough for you to work out a work through which will give you some sort of an understanding. What we don't teach and what comes from experience in working with other people is an approach that you should use. Like where do you start? What do you first look at? How do you grow that circle? And I have this data and this is my outcome that I want. And this is the data that I'm starting with. How do I go from A to B? And what is the approach? What path should I take? How do I select which joint? How do I select which tables? Et cetera. And so we look at some of that towards the end of this talk. If I had to limit or if you had to learn a single thing from this class, I would say the first part where we understand joints from the first principles, understand that and think everything else will sort of follow through. So speaking of joints, coming back to join. By the way, should we wait for more people to join the class? Or should we move on? All right, that was a bad attempt at a plan. But anyway, if you've been around with SQL for a long time, these are some of the terms that you would have seen, specifically around joints. Inner joints, outer joints, left, right, full, recursive, rectangular, cartesian, lateral. Lateral is a recent addition to Postgres and MariaDB. Oracle has had it for some time. And so if you have written queries, I'm sure you would have some sort of an understanding of what are these for and what do they do? And I'm sure you would have seen that infamous, as I like to call it, infamous Venn diagram, where like some parts of Venn diagram are colored and others are not. So in today's class, I want you to unlearn that Venn diagram. The fundamental idea behind a join is that you have sets and you'll combine them. Your data or information is spread across multiple sets and you're going to combine them. Or you have sets and you're going to join them. That's where the word join comes from the relational library where you join multiple sets. Not very different from matrix multiplication, for example. And then the core idea behind joins is that joins are about sets and not tables. If somebody says join and the first thing that comes to your mind is that I'm going to join two tables, then that is the very first thing I want you to unlearn. Joins are not about tables or not only about table, joins are about sets. We always join two sets, we never only join two tables. Table is a set, so it's a subset of sets. And so yes, you can join tables and that's what we mostly do. But it's not limited to tables. For example, your data could be spread across tables. Your data could be spread across views. It could also be across different files mapped as a foreign data wrapper. For example, in Postgres, your data could be in another query and you could still join with this query. And all these are examples of sets. When you write a select query, that returns a set. And then you can join that or treat that as a table and join with other tables or other sets and then get the result that you want. So this is the number one take and the number one thing that you should learn or you should take away from this class is that joins are about sets. And that fundamental default association or join with table, if you can break it, you're on a very, very good path. And then the other thing I said, unlearn that Venn diagram, like the circles left and right and coloring. I'm not going to show it for the reason that I want you to unlearn. But I'm sure if you work with join, you've seen this Venn diagram. Erase it from your memory. Like forget about it, we don't need that. Okay, so enough about slides. It's illustration time now. I'm going to share a whiteboard and we're going to look at some concepts specifically around join. So is my screen visible? Are you able to see like a whiteboard? Okay, perfect. Okay, so let's look at, so we're talking about sets, tables and join side. So let's say what I'm going to do is I'm going to take two sets, then join them because we're going to talk, we're talking about that. And then in that context, we are going to look at what is left, what is inner, what is right, what is outer, what is cross, etc. Lateral joins will cover a bit later. They are a specific implementation and performance improvement over regular joins from a relational query. They don't fit in the original relational theory in the same way inner and outer joins do. And we'll see some of that as we progress. So let's say we have, like a set, we have a table. Is this visible? So this is, let's say this is a set one. And then we have another set which says one, one, let's say three. So first of all, good, visible because I can't hear everybody. I would appreciate it. Cool. So now, if you want to join this, this is our fundamental working set. There is a reason why I chose a very small set. I'll come to that soon. But let's say now we want to join this set. So the most fundamental, the most fundamental join is what you call as a rectangular join or a cross product. What it means is we join every element of one set or let's say the left side set with every element of the right side set. And before we start joining, let me add something to this diagram. Null is a special element which is a part of every single set. So this is simple for null. For brevity sake, I'm not going to write null every time. But whenever you talk about sets, you can practically assume that null is a part of that set and it features into all kinds of calculations and decisions that the DB engines take. And so we had our original set of two records. We've added our default null. And now we're going to try and see how joins work in that sense. So if you look at our superset now, there are three elements in the left set and three elements on the right set. And any joining activity starts at joining each element with each of the other elements. So if we have to go and write it down, what that means is say a A1 will be joined three times with each element from the other side, which is one, one, three, and then null. And then similarly, our other record, which is B2, again joined three times. And then there's one honorable special, oops, whatever. Now, for all practical purposes, this is our sort of what I call as our working set. Is this even readable? I'm looking at the amount of capital that's readable to me. Yeah, it is to me. But is it visible, readable? Yeah, seems fine. It is. Okay, it is very important that we understand this. Okay, perfect. It is very important that we understand this for what is coming next. So now, coming back to our superset, or as I like to call it a working set, let's say we're working with two sets and we're going to join them. Then this is our, this is what I call as our working set. So we start here, and then every other thing follows from this. For all practical purposes, what most dignities do is that they don't consider this case, this case, the last one where the null is joined with null. For all practical purposes, it is of very little use in every day, every life. It is from a, from a mathematics perspective, it is important when you write proofs of things, when you write proof like, oh, left join is going to output or produce a particular set. And when you're demonstrating proofs, so in that case, the null join is important. But we're not doing any proofs today out of scope for this class. So for our discussion, we can consider that this case does not exist. So now, so now the next thing is that how do we add the concepts that we know, like inner, outer, left, right? So let's talk about that. So there are two kinds of joints. One is your regular rectangular joint or cross joint. And the other is it doesn't have any predicate or doesn't have any matching condition. What we do is what you've just done here, we just take every element from left and join it with every element from right, all the combinations, with all the combinations, not permutations. Then comes your inner and outer joints, which are based on a predicate. So what SQL standard allows is, okay, join these two sets, given this condition. So if we had to add context, in this case, say for example, A1 can be joined with one, one, because they have some common element, some shared context. You know, like for example, posts, and if you're joining posts and comments, right? So you can join posts and comments based on comment.post.id equal to post.id. So because there's a shared context, there's some shared information. So similarly in here, A1 and 11 have some shared like the number one they share. But in this case, A1 and 3, you know, 3, 3, that doesn't make sense. What we'll say, like in our case, that join the numbers with literals, where the number from the numbers table, the number set, is equal to the number from the literal set, which means, so we join A1, then we join, and that's it. So right now, we only have one matching entry in this case. So this is where our inner and outer, the concept of inner and outer comes in. Whenever we're adding a predicate, I'm going to cover the syntax in a little bit. But let's say now, if you're adding a left join, what it means is that now we are joining, whenever this element matches, we are joining. So if you look at the table, or if you look at this particular joint, there's no other set where those two columns match, or those two values match. Which means, if we do an inner join, this is the only record that will be selected for us. And this is where our left and right concepts come in. So now if we're doing left join, what that means is, from the left side, you know, pick the entry that doesn't have a match on the right side. What it means is that when we do left join, the right-hand side null is going to come into picture. And this is the reason why we added null in the first place for each set. So when we join on a predicate value, which is what you're used to looking at, in the context of left join, what that means is, select the set from the left side, no matter if there's a match or not. Similarly, for say a right join, if you were to do a right join, the same thing applies. That reminds me. So let's go back here. Which means these two, the total we have about nine entries. Now what that means is, when we're talking about a right join, we also need to consider and for left, we need to consider these. So far, so good. So the idea of left and right comes from selecting elements on the left side of set and the right side of set, specifically with a null match. Now this will get a little more clearer when we actually start writing queries because that is what we are used to visualizing based on. But the fundamental idea is this, that we took a set. We took two sets that had two entries each or two elements each. Then we added our fundamental default entry of null. And then we multiply it. We added each entry against each entry. That gives us a total of nine entries. And then for our practical purposes, we ignored the entry that had null on both sides. And then we looked at left and right. So now I'll repeat again, because again, even though it's trivial for those who understand, it's not trivial for those who don't understand. So from an inner and left and right and outer join perspective, inner is when we do a strict match, which means now, let's say, we decide to match on the middle column, only where we have a strict match. Those rows are going to be picked. And then outer means we're going to pick entries where there's a null. And again, left means we're going to pick entries where the left side does not have a match on the right side. And then the right means we're going to pick entries where the right side does not have a match on the left side. So if you look at it from a symmetry perspective, right? Left and right are very symmetric. And that purely depends on the ordering of the sets. If you flip the right side set to left side, then your left join is same as the right join. And there's no difference. Similarly, when you do an outer join, when you add null entries from both sides, that's like including left and right together. And there are no practical uses of doing a full outer join, except maybe some corner cases in ETL pipeline and data processing engines. In your everyday life, the two most important things that we need to be concerned about is the inner join, where we only select this. And then the left join, where we have a match on the right side. So when I try to sort of write queries, I call the null as a whole, actually whole. When you want to query for holes or when you want to query for data that does not exist, you're going to use null. And whereas when I want to write queries for data that does exist, we are going to write inner joins. Okay, so in order to proceed further, I'm going to introduce a schema to everyone. And the queries that we're going to talk about will be in the context of that schema. Could check are the slides still visible? Yes. Okay. So let's come back to join semantics a little bit before we go on to the schema. What we have seen so far is that set L joins, set on the left joins, set on the right, and then based on our predicate. And without the predicate back to the square line, which is not super important right now, I will come back to it at a later point. This is the set where we have all the elements joined with each other, all the combinations. Like I said, those are purely from a theoretical importance or a mathematical importance. Like for example, if you're doing a scalar product of two sets, maybe you could use a rectangular join. But as far as practicality is governed us in everyday life, we are mostly concerned with joins that are based on a predicate, which means we are looking at inner join and outer join, and specifically inner and left join, because left and right join purely are like a mirror image of each other. And what you can do with right join, you can do with the left join too. Exceptions apply, but they're not super important for now. So yeah, so this is a fundamental syntax for joins. And if it looks too simple, it's because it is simple. Any join is basically on the left, there's a set, and on the right, there's a set. Now again, if you want to derive an intuitive understanding of what the set are, we'll go back to our previous slides where we said a set could be a table, it could be a query, it could be a file, it could be anything. So for example, the set L here could be a select query in itself, and the set R here could be another select query in itself, and we can still join them. We ultimately, we're only looking to join two sets, and every query returns a set. So let's look at joins in action to understand and then look at some more queries we're going to, like I said, we're going to introduce a little schema that will stay with us for the duration of this talk, and we'll use that to write a few queries. So let's say we are a gossip platform, and not the gossip protocol platform, like a magazine, you know, print fair, kind of magazine, and we have rumors, we have tidbits about those rumors, and we have sources of those rumors, you know, people or different sources provide different rumors. So for example, a rumor is provided by many sources, and a source provides many rumors, a standard, many to many relationship. Another bit is a tidbit, right, a tidbit about a rumor. One rumor is often comprised of multiple small factoids, never like a single source. And so a tidbit is that, a tidbit is about a rumor, which means it belongs to a rumor, and again, a tidbit could be provided by multiple sources. And then again, one source can provide multiple tidbits. It's actually much better explained with an example. So for example, there's a rumor going that some big company is going all remote, and the sources for that is like one popular magazine and BuzzTree may popular platform. And so the tidbits are not very hard to imagine. For example, employees, you know, are given home setup budget, is a tidbit or a factoid that is floating around, or a job opening was posted on remote remotely, which has never happened before for this particular company. They hired Jane, a very senior leader, who is an outspoken remote work supporter, or the CEO of the famous company followed the remote podcast on Twitter. So these are tidbits. Independently, they may not mean anything, but together they form a rumor. And this is our data model. I hope that this is clear enough. I'm going to go ahead with the assumption that this is clear enough. But in fact, you know what? Let's take a pause and let's answer questions, if there are any. And then we'll go ahead with the next section. So have there been any questions so far? Yeah, there's been one interesting question. Some got answered through, as you explained some things. But one question is, does the output of an inner join and a left join have no common elements? Or are there common elements between an output of an inner join and a left join? And by left, I'm assuming it means left outer join. Left outer join, correct. Yes, so yeah, good question. I think I'm glad you asked the question, because understanding the answer to this question is, like I said, fundamental to understanding how joins work. So the common element between an inner join and a left join is the element where the match happens. So let's say you're joining, in our case, A and 1 with 1 and 1 literal. And then so in an inner join, the A1 and 1, 1 row is going to show up. Whereas in a left join, that row will show up. And additionally, A1 and null on the right will also show up. So from a set theory perspective, a left join is a superset of an inner join. So I'll explain again. So I'll give a slightly more at home example. If you're joining, say, posts, or let's say you're talking about a blog, then say there's a table for articles and there's a table for comments. Comments belong to an article, article has many comments. And so now if you do like an art join article, left join comments on article.id equal to comment.article.id. Now in this case, so let's talk about inner join first. So let's say you do articles, inner join comments. In this case, the only articles that have an associated comment are going to show up in the result set. Because now we are doing inner join, which means there's a strict match, which means nothing that did not match or produced a match, a null match on either side is not going to be selected. Whereas the moment we do left join, what that means is that select the one where there was a match, but additionally also select where there was no match for the left hand side record on the right hand side. So in terms of understanding or a mnemonic left join means pick the record from the left side. And right join means pick the record from the right side. So in this case, article left join comment means you will select articles where there is a comment and you will select articles where there's no comment. Whereas when you do inner join, you're only going to select articles when there's actually a comment. So that's the main difference. Left join is like a superset of inner join. Is there anything else? Any other questions? I hope this was clear. I hope so too. Now there's no further questions. Okay, thank you. There's one more coming in. Okay. What would an outer join be in terms of this article analogy? Okay, great. Yeah, so an outer join in terms of articles is that you select articles that have a comment and you select articles that do not have a comment. Whereas inner join is select articles that have a comment. So this is a question of at least one or zero or one. Like in reg X you would write plus for an at least match and a star for zero and one match or a question mark for zero and one. The same concept. Inner join only select articles that have at least one associated comment. And then outer join select those, but then apart from that also select articles that do not have any comment. Current, it was current, right? Yeah, all right. Thanks, Karan. And I think when you run a query on these actual query things will be up there. Correct. So I think things will get a little clearer. So on top of this rumor sourced tidbit model, so the reason I picked a slightly wacky sounding model is that often when we are trying to understand something, if there is a very intimately familiar domain, like articles and comments, often we look at it from the lens of what we already understand. And I specifically wanted to avoid some of that. So when the domain is different, when it's out of normal or out of ordinary, then what happens is that you can look at the semantic of the join instead of looking at thinking. So right now, I don't want you thinking in terms of articles and comments. We'll get there. But what I want you to think of in terms of like left and right set and what matches and what doesn't match. And for that reason, this slightly wacky sounding domain I picked. And on top of that, we're also going to look at a very, very common use case, labels. Your standard labels, you know, a label can have sub labels, sub labels can have sub labels. And then labels can actually have a parent label, one single parent label. Together they make a classic tree hierarchy of tags or departments, sub departments, whatever way you want to imagine. But a classic tree hierarchy. In terms of programming, if you were to write like a depth first search, for let's say we want to find out all the descendants, then you'll have to write depth first search. And you can do that in SQL using joins. And we're going to look at that a little later. So if you want to give examples again, say for example, computer science is like an overarching label. And then there are sub labels, you know, it's open to imagination. Add your own things. Some examples are like say OS, and then under OS comes like memory management, processes and signals. Bicernel and other things, device drivers, what not. Then there's software engineering, which is under computer science arguably, but definitely different from operating systems. And then under software engineering, you have OOP design under which you have encapsulation and you have functional programming, etc. So we have like a proper tree hierarchy of labels. And so we are going to sort of look at this. This schema will be sort of central as we go through and write a bunch of queries. I'm going to switch to my query editor now. Okay, so are people able to see the screen? I'm assuming people are. Okay, so Zainab I need to take like a maybe a couple of minute break, maybe two minutes. I just need to get some water. So I think I'll take a quick pause and I'll be back. In the meantime, if you have other questions, we can sort of collect them and I'll answer. And then once I'm back, we can sort of go on. Sure. What did you end remarks or comments at this point from you? Not really. I mean, I think once the actual queries are run, a lot of stuff will become clear for those who are maybe still grappling with it. So sounds good. I think everybody can do with a water break. Yeah, I wish there were days when we had the zoom feature, I had to push my hand through the computer and guess what I bought. Actually, one interesting thought came to me is someone was talking about the SQL being like a right to introduction that if you take an analogy between data storage and databases and programming languages, SQL is like the C of data storage. I mean, the C language, not S-E-A and joins are like the pointers in C. And in more ways than one, like one is everyone is afraid of them or at least business are afraid of them. And secondly, they actually help establish relations. So that's an interesting analogy that came to mind. Interesting. All right, so on this part. Yep. Okay, cool. So I'm going to quickly just to confirm the screen is still visible, right? Yeah. Okay, perfect. All right, so yeah, so we'll just quickly go ahead and create the tables and things that we've just spoken about rumors, tidbits, sources, etc. And I did that sometime ago. And so now what we'll do is we'll try to write some queries based on these. The very first query, and again, I'm going to start with very fundamental queries involving rejoin, and then we'll sort of graduate into some more complex queries. The first, the very first query that we want to write is say, you know, name all the sources of a given rumor, you know, for example, if there's a particular rumor, which we're looking at, a big company is going to remote, then we want to list down all the sources that are associated with this. Now, now the actual query some of you might have already written it in your head. And that's fine. What I want to come to is that this is a very specific type of query. It's called like, what I personally call it like a positive lookup, where you're looking for presence of data. You're trying to find things that are there already or are present in the DB, a positive query. And whenever we are looking at a positive query where we select data that is present, we are typically looking at an inner join. You can also consider inner join as an assertion. Like when you say inner join, or when you start adding, or when you execute an inner join, you can be sure that only rows, and now I'm back to the tables. So only rows that have a matching element are going to be selected. So anyway, that's an assertion like in the result set, there's not going to be a row which will not be matching to our predicate. And so in this case, for example, if you were to write all the sources of a given rumor. So let's say select rumors, let's give them rumors. And then now, like I said, we're going to inner join sources. Now, if you want to look at our, I can't go back to our schema a little bit. So rumors and sources. So the way we think about writing a join is that we always start looking at which set. And now I'm going to pause calling it a set and I'm going to call it a table. But in your mind, know that when I say table, I actually mean a set. And so now which table contains the data that we are looking at or we want. So what's the outcome that we want? So in this case, the outcome is that we want rumors and we want sources. So we want to join the rumor set with the sources set. And we know that right now, if you look at just these two tables, there's no apparent connection between them. But obviously, like, I'm not going to insult your intelligence. And I assume that you know that join tables are for this reason. So that initial sort of basic thought gives us the idea that the data that we need is spread across three tables, rumors, sources, and rumor sources. All the source names are in the sources table. And then all the rumor names or rumor descriptions are in the rumors table. And then the join table puts them together. So that's where we start when we want to start writing our join. Yes, one is one request. And it is, can you like put the schema in the form of the create table statement somewhere in the gist or something so that people can get in front? Okay, yeah. Maybe we can create the screen here, but then you might have to decrease the font. Yeah, I'm just thinking how to do that. Okay, you know what? Okay, I'll put it in the gist and I'll share the link shortly. Let me, I'll have to stop my screen share for that. But let me do it. So it'll be easier with that. Okay, another question. Sorry, I couldn't actually hear you. When someone says bridge tables, I imagine a club where all gentlemen are playing cards. Bridge, yeah. Okay, so here's the gist link. But I'm not sure. Cool. So I didn't understand the question about bridge tables. Meera, would you mind elaborating? I think our join tables also called bridge tables. I honestly have no idea if they're called bridge tables. If like maybe Oracle or some other database calls in bridge tables, it could be it. But yeah, I mean, in a way you could say that they're bridging two tables. So semantically, it makes sense, but I've never heard them call that. But for, I mean, there's no reason why you shouldn't call it.  But typically, yeah, join tables is what I've always heard them call. All right, back to screen share. Okay, so far so good. Do we see this? Is this screen visible? Yeah. Let's see what data I have, if I have some data. All right. So now back to our question where we want to list down the sources of a particular rumor. So let's quickly write this because there are some more interesting things that I want to show. Okay. So the first join in this particular query is that we want to join rumors with rumor sources. And again, we are using inner join. For the reason I just explained, we are looking at positive data. We are trying to query for data that exists. And so for that reason, we're going to use inner join. And then the next inner join we need is sources. So if you look at this, at any moment, if whatever is on the screen is not visible, pause, and let me immediately, let me know immediately. I'll take one to explain. But here's our basic query looks like from rumors, select or inner join. So rumors are going to be inner joined on rumor sources, where rumor source dot rumor ID is equal to the rumor ID. And then we'll go one one level further. We'll join the sources on sources with rumor sources. And that'll give us data from all the three tables. We don't want to select everything. We can probably select something like rumors dot description. And then sources dot name. And that should give us, in case it's not visible, at the bottom here is our data. So for example, the Brangelina rumor was sourced by a bus feed and tablids. And then Falcon, whatever landing it, if you do a source of bus feed, et cetera, et cetera. If you look at our rumors table, there are a couple of rumors that have no source. Severus, Lili, for example, Luka, my father of Broadway musical. These two have no sources. And because it's an inner join, they do not show up here. So only three. So if you look at source description, there are three unique sources. And only those show up. So actually, our next query is, what are some unfounded rumors? And when I say unfounded, it means they don't have any source. And so now if you want to write a rumor that does not have a source and that does not is our clue. Whenever we think of a does not query, which means we are querying for a whole, or data that does not exist. And when your brain or it should automatically make the connection that whenever you're looking for holes, you are looking at an outer join. Whenever you're looking at an exclusive kind of query, like, there are two, three different kinds of queries, like some of, one of, any of, all of. I'm not going to explain all of them. I think most of these terms are understood. But the main thing is this, whenever you're trying to query for absence of data, inner join shouldn't be on your mind. And so in this case, we'll sort of take this query that we started writing and we'll change it to an left outer join. And similarly here, also a left outer join. And now if we execute this, we're going to see some interesting tidbits. And that interesting bits is that the couple of places where there was no source, they've showed up here. For example, Severus has no corresponding source and Broadway musical has no corresponding source. For now, ignore this record. It's a special record. I'm going to cover this a little later. This is a mixed record. So for example, Brangelina does have a source here. And yet it shows up here. It's because of the data corruption. And I'll try to cover a technique where you can avoid some of these things. But for the purpose of this particular query, if you want to find out rumors that have no sources, what we want to do is that we want to add a where condition, where clause, where say sources, rumor, if we do this, Severus, Love, Lily and Broadway musical. So these two sources, these two rumors have no corresponding source associated with them. And so this is what I call as querying for whole. One quick thing before we move on to other more complex queries. If you notice the moment I changed one inner join to an outer join, I also had to change the other inner join to an outer join. And why is that? The answer to that, again, is very, very fundamental to understanding multiple table joins. I highly encourage you to experiment and play with this at home or on your PC cool consoles or your editors. Create a schema like this, add some data, and then try to do mix and match and see what data you get. I'm going to explain why we had to do left outer join here. And the secret to that is how joins are executed or calculated by the DB engine. So as far as databases go or DB engines go, the joins have worked from left to right, left first to right most. And every single join creates the entire left half of that as a set in itself. So for example, we started rumors, which is our left most set. Rumor is joined with, left outer join with rumor sources. So as far as these two sets are concerned, rumor sources is the right side and rumors is the left side. And then we've seen what happens with this query. Then the moment we add the third table in the mix, which is in this case sources. So when we come to this particular join, left outer join sources, s on r s dot source, r equal to s dot id, for this particular site, the right-hand side set is sources. Whereas the left-hand side set is the result of this join. And this is why I was so particular about you not thinking in terms of tables, when it comes to join, because joins do not operate on tables, joins operate on sets. So the left-hand side set in this case is the result of this join. And so now what happens is like if you mix and match, it is going to give you a slightly unpredictable result or rather not unpredictable, surprising results. Because if you're used to thinking in terms of table, then you're looking at the table data and then you will try to work out what is going to be in our results side based on the tables. Whereas when the joins are calculated, dv engines are looking at the result of this join. So in this case, what's going to happen is rumors, when left join with rumor sources, produces a certain output. So for example, now I have joined on everything. And this, as far as our join is concerned, this is our table. And so now if you look at the second half of this, we're joining sources on rs.sourceid is equal to source.id. In this case, rs.sourceid is going to be null here. And because this is part of the left side, the left side itself is null. It is not going to feature in our result set. I'll repeat again for the second half of our join, left outer join sources, which is what you call a multiple table join, right? Now we're running between three tables, table number one, table number two and table number three. When we're joining table number three, table number three is not joined with one and two. Table number three is joined with the result of joining one and two. And the result of joining one and two has these null entries. And so because our original definition of left join, it's a pick entries that have a match on the right side or are null on the right side. But you must have an entry on the left side, which means the moment we do an inner join, these entries are going to vanish. Every time you do an inner join, let's say you add any inner join in here, it will treat the left-hand side and right-hand side as your regular joins and then both side match must be present. And it's not very difficult to see. You can just add it here and execute it. So these two rows are going to vanish from our data set and it will effectively act as an inner join. So can someone tell why we did not get any single result in this particular query? Quick answer, anybody? I think there are a few answers. Yep, there you go. Yes, I think absolutely. So I'm glad so many people were able to answer. So the reason is this qualifier immediately removes all the records from the result set because there is no way that RS.ID is going to be null because we've added now inner join. Whereas obviously when we change it to outer join, which means there are going to be holes, this gives us our original query where the right-hand side is null and the left-hand side is not null. So far so good. So the next set of queries that I want to explain is the difference between having conditions in the predicate, whereas on, for example, this on clause, it's called a join predicate and the where clause. So for example, you could put this RS.ID even here and it would still give us some results. We don't know what those results are going to be because we haven't learned what that means. But you can see that it's not a syntax error. There's no error. It just returns something. And I don't know that looking at the result doesn't make any sense. It's just written in null on the right-hand side for everybody. So let's go look at what that means, what it means to have conditions in the predicate and conditions in the where clause. So number one thing is whatever comes after on the predicate is our original selector for the join. If you remember the 3x3 join diagram that we had done on the whiteboard, this predicate is going to decide what row comes and what row goes. So when you are trying to query something like, say again, holes, for example, there are some techniques where you could put conditions in the predicate and it will give you different results. Now here, for example, in this particular case, when we are joining say rumors with rumor sources, by default, we are joining on this one single condition that whether rumors or ID is equal to rumors RS or rumor sources or rumor ID. I can add another condition here, something like rumor-source.created-act, I have a timestamp here. Supply that, yeah. So I can add a condition like rumor-source.supply-act, less than some 2020. 20 or there's 21. What this means is that we are adding a join condition now based on not just the join column or the column that feel like a join, but we are also adding like a random condition or maybe not so random, but we are adding a condition or a qualifier as it's called for the join. What this does is, from a fundamental understanding of join, this changes nothing as far as the join calculation is concerned. What it changes is our understanding of the result set. Now the way Postgres or MySQL or any other DB engine works is that it's going to look at these two sets. It is going to calculate your working set where every row is joined with every other row and then it's going to apply this predicate to that particular join row. So now rumors, for example, has three columns and rumor-sources has about one, two, three, four columns. So now this, between the two tables, you have seven columns at your disposal, seven values from the tables. And so you can write any condition on this. For example, you could also write on one equal to one and that will also work because for the DB engine, it doesn't matter what you put here. As long as it's a Boolean and it's predicate, it's going to simply work. And so now the results are not surprising. So if you look at it, you have so many results on 3035. It's what it has done is it has joined every single row from left-hand side to every single row on the right-hand side. It's because the predicate always returns true. So in our original case, what happened was it applied this predicate rumors or ID is equal to RS or rumor ID. And there were only four or five rows or four or five combinations where that was true. And because you're doing left out a join, it also added the right-hand side null row. Never forget that. And so that is the main thing you want to understand that when you think of a join, don't always think in terms of the two columns that join those two tables. But think of that as literally like a joining or a welding for those two sets. Now if I add a condition like supply that, how does that affect what comes in our result set? Let's say there is a rumor which has a source, but the supply that timestamp is say in August. Here we're querying for something that is before 31 July. Now what will happen is in normal case, you will get every single matching row on the right-hand side when we only do a match on ID and rumor ID. Whereas when you add a qualifier or additional condition, that also is going to be taken care in terms of a join. Now the key difference is this. If you put this condition in where clause, where clauses execute after join clauses. Which means this condition will be applied to the final result set. Whereas in join, especially with left outer join, if you add a predicate here, what will happen is there could be some rows where the ID is equal to rumor ID. But the supply that is in August, it does not match. And in that case, it will produce a right-hand side match. So let's see if this works and what it gives us. Is this clear? Is anyone able to follow? I can repeat this as much as needed. For example, now if you look at it, so let's add this rumor ID. So now if we do it without it, there are about nine rows in our result set. And some most of them are, if you look at the supply that they're all in September. So in our current result set, when we join, we are joining based on the ID match. But the moment we add another condition, a qualifier, on the date that match goes away and we have right join. On the right side, there are null entries. Is this clear? Like is this part because this is fairly critical to understanding how joins behave, especially when you add predicates that are not intuitive. So now in this case, if you were to query for rumors that were, say, sourced in only July, right, or rumors that were not sourced in July, for example, let's talk about holes. So this query, what it's giving you is that any rumor that does not have a corresponding supplier in the month of August or before August. And so whenever you are thinking of holes, also think of where clauses and conditions in their clauses versus conditions in joint clauses. It's a very powerful construct. Once you understand that you don't have to join a table based on the ID match alone, then you can expand this pattern into many, many different ways. So far so good. I'm going to move on to a different or new category of queries in join, where aggregates come into picture. Now, let's say if you were to find a rumor that was exclusively provided by somebody, some source, let's say there is a D between the source and us, and we're going to pay that source on exclusivity.  And so we want to find out who or which rumor was given to us by a single exclusive source. Now, a single exclusive source is often a signal of any or rather all of query, where it is not enough to look at a subset of the data. It is important that we look at all of the data. And again, the moment you're looking at holes or all, you're always looking at inner joints, outer joints. And so in this case, if you want to write that query, so the query will go something like this. So you want to write something like select, let's say for now we'll do select star. So this is a base query. Again, like we start thinking in terms of working sets. This is our base query where we are getting every single rumor and every single source associated with it. And now what we want to do is that we want to sort of gather all the sources into like a single collection that we're going to write. And this is where aggregates come into picture. And again, I'm not going to in depth about aggregate, but I'm going to show you how to write this particular query. And specifically how it ties with our joints and our understanding of joints. So here, what we want to do, by the way, there are many, many different ways of writing this query. Gloomba is not the only way to write this query. There are other ways. If you're curious, I urge you to sit down and experiment and figure out so now if you look at this, what it is giving us, I'm going to explain what's happening in a little bit. I'm adding description for the reason just to make sure it's a little more readable. In a normal query, you probably wouldn't add description, but here is what is happening. This rumor, Brangelina was sourced by null three and one. Then the second rumor was sourced by one and four. This broadway was sourced by nobody. Severus was sourced by nobody. And then Luca, my father, was sourced by six and five. What this gives us, whenever we are looking at something like exclusivity, or all of, or each of, we want to aggregate all that data into something. We want all the data to be present. And for that reason, you always have to think about left outer join or left join. Because the moment you do inner join, some of these will basically vanish. They won't even come. For example, if we do this, let's see what happens when we do inner join. Now, the two that did not have any source, they've vanished. So in this case, so for example, that won't make a difference because when we are looking at rumors from an exclusive source, which means the source is always going to be present. And so in this case, we are okay to do an inner join because now we are not looking at a whole, but we are looking at every single source ID. And so if you want to, what happens is if you were to write queries based on where clauses here, then these null and other entries, they're going to get left out of the working set. And then you won't be able to write each of kind of queries based on this. So this is where your aggregations come into play. Aggregations coupled with inner joins or outer joins are very powerful. You can also do other kinds of query where you're looking at a source that was, for example, the combination of sources, like two different sources sourcing something. And again, in that case, you're looking at a whole for source one, a whole for source one, and a whole for source two. And then when you want to combine them, you must have left out a join because now your exclusivity is gone. I'm going to take a small pause here and again ask if things are clear, not clear. Also, I want to do like a time check, how much time is left, et cetera. Any questions so far that have come through? I don't see any. Okay. Fenyon has questions. Okay, there's one question. Which tool are you using to run these queries? So this is a tool by JetBrains called DataGrip. If you're serious about writing queries and using relational DBs, use DataGrip. It's amazing. It has impeccable auto-complete support. It supports different kinds of data sources. Basically anything SQL, DataGrip is quite fantastic. I'll link to it if you want. But DataGrip is what we're looking at. Okay, cool. So because there are no more questions, we're going to move on to... I want to cover recursive and lateral joins. Okay, thanks. Okay, cool. So before we move on to say recursive joins, and I think we are also sort of running short of time, if I'm not wrong. Zainab, what is our tentative time limit? This one, 8.30.  Sure. I'll try to wrap up in the next 10, 15 minutes. We've covered the core principles, but I want to make sure we at least cover recursive joins once, because that's something people don't see often in their everyday programming. So I'll quickly summarize what we did, what we learned so far. Then we'll look at the approach to write joins and then we'll move on to recursive joins. I think we have that much time. Like we should be able to cover all that in about 15 minutes. So the number one thing I'll go back to is the set diagram. We did 1A and A111, the rectangular join followed by the different kinds of joins and how do you start? Now, the question is, what do you do with this knowledge and what do you do with this understanding and how do you actually start composing more complex joins? And then that's what we were sort of looking at when we tried to write some of these joins that we always think in terms of inner and outer and left and right. And we look at, are we looking at whole or are we looking at presence of data? If you're looking at presence, then you go for inner join. If you're looking at holes, you go for outer joins. The other thing that we covered and which is important if you want to compose multi-table join is always remember our 3D level join that we did. For any join or any join clause in your complex join, it's always a join between the left-hand side set and the right-hand side set. And so if you're getting confused about what will show up in the result set and what will not show up, just add brackets for your own understanding. It's not syntactically correct, but just add brackets for your own personal understanding. You can also do indentation or whatnot. And then solve each bracket, the innermost bracket first and then go outwards. That is one strategy that I used to employ when I was new to join. But the understanding that it's always a set and we go from left to right, that is the most important thing. And the second most important thing that we learned which you should intuitively understand is the predicate. Now if you're used to writing queries using ActiveRecord, the moment I say rumors and rumor sources, the first thing that's going to come to your mind is rumor.id equal to rs.rumor.id. And that is where I want you to take the pause and think that is that the predicate you want. And like I said, you can also add other conditions to the joins. So mentally, when you're working out what the result set looks like, look at the predicate and then see left and right parts, whether there's a match. So on left join, if the predicate comes to true, then it's going to get selected. Or if it comes to null, then it's also good. If it's false, then the left side is going to get selected. Whereas for a left join, the right side is obviously going to be omitted. I repeated this 10 times so far because it is that important. Predicates, that's one big lesson. As long as you understand how predicates affect a particular join, I think you will understand joins just well. Okay, cool. So I'm going to take a pause on specific joins here. We've covered inner and outer and we covered left and right, which is what most of the joins that you will write will be. Now let's look at recursive joins. And what does even recursion mean? I'm going to quickly show you how the labels table looks like. So we have things like computer science, operating system, etc.  Computer science and arts and humanities are top-level labels because they don't have any other parent. And so now let's look at how a recursive query works. And we are going to use the understanding of joins that we have built so far in order to construct a recursive query. Can someone tell me what are the two fundamental things of a recursive function? Like what are the two things that are must for recursion? Does anyone want to volunteer? Base case is one. What is the other? Self-reference, sure. But that's not... I mean, sure. Without self-reference, there's no recursion. Well, base case and termination clause. These are the two things. Often, base case and termination clause are all into one. But the two things that are must for deterministic recursive function is a base case and a termination clause. And so what posgras... So this particular syntax is specific to posgras. Other databases also support similar syntax. They're not very, very different from what we're looking at here. But there might be minor differences in how the syntax is structured. I don't think that should ever stop you from writing a query like this. Now, the query we're trying to write here is very simple. Given a particular label, find out all the disinvents of that table. Now, if you've done any kind of comparative programming or if you've taken an interview at a big company recently, you likely understand what depth first search means. So here, we're basically doing a depth first search. Let's say we started computer science as a top level or the first label. Then we want to look at every child of computer science, which in this case is operating systems, device drivers, software engineering, data science. And then again, each of their children in turn. And so without any kind of support for recursion, this is not possible unless you write a recursive query. In this particular schema, it's not possible to write that query. Which is why there is a special special case constitution called recursion. By the way, before we write this query, I want to quickly add that this is actually a very inefficient way to store this particular data that are better structures, like adjacency metrics and L trees. If you want to store trees, those are slightly better than this. On a small dataset, this is going to work just fine. But if you're looking at, if you're writing a recursive query on a table that has 100 million rows, it's not going to terminate and take forever to run. Based on, obviously based on indexes and other things, but recursive queries are expensive and they are best written for smaller datasets and not for larger datasets. All right, with that, let's quickly look at a structure of a basic recursive query. So this is how the syntax looks like, don't try to read it all at once. I'm going to read it for you and I'm going to explain each and every part. So the structure is like this with recursive, some label here, some, you can say arguments, just like a function and then the function body. So for all practical purposes, we could consider this as a function, because it's not a function truly, it's just a set operation. So back to our two critical things that are needed for recursion. The number one is base case. We must have a non-recursive case. So like for example, if I, before that actually we do that, let me execute and show it to you. So when we execute this, it actually gives us, so I'm looking at number four right now. So all the children of node number four, which is software engineering. So in our original case, software engineering has four and, so we have programming languages, OOP, functional programming, and then 1911, so which means interface aggregation, classes, objects, and then esoteric languages. So this is what we are looking at when we are looking, we are trying to get all the descendants of software engineering. And so when we execute this, that's what we get, which is our top-level label, then the three children, and then the three children of the second, the first child, and then esoteric languages. So this is what we get. So this is what we call as a base case. We are starting with software engineering as the first label, ID number four. That's our base case. Without that, there is no query. And you can see if you try to, say, comment this out, and try to execute it, who gives us a very nice error. Error recursive query, all labels does not have the form non-recursive union all, which means we must have a non-recursive clause in it, which is our base case. So it's going to start the result set at the base case, and then it's going to recursively collect results. The other thing I'm going to show is that if you try to do left outer join here, let's see what happens. Here you go. So recursive reference to query, whatever, all labels must not appear within an outer join. And why is that? The reason is simple. With outer join, our result set is unbounded. What do I mean by unbounded? Every time you do an outer join, you are going to get queries that are null on the right-hand side, and your recursion will never terminate. You will keep getting nulls on the right-hand side. And that whereas with inner join, what happens is, when our query reaches esoteric languages or when it reaches interface segregation, there is no child element for interface segregation, which means no further outer inner join is possible. Same with esoteric language. So these, you can call the leaf nodes as our termination clauses here. And for that reason, postgres will not allow you to add an outer join because it is going to give you an unbounded query. And the recursion will not terminate. Again, I think there's a question. You mentioned recursive queries are good for small tables. Is that only the case for recursive queries? Okay, so fundamentally, small tables is what I would say is an implementation detail or a performance detail. And when we are trying to understand recursive queries, we don't need to consider that. But from a practical perspective, you definitely need to consider that. For example, how many columns are indexed? How many columns are not indexed and what are the performance characteristics of your query? So these are some of the things you need to look at. And these are very data specific things that postgres or you cannot tell beforehand, before looking at your data. But there is no rule. We say that you should only use no thumb rule. That says that you should only use recursive queries for small datasets. You can use it on large datasets. If your database server can handle it, go for it. What you should have is an intuitive understanding of how recursion works. Because I'm not 100% sure if there's any thing like tail call optimization here and those kind of things are not exposed on the query level to us. So from looking at the queries, it's very hard to say whether there'll be tail call and other optimization applied here. And so performance characteristics, like I said, are very, very dependent on the actual data. But don't let your data size stop you from writing a recursive query. By all means, once in your lifetime, you should write a recursive query on a production database. We have one more question. What is the use of with recursive? So we might be going into a segue into CTs, but up to you. Like I said, this is what you call a common table expression. I don't think we have enough time to actually cover the entire thing. But with recursive is basically a syntax form. Recursive is just a keyword here. We can remove it. The recursive keyword allows us to refer to all labels from within all labels. That is why it's called recursive. But the general form is this. You have with some label as and here a set here. And then you can say label one and say label two as set, etc. And then on that, you write some select query. This is the general structure for a common table expression. It's called common table expression because these sets or tables, as you call them, they are common to all the queries. They're available to the other tables and to your main query. And so this is something like a let block of a programming language. Like if you've done any kind of scheme or a camera of Haskell, there are let blocks where you define a block and then you give it a name or a function, name function. You can even think of it as a name function available to you at query time. So that's what this syntax is. With recursive is just a special form. If you want to add a recursion to it, then you need to add that keyword. Without that, Postgres will not allow you to refer to itself. The reason being the same thing, if you don't declare it as recursive, Postgres will not know if you're writing a bounded query or an unbounded query. And again, when I say Postgres, I mean MySQL or any other. So I'm going to quickly explain the recursive query and then we're going to stop here. We have to put it in another time. But here's how it works. This is our base case, which you've already seen. It is unioned with our regular query. And the way this works is every time the recursive call happens, the result set is added. That particular result of that recursive call is added to a final result set. And you keep accruing your final result sets. Which means what is happening here is, this particular part join is exactly the same as any inner join that we have seen so far. We are joining labels, our table, with all labels are currently running a result set. So in the case of base case, when the recursion runs for the first time, it's going to have this particular set in all labels. So on the first execution, you got the base case, which is our result set number one here at the bottom. In the second recursive call, you get all children of the first case, which means all of these here. All of these here. Because what happened was, now this first record four acted as a different right side table or right side set. Then you get a join on that. Then on the next recursion, this next set acted as the right side set. And then you got this then and likewise. So this is how you typically write recursive queries. The thing I want to tell and what I want you to understand is that if you stop looking at this syntax, it's just your regular join query and nothing fancy. All we are doing is we're just doing a join with a current table or current set that we're working with. And that with recursive syntax allows us to do that. It's just a special form of common table expressions. Cool. I think we are completely out of time. We are in fact three minutes over. I'm going to stop here. But if you're interested or if anything stood out as like you didn't understand, shoot me a question, Twitter here anywhere. And I'm happy to chat. Those who are registered for my class that is coming next this month, let me know what we'll do is we'll not cover the same thing again. We'll probably try to cover something more exciting because we already covered this. But in any case, I'll take a pause now. And I'll open the floor for questions if there are any. Otherwise, that's it. Thank you very much. All right. Thanks a lot, Swannan. That was very insightful. And I guess I think you and I learned a couple of things over there. We still have, I think, we can take questions for another four or five minutes more. So if anyone does have questions, please fire away. Meanwhile, maybe Swannan, you can just display on the screen your Twitter handle or something where folks can get in touch with you. Oh, yeah. That's a good idea. Let me do that. So the takeaway from this, apart from all the said and settled things that we talked about, is that if you want to learn like, you know, open up your PC or console, console, create those tables and fire away the queries. That is the best way to experiment and understand and learn. And that's how I learned. All right. Meanwhile, we have a few questions coming in. The first one is, do recursive joins work on JSON types as well? Yeah. So, okay. Cool. Yeah. So JSON is, like JSON types are nothing special. If you can, so here's the thing with JSON. If you have a JSON value in a single column, then you need to unpack that value in a table or in a set. See, for Postgres or for any relational DB, there are types of values. One is your scalar, actually it's not scalar, but one is a value expression and one is a table expression. So table expression is typically when you have multiple records and a value expression is when you have a single value. It could be, it could be like an array. It could be a multi-valued value like an array, but still at the heart it's a single value. And so you can't join or you can't do table expressions or table operations. So that some data in a JSON column, for example, you'll have to unpack that. And there are a couple of SQL constructs for that. But if you want to Google the word you're looking at is unpack. So you want JSON into a table. And then once we have a table, then the world is your stage. You can write and enjoy it. Whatever join we looked at, it can be written on top of that table or a set, as I said. All right. Another request is can we get access to the queries used in this talk? So all the SQL stuff that you put. Maybe you put it on the gist or something. Yeah, I'll do the, yeah, I'll do an gist. And I'll also put the diagram, the hand-on diagram that we did. I actually, I have a blog post on that as well. I'll link to both. I'll share that diagram. That diagram is very useful. Like if you're writing a query, put the diagram up in the front end. Great. I guess that is it then. Thanks so much. There are two questions from Pranjal. I'll, Aditya, I've shared both of them with you. Oh, is this from YouTube? Okay. So the first question is what is database sharding? Okay. Wow. Okay. Yeah. Out of, sorry, man, out of scope for this. Sharding is basically partitioning of data. It's one more partitioning, project partitioning. But it's too detailed topic to cover right now. Maybe, you know, Aditya can go talk on it later. But yeah, definitely out of scope for this. This is great management lesson in delegation. I think there was one more question from Pranjal, where he was asking about what is the use of width recursive? I don't know if you already addressed it. Yeah, we covered that. Yeah. Okay. All right. Great. Okay. So on this note, Swannan, thanks for this extempore talk. I hope you have lots of water and enough rain. Aditya, thanks for patiently sitting through this. And I think like Swannan said, we really hope to see you on some of these sessions. So folks, we'll continue with the SQL discussions. I think Swannan can post some of the resources also on the Hasgeek page. And those can also be shared as updates with participants and for anyone else who wants to see. We will come back with another session on SQL, maybe a war story session, where all of us can sort of share all the horrible things that have happened and what kind of like gas fires we've doused and whatnot. Having said that, feel free to suggest to us topics that are of interest to you. And if you'd like to have discussions around specific topics, Swannan runs a very wonderful community called Papers. We love to check it out. And yeah, that's about it. Have a good evening. And hopefully you shall have, you should be able to do both of your last joins going forward from now onwards. Thanks again, Swannan, Aditya. Thanks everyone. Thanks for giving the opportunity. Thanks, Swannan. Bye-bye.