Focus video player for keyboard shortcuts
Auto
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
We will discuss and write inner joins to combine more than one table in a specific way
Exercise files
Download this lesson’s related exercise files.
INNER JOIN59.2 KB INNER JOIN - Solution
59.5 KB
Quick reference
INNER JOIN
Inner Join allows us to pull data from another table.
When to use
Use it when you want to pull data from a table that has something in common with your current table.
Instructions
SELECT [Last Name], [Blue Widget]
FROM [dbo].[Current_Customers]
INNER 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. The Customer ID is what each table you are using INNER JOIN on have in common.
If both tables did not have a Customer ID column, this INNER JOIN would not work. In the next 3 modules we cover other types of Joins (Outer, Left, Right).
Hints & tips
- Inner Join lets you pull data from a table that has something in common with your current table.
- 00:05 In the next few videos, we're gonna be talking about dealing with more than one
- 00:09 table, and pulling data from multiple tables.
- 00:12 And specifically, we're gonna talk about joins.
- 00:15 And join is just what it sounds like, you're joining data from two or
- 00:19 more different tables.
- 00:21 So in this video, I wanna look at inner join.
- 00:23 And inner join is what you use when you want to pull something from two different
- 00:27 tables that have some matching values in both tables.
- 00:31 So, something from each table has to match in order to use an inner join.
- 00:36 So, I'll explain this in real terms.
- 00:38 First off, before we start, you'll notice I made a change to our current table,
- 00:42 our current customers table.
- 00:43 I added this customer ID and
- 00:45 that's a customary thing whenever you create any type of database.
- 00:48 You'll usually give each record its own unique ID so
- 00:51 you can compare that ID across different tables.
- 00:54 So in this case, every customer has an ID.
- 00:58 So John Elder's ID is number 1, Julia Styles ID is number 6, right?
- 01:02 The next thing you'll notice I did is I added a new table, and
- 01:05 we'll look at this real quick.
- 01:06 And it just has three columns.
- 01:08 Actually, let's go ahead and just look at these.
- 01:12 So, it has a Customer ID, and
- 01:15 this Customer ID corresponds with our current Customer ID.
- 01:18 So remember, John Elder's Customer ID was number 1.
- 01:21 So, I've named this table Orders.
- 01:24 So these are the orders of John Elder, customer number 1.
- 01:27 John Elder's ordered 12 red widgets and 3 blue widgets.
- 01:31 Julia Stiles was number 6, she's ordered 14 red widgets and 27 blue widgets.
- 01:36 So this is sort of a common thing.
- 01:37 You have customers, right?
- 01:39 So you have a table with their customer data, their names and their addresses and
- 01:43 all that stuff.
- 01:43 And you might have another table with their orders.
- 01:46 It's a current customers, current customers order things.
- 01:49 So you would create a separate table just to have their orders.
- 01:52 And we can connect them by this Customer ID column because these numbers are gonna
- 01:57 correspond To these numbers.
- 02:04 So we already know how to make tables, I don't have to go through that again and
- 02:07 you can refer back to the prior videos.
- 02:09 Right now, we just wanna do a quick inner join command.
- 02:12 So first off, pick the columns you want to display.
- 02:16 So we want, let's say, three columns.
- 02:19 And let's go, first name, Last name, and let's say, red widgets.
- 02:28 We sell widgets, red widgets and blue widgets, right?
- 02:31 So, the second line here is the first table you want to access stuff from.
- 02:36 And this is gonna be our Current Customers table.
- 02:38 And then now we wanna tell it, let's inner join to our
- 02:43 other table which is called dbo Orders, see right here.
- 02:48 Orders.
- 02:49 And now we need to stay on cuz we're joining it on what?
- 02:53 What two things like I said earlier,
- 02:55 each of these tables have to have something in common.
- 02:57 They have to have the same thing in both tables.
- 03:00 In our case, it's this Customer ID column.
- 03:03 Each table has that Customer ID, so we need to specify that.
- 03:07 So we just go [Current_Customers].[Customer.ID].
- 03:16 And we set that equal to [Orders].[Customer.ID].
- 03:25 So if you look at this, we're just saying combine our second table
- 03:29 to our first table and those two tables have this Customer ID thing in common.
- 03:34 That's all we're saying here.
- 03:34 So, if we execute that we see first name, last name and red widget.
- 03:38 So, John Elder has ordered 12 red widgets.
- 03:40 John Smith has ordered 6 red widgets.
- 03:43 And we could add our blue widget column just as easily, if we wanted to.
- 03:49 So John Elder has 12 and 3 and we can confirm that by going to our orders.
- 03:53 Remember John Elder is number 1, so if we just Edit Top 100 Rows,
- 03:59 we see number 1 has 12 and 3, which is 12 and 3, so that's correct.
- 04:05 So that's inner join.
- 04:07 It seems a little complicated.
- 04:08 This is a whole lot of stuff we've put on here, but it's really pretty simple.
- 04:12 Designate this line is just like the from line we've always done.
- 04:16 This line is like the select line we've always done.
- 04:18 We just add the columns we wanna list.
- 04:20 And the only thing new is this INNER JOIN.
- 04:22 Second table on and then list a thing that each of those two tables have in common.
- 04:28 And that's what INNER JOIN means.
- 04:30 It means the two tables have something in common.
- 04:33 Something that's the same in each table.
- 04:35 So that's INNER JOIN.
- 04:36 In the next video, we'll look at outer join.
Lesson notes are only available for subscribers.