Locked lesson.

Upgrade

About this lesson

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

Exercise files

Download this lesson’s related exercise files.

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

Quick reference

OUTER JOIN

Full Outer Joins allow us to pull data from more than one table so long as their is a match in either table.

When to use

Use it when you have matching data in at least one of the tables.

Instructions

SELECT [Last Name], [Blue Widget]
    FROM [dbo].[Current_Customers]
    FULL OUTER 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.

Unlike INNER JOIN which required both tables to have the same thing in common (e.g. Customer ID), with OUTER JOIN only one table needs to have the thing that you designate (e.g. Customer ID) in order to work.

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

  • Full Outer Joins pull data when there is a match in at least one table.
Login to download
  • 00:05 In the last video, we talked about inner joins, in this video,
  • 00:08 I want to talk about outer joins or full outer joins, as they're called.
  • 00:12 So when we talked about inner joins, the thing about it was, both tables had to
  • 00:16 have something in common, and in our case, it was our order ID, our customer IDs.
  • 00:22 Each table had a customer ID column, and
  • 00:24 those customer IDs corresponded to a single person in each table.
  • 00:28 So John Elder was customer number 1 in the current customers table, and
  • 00:33 John Elder was customer number 1 in the orders table.
  • 00:36 So as long as both of those tables have that, you can use inner join.
  • 00:40 Well, outer join or full outer join, as it's called, allows you to do the same
  • 00:45 thing except for a thing only has to be true in one table and not both tables.
  • 00:50 Like in inner join, both tables had to have the customer ID and
  • 00:53 a full outer join, only one table has to have the thing that you designate.
  • 00:58 And remember, we designated the thing, Customer ID, right here at the end.
  • 01:03 So let's go ahead and look at this.
  • 01:09 So let's just start over again.
  • 01:10 And let's go, well, [First Name], [Last Name],
  • 01:14 probably shouldn't have deleted this, [Red Widget], and [Blue Widget].
  • 01:21 And the second line, it stays the same.
  • 01:22 It's just the first table you wanna look at, in our case, its current customers.
  • 01:27 And now, we do full outer join and we want dbo.
  • 01:34 We want to join our orders table on dbo,
  • 01:41 [Current_Customers].[Customer_ID], set
  • 01:49 that = [dbo].[orders].[Customer_ID].
  • 01:56 So, so far it's almost exactly like our inner join, right?
  • 02:00 We just changed INNER to FULL OUTER.
  • 02:03 And if we run this thing, we get the same results.
  • 02:05 Now, let's look at Tracy Smitherton.
  • 02:07 She's ordered 200 red widgets and 6 blue widgets, and her ID is 7, right?
  • 02:13 So let's open our Orders and edit our top 200 rows.
  • 02:17 And let's just delete her, delete all of her orders, we'll delete them.
  • 02:21 Yep, delete.
  • 02:23 So now if we run this thing again with outer join,
  • 02:27 she's still listed because even though she doesn't exist in the orders table anymore,
  • 02:32 she does exist in the current customers table, and that's all we care about.
  • 02:37 She only has to exist in one or the other in order for full outer join to work.
  • 02:41 So for red and blue widgets, she just has NULL, which means nothing, which
  • 02:45 means there is no record of her having any red or blue, so that's kinda interesting.
  • 02:49 In fact, we can just go ahead and change this back to INNER JOIN.
  • 02:53 If we run this again, boom, she disappears because remember with INNER JOIN,
  • 02:58 she has to exist in both tables and she doesn't, she only exist in one.
  • 03:02 So if you're wanting to be very specific,
  • 03:05 use INNER JOIN, FULL OUTER JOIN, we'll return everything.
  • 03:10 Now, if you've got like millions of records in your table,
  • 03:12 you're not often gonna wanna use FULL OUTER JOIN cuz this is gonna return
  • 03:15 everything from all your tables so it could be really huge,
  • 03:18 and you're gonna have a bunch of nulls and stuff.
  • 03:20 So if you wanna really filter it down, you use INNER JOIN, otherwise,
  • 03:24 you use OUTER JOIN.
  • 03:25 So that's OUTER JOIN. In the next video,
  • 03:27 we'll look at left join.

Lesson notes are only available for subscribers.

Gift this course