What is a JOIN in PostgreSQL

Rob Percival
A free video tutorial from Rob Percival
Web Developer And Teacher
4.5 instructor rating • 41 courses • 1,937,015 students

Lecture description

This important lecture explains what a JOIN is, and why it is essential for relational databases to support this.

Learn more from the full course

The Complete Python & PostgreSQL Developer Course

Build 9 projects—master two essential and modern technologies in Python and PostgreSQL

21:39:30 of on-demand video • Updated January 2021

  • Understand software and programming with Python from beginning to end
  • Create Python applications ranging from beginner, basic applications to advanced, useful and engaging applications
  • Learn how to extend Python applications by using a professional data storage which is PostgreSQL
  • Understand advanced concepts extremely useful in jobs and interviews, such as data structures and algorithms
English Hi and welcome back in this studio we're looking at what is a join this video is going to be a presentation style video just because it lets me show you table samples and diagrams and things like that a lot easier . So bear with me for this video is one of the few videos that is going to be in a lecture format and it's just a lot easier for me and for you as well. And this really we're looking at what Joines are and Joines are essential elements of relational databases and on the Pascua sequel and my sequel are relational database systems and joins are key elements of those systems because they allow us to retrieve data from multiple tables at once instead of being limited to getting data from one table. We can now get data from multiple tables and join it together using sequel. And that's really powerful because the whole purpose of relational databases is to have data that is related to one another. For example you could have a Customers table and the customer Stabil can be related to an Orders table . For example the orders could also tell you which customer did which order without joins you'd be limited in finding out customer details for a particular order but with a join you can select data from both the customer and the order tables at the same time. And that's really nice. Let's go over the different types of join. I'll give you a few examples of what you may be able to do with Joines and why they're powerful. But first remember that joins olic sense. We've looked at sets. They are unordered groups of unique elements and we saw this in Python and Joines treat rows of data as if they were sense. So we can perform a set operations on the tables and I'm going to give you one example of how a set operation can be translated to a join and how they're similar. Remember the Intersect operation the set intersection is the elements common to two sets. For example as we saw in this diagram a while back we've got set a which has the elements 1 3 5 and 6 and we've got a set B which has the elements 2 4 5 and 6. As you can see five and six is common to both sets. So if we were to calculate the intersection between A and B we get the elements 5 and 6. So 5 and 6 are the elements common to both sets and the first and most common type of join We're going to be looking at today is akin to the intersect and that's the inner join. So the sequel INNER JOIN is similar to the set in dissection. And here's a quick description of where the indigenes. But we're going to look at an example as well. THE INNER JOIN selects rose from table 1 and from table to where they match the selecting column. So it's only selecting rows when they match the selecting column. And we're going to look at an example just now imagine we have a Customers table where each customer has a unique identifier an ID and a name. So for example here I.D. one is Jose I.D. to Ralph and so on. And then we also have an Orders table. This orders table and gives a unique identifier to each order. Each order has a customer ID and also a product that the customer bought. So here Order Number one you order with I.D. one tells us that customer 1 bought a chat order number to tell the customer one bought a pen or the number three tells us that customer 1 bought a monitor an order number for tells us that customer 3 bought a pair of headphones. However just from looking at the Orders table we don't know the customer name to do that we would have to go to the customers table and see OK. Customer 1 is Jose. So orders 1 2 and 3 are for Jose and Order Number four is for Anna and we would have to do that manually which is not ideal. What I'd like is to be able to query my database in a way that gives me the customers and the orders that they have done. And that way I don't have to go looking for the customer name in a different table and there's a way to do that which is with the inner join the inner join to be constructed like this we would first select star from customers but we're not only selecting from customers we're selecting from customers or join orders. We are selecting from the result of joining the customers table with the orders table and to perform this joint. We have to give it a selecting column and we do that by seeing on customers dot ID equals orders dot customer id lets go over that once again with one or two tables and the first thing we do is select stuff from customers in or join orders. So were joining on the table on the right would join in the table on the left. Customers with a table on the right orders on the customer ID equals orders not customer ID. So were saying join those two tables when the customer ID is equal to the orders not customer ID and what that creates for us what this inner join and result creates for us is we're selecting everything from the resulting table which is customer id the customer name and also the order ID order customer ID and the order product. So what happens here is that because the customer number one has an order in the Orders table we get all the data related to customer number one and to the order that they have done. And we know that the order is belonging to customer number one because the customer ID is equal to the orders not customer ID. Thats one that matching call them or selecting call them does. Then the next row of the orders table is also for Customer ID 1. So that also gets shown there. We get another replica of the customer data and we can't. Also the order. And finally the third column the third row rather of the orders table also for Customer 1. So we get the same data again but this time with a third order. And finally the last row of the Orders table is not for Customer 2 but for customer 3. So what we get is the data belong into customer 3 and their order. Notice that this is the inner join it gets data from the table on the left and from the table on the right. If they match the selecting column. So here the the the result on the table that weve got only has customers 1 and 3 because they are the only customers that are present in the Orders table. So that would be the result. And notice that the result in table is what we are selecting from. So we are selecting from the customers in a join orders on customers ID equals order to our customer . And so thats the result in table. Okay. The next type of join is the left join and this is also a fairly popular type of join this join selects all rows from table 1 on the left and the rows from table to access table one has been able to on the right if they match. If they don't match the data for the right table is blank. So let's have a look again at an example. We've got the customers table and the orders table and now we're doing select staff from customers left to join orders on customers ID equals orders dot customer ID as you can see the customers 2 and 4 don't have any orders. So let's look at what happens in the resulting table. The first thing we get is customer number one with his first order because in a bid to the second order customer and the three with his third order. And then we can start becoming a one with third or and guest customer number three with his or her first order. And then he had with a difference begins but we've got so far is the same as the inner join. But now we're going to get the data from the left table. Even if they don't have anything matching on the right type. So we get customer number two with a blank order and customer number four with a blank order. So this is a left join. We've got all the data on the left table and we've got data from the right table where it matches and if it doesn't match then it's back to the next type which is the right join. And I'm sure you can guess now what this is going to do. And it is the opposite to the left join and they select all the roads on the table on the right and then the rows on the table on the left. If they match and if they don't match then the data for the table on the left is blank. So here's an example. And notice how I have changed the orders table. Now Order Number two has no customer ID associated with it. We can pretend that this was part of a promotion. Nobody bought this item was given away for free for example. So what happens now is I'm sure you can guess what happens. We get customer number one with his order customer number one with his other order which has now order number three. And then we get the second order but with no customer. So we getting the data from the right. But in this instance. Order number two doesn't have a customer. So it is blank and then we've got Anna with this other word. So what we're showing here is data from the right table and then we are putting in data from the left table. If it exists and if it doesn't exist it is blank. But we still show the data from the right table which is orders. Finally the last type of join is the join and then selects all rows from both tables and it matches them if there is a match and if not then it doesn't match and it's blank and you can think of it as both a left and a right to join. So once again the same data as before. This time we're doing a full join. And what happens is that the first wrote the first customer with his order then the first customer with his other order number three then we've got the second customer with no orders. So it's blank and then we've got the second order with no customer. So the customer side is blank and then we've got the third customer with order and then we've called the fourth customer with no order. So as you can see we've got all the data in those tables and we're matching the data where possible . But if these are cannot be matched then it is just blank for the part that's missing and that's it. As you can see that is not particularly useful in an application because there's too much too many bits missing essentially. But although it can still be used and it is still used but maybe it is more useful if you're doing data analysis and things like that. Nevertheless you're going to find these four types of joints throughout your journey of sequel. Plenty of times. So it's important that you know what they all are. OK to review Joines are essential to relational data as it lets us get data from various tables and there is always a various and not two tables because a join allows us to get data from three tables or append tables if we wanted. And we're going to look at how to do that throughout the next few videos and joins real quick. There are a lot quicker than if you were to get the data from the table separately and matching the data in your application. So for example if you were to get the customer's data in Python and then the orders data in Python and you were to go through the data and match customers to orders in your Python application that would be quite slow. But if you do it with sequel that's really fast and you actually don't really have to worry about performance issues at all unless you have many many millions of rows. The most common type of join is by far the inner join followed by the left join but they are all used and you should know what they all are. And to recap the inner join is a set intersection. It matches when both tables are both the rows match. In both tables the left join gets the data on the left that matches it with date on the right. The right join matches with the data on the right. I guess the on the right and matches it with the date on the left and the full join gets the data from both tables and matches it where possible. OK. That's everything for this video and I know it was a bit hefty. There's a few things to cover but Joines are all identical. It just depends which data you get first and where the matching happens. That is going to change the output. Just remember the different types and come back to this and review them. If you ever forget that's it. And thanks for watching