2.17 Inner Join

00:00

I welcome back to model to databases. This is less than four sequel joints were in less than 1.3 thean er join and the previous lesson we discussed in the intro to joins Lesson. How joins our How you establish a relationship between tables and ask the sequel engine to return

00:17

a dataset based on that relationship

00:20

and an inner join is the perfect join To start with, it's probably the easier of the four or five to understand

00:29

if we look at the Venn diagram of an inner joy and this is on the Wikipedia site, Um,

00:36

if we have a table A and A table B and we enter, join them, we will get a result set that exists in both A and B. What that means is that when we make that relationship connection, we're saying this relationship has to have representation in both table, eh?

00:55

And Table B.

00:57

Otherwise, do not return it.

01:00

And where we're going to use that

01:03

is between the employees

01:06

and the department manager table. So we want to enter. Join these two tables,

01:12

and we want a data set that only includes employees that our department managers

01:19

So when we enter join these two tables.

01:23

We're going to connect them with the AMP. Underscore number key,

01:30

and this key will have to exist in both tables to bring back the data.

01:36

So let's go ahead and get started with that.

01:38

So make sure you have your employees database said as the active database again. You do that by right clicking and clicking. The set is active,

01:48

then sequel editor, new sequel editor

01:52

and let's start

01:53

writing our query So I'm gonna select I don't know what I want yet, so I'm gonna just drop down to the next from statement.

02:00

It's gonna be from employees. I'm gonna give an alias up. EMP, an alias is a short name for the table so that I don't have to type the whole employees keyword.

02:13

And when you're dealing with tables that have the same column names, you will need either to name the full table or given an alias so that the database engine knows what call him you're talking about,

02:24

and we want the department manager table, and I'm gonna give that in a lace of d man,

02:32

and we need to establish that inter join. So I'm gonna say where

02:38

IMP,

02:39

which is the alias for employees dot

02:42

employee number, is equal

02:45

to demand

02:46

dot

02:49

employee number.

02:51

Now let's go ahead and bring back the records we want. So from the EMP table e M p. I want the first name,

03:00

and that's going to bring back the number because that's common to want to bring that back

03:06

comma. Make sure you put commas between your columns

03:09

and from the ER

03:12

demand table. A department manager, we'll bring back the department number.

03:17

You also will bring back the from day and the two days so we can see when these folks were managers.

03:28

And let's go ahead and run this and see what we get back.

03:32

And sure enough, we got a list of employees that were or exist in the department manager as well. I know that we have Maur employees than this, so I know that the that a lot of employees have been excluded from

03:47

the query results and I know they were excluded because those employees do not exist

03:53

and the

03:55

in the department manager table. So

04:00

if Table A was the employees and table be was the department managers, we know that this results set in the middle that returned were employees that were also in the department manager table.

04:15

Now, if we wanted to just kind of verify that we could double click on the employees table and look at the data and we can see that we have way more employees than we got back in our own reason. Results set.

04:30

So that is the inner join. It's simply a join that establishes the relationship between two tables

04:38

where you indicate to the sequel database engine on this relationship. Return me the items or the records that exist in both tables.

04:48

That brings this lesson to a close. I hope you got your inner join working and I hope you Ah,