7 hours 36 minutes
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
a dataset based on that relationship
and an inner join is the perfect join To start with, it's probably the easier of the four or five to understand
if we look at the Venn diagram of an inner joy and this is on the Wikipedia site, Um,
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?
And Table B.
Otherwise, do not return it.
And where we're going to use that
is between the employees
and the department manager table. So we want to enter. Join these two tables,
and we want a data set that only includes employees that our department managers
So when we enter join these two tables.
We're going to connect them with the AMP. Underscore number key,
and this key will have to exist in both tables to bring back the data.
So let's go ahead and get started with that.
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,
then sequel editor, new sequel editor
and let's start
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.
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.
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,
and we want the department manager table, and I'm gonna give that in a lace of d man,
and we need to establish that inter join. So I'm gonna say where
which is the alias for employees dot
employee number, is equal
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,
and that's going to bring back the number because that's common to want to bring that back
comma. Make sure you put commas between your columns
and from the ER
demand table. A department manager, we'll bring back the department number.
You also will bring back the from day and the two days so we can see when these folks were managers.
And let's go ahead and run this and see what we get back.
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
the query results and I know they were excluded because those employees do not exist
in the department manager table. So
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.
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.
So that is the inner join. It's simply a join that establishes the relationship between two tables
where you indicate to the sequel database engine on this relationship. Return me the items or the records that exist in both tables.
That brings this lesson to a close. I hope you got your inner join working and I hope you Ah,
I didn't run into any issues with it. And I will see you in the next lesson. Thank you.