 Hi everybody, I'm Meera. I work at a startup called Hasuna which focuses on GraphQL as a service. So we use Postgres as part of our bundle along with GraphQL but I don't work for Postgres. I just like Postgres the background so I just had it. So yeah, so I am fairly new to SQL in terms of learning some of the more advanced topics and things like that and moving on from say ORMs and such. So in that sense, the workshop by Swannan was very useful for me and some of you may have attended the fearless joints workshop as well. So I'm going to be talking about joints but before I go on to my presentation, I'll let Kreis and Aram introduce themselves and then I'll start my presentation. Okay, I'll go for my introduction then. Hello everybody. My name is Aram. I'm at Phoenix Wizard on Twitter. I am part of the engineering team. Thanks Aram. I'm Kreis. I mostly work in Python and building back-end solutions using Python. Most of my experience with SQL has been with using ORMs on Django. Only in the rarest case scenario where you want to optimize the SQL queries and figure out what actually is happening that is when you fire up the SQL editor and figure out like what exactly is happening analyze it. So but the workshop which we attended by Swannan was to give a very good introduction to the mathematical concepts which helps to understand or to build a mental map of like what exactly goes inside a query and what is it coming out. It's not just a black box anymore. You can use certain kind of assumptions and certain kind of concepts to go to understand what actually the DB engines are doing. Go to you. All right. Okay, so I'm just quickly going to share my screen. Okay, you should be able to see my SQL War Stories presentation here. All right. Okay, cool. So I'm going to talk about one very specific thing and also the thing that Swannan mentioned as if you're taking one thing away from this workshop, let this be this, which is understanding joints from a different sort of perspective. So we actually spent quite a bit of time learning that and I found it very interesting because my previous mental model of joints basically was okay we're going to have just two tables. That's not involves two or tables. It's just two tables and basically a joint statement has these keywords. I was thinking of it in terms of keywords and it'll have all these things going on and like a lot of trial and error, which is not ideal. So a lot of trial and error till I get what I was looking for. So when you're looking at like a big dataset with like thousands of lines and several hundreds of tables sort of approach hardly works as most of you know. So this mental model was not working for me at all. So we kind of discussed this in the fearless joints workshop by talk by Swannan as well and like this was what we had in the masterclass was an extension of that. So I came across this interesting quote, which I just wanted to from sellingstar, sql.com. So our goal here isn't to learn the rules for how to use group by or when to pick a left join over an inner joint. So we know we've been successful of after writing an SQL query, you can close your eyes and imagine what the computer would do and what output it would give. Only then would you be able to solve real world problems with SQL. This really a resonated, I would say with what Swannan also taught us and in general from whatever little I've seen with respect to SQL. So going forward, I'm hoping that my mental model and all my co-classmates in the workshop will also have this sort of mental model. So moving on from here, I also encourage you to check out selectstar sql.com. It has some fantastic lessons and exercises, which are fabulous. Anyway, so that is a picture of my classmates would note, but that is a picture of me trying out the relational sort of thing for the mathematical approach that Chris mentioned. So where we actually visually write out or plot out how an inner join would work. I'm not going to really get into the details of how this is done. You should check out the fearless joins thing where Swannan talks about this, but to give a very broad overview of this idea is thinking in terms of, you know, true matches, no or false matches and null matches when you're trying to join two tables. So this is for the inner join and I told you that I used to think of it in terms of, okay, joins is just for two tables, but obviously that's not the case. There are so many cases when you will have to do multi-table joins and many cases where you have to do a self join. So in those cases, this sort of a map, this sort of a mental model really helps. So I will show you a couple of examples of those as well. So understanding, so once you have a clear understanding of inner join based on, you know, null and what sort of things are excluded and what is actually present in the final join dataset, it makes it easier to extend and understand the left and right outer join based on this. So those are actually easier is what I felt. So once you nail the inner join these things kind of get easy. So what I will do is like show you some couple of examples. I already have the code setup. So I won't actually be coding this now, but I will show you the, this is from PG exercises and again, a very good website, especially if you're just kind of new and you want to try out some slightly tricky problems in SQL, not necessarily restricted to Postgres, but though it's called PG exercises. Anyway, so this is the schema for your, just so that all of us are on the same page, there are three tables, members, bookings and facilities, think of it like a sports club or something. And there are members and all their details and bookings, details of bookings and facilities, tennis courts, table tennis, swimming pools, et cetera. And all the related fields there. So I'm just going to quickly switch over to the core editor. And I hope the font is big enough in this. All right. Okay. So yeah. So the first problem is, so I'll put a list of all members, including the individual who recommended them, if any, that is the key there. So ensure that results are, okay, ordered by something. So, so I also took us over, you know, how to construct a query in the sense of the order in which, you know, the statements are executed and not the order in which we, which is not the order in which we are actually writing them. So most of the time we started off the queries in the workshop with just a select star. And then we'll figure out as to what needs to come later because the select statement is not what is executed first. So we work with, so what we need here is to start with the from and then look at what sort of join needs to go in here. So all members, including individuals who, including the individual who recommended them, if any, so in this case, what happens is members and individual, both of these refer to both of these basically come from the CD dot members table. So it is a self join. So that was one thing. And the second thing was this, who recommended them, if any, so this kind of ruled out the thing that there is a, this kind of gave the idea that there are null feels possible there. So basically, people may not have a recommendation, they would have just joined that sort of thing. So in that case, it is, since it's not going to be not null sort of thing, this will not be an inner join and definitely left out a join in this case. So, so what I'm doing here is basically have a first name surname coming together. And then I also have, so if you look at the left outer join here, I am, I'm basically self joining it with the same CD dot members. So here are the CD dot members and joining itself with CD dot members. And then it goes on to query based on this particular join. So let me quickly show you what the output looks like. Yeah. So this is, yeah, this is, these are the first two columns are the names of the members in the last two columns on the names of the names of the people who have recommended them. And you will notice that we have null values for some of these fields. So this was, yeah, this, the mental model that we spoke about in the workshop kind of made this very easily understandable for me. I've tried the same thing maybe a few months ago and probably didn't, I just winged it. I'm like, okay, let me try this, that this. So this time it was actually very, very clear to me. So that was that. And then quickly, like a multi, multi table join example, and then I'll pass it over. Okay, so what we need is a list of bookings on the day of a particular date, okay, which will cost the member or guest more than $30. So all this, this the date and the cost and all those things ideally should go into the where class. And, but there is a trick here, remember that guests have different costs to members. If you notice here in CD dot facilities as a member cost and the guest cost. So those will differ. And all this is include in the name of the facility, all that will go into the select class, which I will think of later once I have the join going on correctly, everything else I can figure out later once I have the source and the join going on correctly. All right, so what happens here is okay, I need a book, I need data from the bookings table. I also need I need to grab, you know, the members names, because yeah, so I will use this, this particular bookings dot memory and CD members members dot memory sort of that that relationship there. And this, this will also include another join where the booking dot facility ID and the facilities dot facility ID are basically having a join is based on that. So what's happening here is since we are not there, ideally shouldn't be any null values here because we all of these should have like an exact value. If it's guest, it should instead of surname, it would be guest. So, so there is no possibility of null. So we do not want any sort of null values in our table. So we go for inner join in this case. And there is a small trick here, we have to use a case also here, because we want to kind of slot it as member cost or guest cost. For that reason, I set up a case here. And then finally, in order to display it again, as in based on the filters that were mentioned, we have both and one quick thing was to, you know, basically typecast the start time and only grab the date. That was also something new for me. So that and also this greater than 30, you know, the condition that's provided in the problem. So if I run this, what I should see is, yeah. So if you see this, it's ordered in a descending fashion, the cost, but there are both, it comes as guest, because I'm doing a concat first name, surname sort of thing. So that's the reason why. But yeah, so this is how it is listed about 18 values. So and all of them above $30. So yeah, so this was a tricky one for me. I remember talking to Swannan before the workshop about this problem. He said we'll talk about it. No worry. So this kind of cleared up a lot of misunderstanding or because I was actually scared fear of joints actually. So this workshop and the fearless joints talk really helped me. And I'm hoping to learn more SQL and looking forward to more workshops from Swannan than Haski. So thank you for listening. And if you had any questions, Swannan is here. Thank you.