Introduces different kinds of table joins, and explains related terminology the student might come across
- [Instructor] Now we're going to look at inner joins, outer joins, left, and right joins. In the last video, we just used the keyword join on its own. In any version of SQL I know, join defaults to inner join. And inner join is where records are shown only if they exist in both tables. It's like the inner part of a Venn Diagram. Now I've created two small tables that you won't have a Sakila database, tableA and tableB, and I've done this to show how joins work.
tableA contains days of the week with just five days from Monday to Friday. And you can see we've also got this extra field a_b_id, which is effectively like a foreign key for the b table as we will see, and that's got values one, two, three, and seven. Now if we take a look at tableB instead, it's a simpler table. It's just got ids from one to four and against those ids we've got the names say of members of staff.
So let's say that on each day, one person is required to work at the shop. Now what we can do, as we know, to join these together is to say, SELECT * FROM `tableA` a and then we'll JOIN `tableB` with an alias of b ON our sort of foreign key in our tableA knitting together with our id column from tableB.
And then we'll click Go. So we've just used the join keyword there and let's look at the results. We've got four rows. We haven't got a Friday there and we've lost Fatima as well. We've got Fred twice but no Fatima. So if I just edit that query and instead of JOIN specify INNER JOIN, you can see it returns us exactly the same thing. So join and inner join are synonyms. So we're missing Friday and we're missing Fatima. And if we just look back in the table we see, Friday has a foreign key with an id of seven and seven doesn't exist in tableB and that's why that's been excluded.
And similarly, Fatima has an id of four. And if we go back to tableA, you can see that number four is not listed in this foreign key column and that's why some rows are showing up and other rows aren't. So that's an inner join. An outer join, by contrast, would return all rows from both tables matched together wherever possible. It's probably the least useful of the joins and it isn't supported in my SQL.
Outer joins are also called full joins and full outer joins. So you might come across that terminology too. Left join takes all the rows from tableA whether or not there are corresponding records in tableB. So let's run an example. So we have SELECT * FROM `tableA` with an alias of a LEFT JOIN `tableB` with an alias of b on the same column match that we used before.
And if we run that, we see now we have five rows returned whereas before when we used an inner join, we just had four. And what it's done, it's returned us everything from tableA. We've got all five days there, regardless of whether there's a match in tableB. So Friday, with an id of seven didn't have a match in tableB and we've just got a couple of nulls there instead. So why is this left instead of right? If we reformat our query a little bit, it might become more obvious.
The way I think of it is that when you see left like this, it refers to table on the left of it. And if we had right here, it would refer to the table on the right of it when formatted in this way. So we could make this a RIGHT JOIN now, which means, show all the records from tableB because that's to the right of the keyword join. And if we click Go, again we've got five rows but this time the nulls are from tableA and Fatima has reappeared.
So in fact the following two lines of code are actually equivalent. A LEFT JOIN with tableA to the left is the same as saying, a RIGHT JOIN with tableB to the left. These two lines of code then do precisely the same thing. It's generally considered good practice when you have this sort of choice in a query to use left joins in preference to right ones and change the table name order to get the query that you want.
It can make life a lot easier when you start joining more than two tables together. Also, left join is actually a shortcut for LEFT OUTER JOIN and likewise on the right. That's why the middle of the Venn Diagram that we saw is shaded blue. You can't have a left inner join though because by definition, if it's in the middle, it's neither left nor right. What you can do and you might want to do occasionally, is return left or right records where there is no match in the other table.
So visually this would look like this. Notice that the middle of the Venn Diagram is no longer shaded blue. This can be really useful for spotting errors actually. To achieve this we can just add a where clause. So we could say SELECT * FROM `tableA` LEFT JOIN `tableB` WHERE b .b_id IS NULL.
And that returns us the one row that doesn't have a match in the other table.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries