Locked lesson.

Upgrade

About this lesson

We will discuss and write left joins to combine more than one table in a specific way

Exercise files

Download this lesson’s related exercise files.

LEFT JOIN.docx
59 KB LEFT JOIN - Solution.docx
59.5 KB

Quick reference

LEFT JOIN

Left Join allows us to pull data from more than one table when there is a match in at least the left table.

When to use

Use it when you know the data exists in at least the left table.

Instructions

SELECT [Last Name], [Blue Widget]
    FROM [dbo].[Current_Customers]
    LEFT 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 OUTER JOIN, with LEFT 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 left that needs to have that thing in common. The table on the left is the first table that you list, which in this example is the Current Customers 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 still show up in your results. They will just have NULL values in the Blue Widgets column, as they have not made any orders.

Hints & tips

  • Left Join pulls data when there is a match in at least the left table.
Login to download
  • 00:05 In this video, I wanna talk about left join.
  • 00:07 So we've talked about inner join and outer join.
  • 00:10 And with inner join, both tables have to have something in common.
  • 00:13 With outer join only one of the tables has to have something in common.
  • 00:16 Left join is similar, only one of the tables has to have something in common,
  • 00:20 but it's the table on the left.
  • 00:21 So what's that all about?
  • 00:22 Which one is on the left and which one is on the right?
  • 00:25 Well, think of the first table that you talk about, that you list,
  • 00:29 right here, this current customer's.
  • 00:31 That's table one, let's say, and table one is on the left.
  • 00:35 Table two is on the right.
  • 00:36 So I'm gonna pull up Photoshop really quickly just to sort of illustrate this.
  • 00:40 In a goofy kind of way.
  • 00:41 So say this is table one, okay?
  • 00:44 And this is table two.
  • 00:47 Let's see if I can move this guy around a little bit, there we go.
  • 00:52 So table one is on the left and table two is on the right.
  • 00:56 Let me pull up a thing right here.
  • 00:58 So with inner join we see this stuff right here.
  • 01:04 This is where the two tables combine.
  • 01:06 This sort of a Venn diagrammy type of looking thing.
  • 01:09 Where right here in the middle, this is a customer ID right, this table,
  • 01:13 table 1 has a customer ID and table 2 has a customer ID right here.
  • 01:18 They overlap so we can use inner join.
  • 01:20 With outer join, all of this.
  • 01:26 It doesn't matter where the thing is.
  • 01:28 It can be in any one of these places and any one of the parts of the table.
  • 01:33 As long as it's in something, you can use outer join.
  • 01:36 For left join, it has to exist in here.
  • 01:43 Right, so if it exists in here, even if it doesn't exist in here, it'll still work.
  • 01:48 So this is table one, this is table two.
  • 01:50 It seems a little confusing,
  • 01:51 but that's really the best way I can think of to sort of talk about left and right,
  • 01:55 and inner and out, or just sort of visualize that in your mind.
  • 01:59 But the first one is the left, the second one is the right.
  • 02:02 So to do a left join we just keep everything the same.
  • 02:05 We just call left join, and
  • 02:08 again we're using customer ID as the thing that the two things have in common.
  • 02:13 So remember last time we were doing full and Tracy Smitherton,
  • 02:16 we erased her order so she didn't have any.
  • 02:20 So if we execute this again, boom.
  • 02:24 She still exists in this output because she exists on the left table,
  • 02:30 in the customer, current customer's table.
  • 02:33 So, she's here.
  • 02:34 Even though she doesn't have any orders on the right table,
  • 02:38 in the orders table, she still exists.
  • 02:40 So if you're looking to pull data from only one table even though it
  • 02:45 doesn't exist in the other table, yet you still want to
  • 02:49 put some data in from the other table, left join works, and so that's left join.
  • 02:53 A little bit confusing I understand, it's just it'll become more apparent as you use
  • 02:57 it over time in more real life situations, but that's left join.
  • 03:01 In the next video, we'll talk about right join and
  • 03:03 then we'll be done with the joins.

Lesson notes are only available for subscribers.

Gift this course