Locked lesson.
About this lesson
Exercise files
Download this lesson’s related exercise files.
RIGHT JOIN.docx59 KB RIGHT JOIN - Solution.docx
59.5 KB
Quick reference
RIGHT JOIN
Right Joins allow us to pull data from two tables when there is a match in at least the Right Table.
When to use
Use them when you know your data exists in at least the right table.
Instructions
SELECT [First Name], [Red Widget]
FROM [dbo].[Current_Customers]
RIGHT JOIN [dbo].[Orders] ON [Current_Customers].[Customer ID] = [Orders].[Customer ID]
This will display the last name and number of blue widgets purchased by each customer ID.
Similarly to LEFT JOIN, with RIGHT JOIN only one table needs to have the thing that you designate (e.g. Customer ID) in order to work. In this case, it is the table on the right that needs to have that thing in common. The table on the right is the second table that you list, which in this example is the Orders table.
This means if you have a customer who has a customer ID in the Current Customers table, but not the Orders table, they will not show up in your results at all, because they do not exist in the right table.
Hints & tips
- RIGHT JOINS work when the data exists in at least the right table.
- 00:05 In the last video, we talked about Left Joins.
- 00:07 And if you're not confused and you're still with me, in this video,
- 00:10 we're gonna talk about Right Joins.
- 00:11 As you expect, left join deals with the database table on the left.
- 00:16 Right join, obviously, will then deal with database table on the right.
- 00:20 And we can pull up our insane diagram here.
- 00:22 So right join will deal with everything in this guy right here.
- 00:29 This is the right table, in our case this is the Orders table.
- 00:34 This is the left table, in our case, it's the Current Customers table.
- 00:38 So with a right join,
- 00:40 the thing has to exist at least in this table in order to show up in our query.
- 00:45 So before with left join we had Tracy Smitherton, and she had NULL and NULL.
- 00:52 To do a right join, we just type in RIGHT.
- 00:56 But boom, Miss Smitherton has now disappeared, and why is that?
- 01:00 Well remember, she's ID number 7, her customer ID is number 7.
- 01:05 So if we pull up our Current Customers, and we just look at it,
- 01:09 we see Tracy Smitherton with a customer ID of 7.
- 01:13 If we pull up our Orders, we see all the customer IDs, there is no number 7.
- 01:20 So when you run this right join, there is no number 7 in the orders table,
- 01:25 so she doesn't show up in the results.
- 01:29 If you do left join, as we've seen,
- 01:31 she does show up because she does exist in the left table.
- 01:34 So it's an either-or type of thing, if you want pull her in the same diagram again.
- 01:40 If you want something that has to be in both tables,
- 01:43 that's gonna correspond to this area right here.
- 01:46 Where these two tables overlap and they both have the same thing, in our case it's
- 01:51 the Current Customer and the Orders, Customer ID, then you'll use Inner Join.
- 01:55 If you just want it to appear in anywhere here and anywhere here,
- 02:01 and you're not real particular about anything, you'll use Full Outer Join.
- 02:08 But you'll have huge results if you have thousands or
- 02:11 millions of records in your table, you may not want to use Full Outer Join.
- 02:15 Likewise, left, if the thing exists at least somewhere in the left side,
- 02:20 or in our case in the Customer table, you'll use Left Join.
- 02:24 And if the thing you're looking for is only in the right side,
- 02:28 we'll use Right Join, so pull those back up.
- 02:31 So those are joins, I know it's kind of a weird thing and it's a weird diagram but
- 02:35 this is very useful in order to pull information from more than one table.
- 02:38 And we've just been doing two tables, I mean you could have a hundred tables and
- 02:42 you can do join statements from all of them,
- 02:45 you just keep stringing them together, like we do with commas and the like.
- 02:49 So that's Join, that's working with multiple tables, and
- 02:51 we're done with this section.
- 02:53 In the next video we'll work at Subqueries.
Lesson notes are only available for subscribers.