In this lecture, we're going to be talking about the very important join keyword, which allows us
to write SQL queries that span multiple tables.
We can pull different information from multiple tables and write more complex queries.
We have already seen this when we did our view back in our production database, and the join statements
were generated by the view user interface, and we were able to make a nice looking view that had data
from multiple tables.
So the point of this lecture is going to show you how to use the join statement and the different types
of join statements, so that you can write these in your SQL queries.
And as we stated earlier, the strength of a relational database model is in the relationships that
can be constructed between data items in the table.
So here in this diagram for our battery code database, we can see that there is a relationship between
the departments table and the employees table.
And it centers around the department ID.
So in the departments table which is a table that lists all the departments, it has an ID and a name.
And then in employees there is a field that tells what department ID they're in.
We can take advantage of this relationship when we are writing SQL queries to print out the department
name instead of the ID.
But we first have to effectively join these tables.
So let's take a look at how to do that.
So we'll start in our battery code database.
And we're going to look at our employees table here I'll need to select it here.
And I am just going to do select.
And I'm just going to drag out some columns here.
So employee ID first name last name and department ID from.
And then I will drag out the table name and we'll just execute this.
This is just a really simple query showing our employee ID first name, last name and department ID,
and we know that the employees table and the departments table are related on department ID.
So if we go look in our departments, we see that it's just a list of department IDs and names.
So we can take advantage of the relationship to print out the department name instead of just the department
ID, which is just kind of useless.
And so there's a couple of ways to do this.
I'm going to show you both.
So the first way is to use the join keyword to create the relationship.
And I am just going to copy this.
And so we will just put in join which defaults to an inner join.
And we are going to pull out the table that we want to join.
And then we're going to put on, and then we're going to put in the relationships that we need.
So I will do employees dot department ID is equal to dot department ID.
So that is going to create the relationship.
And then what we want to do is just get rid of that and grab our department name and pull it up here.
Now when we run this, we have the department names instead of the department ID.
Now there's a couple other things I want to show you about this query before we kind of move on.
So I am just going to copy this.
And this is going to be using Table alias.
And this is just a way to kind of make things easier.
And you'll see this a lot.
So we've got two tables here, but when you have a large join statement, always putting the table name
can be a little tedious.
So we could say from DBO employees E, which means E is going to be an alias we don't have to put as.
And then we could do departments.
D and then it's easier to just say E dot department id equals d dot department ID and that makes it
a little bit easier to deal with, especially when you have a bigger query.
And the other thing I want to show you is, well, let's say that I just want to also print out our
department ID, okay.
So notice that we got the red squiggly line and the error says it's an ambiguous column.
That's because the department ID exists in both tables, and that's kind of the point of this.
But SQL doesn't know which one we need.
Now we know that it doesn't matter because there are going to be the same.
So what we have to do is define which one.
And I could do e dot department ID or I could do dot department ID it wouldn't matter.
But if you have the same column names you have to define which table it's coming from and it's going
to work.
Now, there's another way to do this statement.
And you may have seen this, or you may see this in the future and it may be confusing.
So I'm just going to show you this.
I've heard this called an equi join or an equivalent join.
And so that's what I'm going to put here.
Equivalent join statement.
And so you don't use the join keyword here.
What you do is you just list your tables And I really like to use aliases.
So we've got select our column names from and then we've got select our tables.
And then in the where statement you just say where the employees department ID equals the department
ID.
And if you do it like this it gives you the exact same results.
When SQL processes this query it just chooses the most optimal path.
So does it matter whether or not you do the join statement or this?
And the answer is it depends on who you're working with or who you're working for.
Some companies want you to use the join keyword and they'll say, well, if you use join, keyword is
easier to read.
It's much clearer what's happening where here you don't.
That can be a valid statement, but it ends up being a matter of preference and a matter of what policies
your company has on programming and things like that.
So these are the two ways to do a joint statement.
Let's do one more quick example here where I'm going to look at our orders table.
So I will do select.
And then here's our orders table.
So let's say that we want the order ID the customer ID and then the order date and employee ID.
I guess we're just going to go ahead and get all this stuff we will do from orders.
Okay.
So if we print this out it's going to be pretty much what you expect.
But now we want to go join our customer ID, our employee ID and our stock ID.
And so we're going to basically do the same thing.
I'm going to use the join statement.
And I think the join statements a lot clearer and a lot cleaner because it keeps the joint statements
out of your Where statement.
So we'll do join.
And our first table is going to be our customers table.
So I'm just going to go grab customers.
And then we do our on keyword.
And then we define the relationship.
And so our relationship is going to be on customer ID.
And let's go ahead and do our aliases.
So orders oh customers see on oh dot.
And notice how the intellisense knows exactly what I'm doing here.
So oh dot customer ID is equal to c dot customer ID.
And then instead of customers ID and I like to go ahead and replace the column name.
As I'm doing this, we'll go to our customers table and I can pick any of these.
So I'll just do first name us and then I'll do a space and then we'll do last name alias here.
And then let's just kind of test this out.
So now we've got our customer name.
And then we're just going to repeat this process.
So we'll do join.
The next one is going to be our employee ID.
So it's going to be our employees table.
And then we can do an E and then we'll do our on keyword.
And then it's going to be O dot employee ID equals e dot employee ID.
Isn't that a lot easier than just pulling out the entire table name.
And then I will replace my employee ID.
They have different nomenclature for first name and last name.
So f name and l name from our orders table.
And then I'm going to do the same thing.
So I'll do first name plus space.
And so notice that I didn't put table designations for first name and last name because I don't have
to because there's no other join tables that has those.
The last thing is a stock ID that is going to be our inventory table.
So I'll just drag this out and it's called I.
And then we will define our relationship.
So it's going to be O dot stock ID is equal to.
And then we can do I dot stock ID and then we'll go to our stock ID go to our inventory table.
And we'll just use the description for that.
And let's highlight this and execute.
And that is a much nicer result set there because we've made all our relationships.
And instead of seeing customer 100 we've got al dente.
And instead of seeing employee name 27, we got Kathleen Poitras.
And suppose if we want to order this by our, say, customer name, I can just drop it in and treat
customer name just like a regular column because it's been built in the query.
And if we do that, it's totally fine and we can order the results by this alias.
So for this last example we're going to get a little more complex.
And we're going to use some concepts from the last few lectures.
Our boss has asked us to create a report.
And the report that she wants is all employees with ID first and last name, and the total number of
orders they are associated with from the orders table, even if they have no orders, in which case
should be zero.
And she wants the report ordered by the employee name sounds pretty straightforward.
We have our employees table kind of opened up, so we'll go ahead and get the initial query in select
and we will do employee ID comma.
We'll do first name.
We'll go ahead and just build the name like we have been.
So there's my first and last name as and this is going to be imp name.
And we'll go ahead and do from employees and just get some initial results here.
Okay.
So that looks good.
So we've got our employee first and last name.
So now we need the total number of orders which means two things.
Number one total number of orders is an aggregate.
So that's going to be count.
Second thing is it's going to come from the orders table.
So we're going to need to join the orders table.
So I'll just scroll up here a little bit and we're going to do join.
We'll grab orders the on keyword.
We'll put our alias here to make this easy.
And the relationship is going to be on the employee ID employee ID is equal to the orders table employee
ID.
And then of course we can't leave this this ambiguous.
So we'll just do E dot employee ID and now we can add our total number of orders.
So we'll do our aggregate function.
We know that it is going to be an aggregate on our orders table.
What we're really just looking for is the order ID.
Notice that the intellisense doesn't work because I'm in this aggregate, and it kind of doesn't know
what to do.
We're just going to put any unique field in here, which is going to be order ID and that should give
us the aggregate and we'll go ahead and alias it and we'll do as number orders.
Now since we're using an aggregate, if you may recall, we can't use just regular column names without
grouping them.
And that is basically going to make our count return a single value for each group.
Right now if we try to run this, we're going to get an error.
And so we have to do group by and we will do our employee ID.
And then can we get away with doing this like we did in our last example.
And the answer is no you can't.
So the group by function is not going to recognize an alias.
And that's okay.
We will just also group them by the last name and then finally by the first name.
And then finally we need to order by the employee name.
And that is this alias right here.
We can do order by employee name.
And if we run this we will get some results back in.
That's great.
But one of the great rules in SQL programming is just because you get results doesn't mean they're right.
We only have 11 things here.
If we go back and look at our employees table, and I'll just do select all rows.
We only have 75 rows here.
So we're missing some.
And so what the problem is this is by default an inner join.
So if I run this results are not going to change.
An inner join will return all rows where there is a matching row with the exact same value, so it is
only returning rows from the order table where it finds a valid employee ID which matches the current
row.
So that means for any employees who do not have any orders, it will not give us results.
And that is not what we want because we wanted the total number of orders, even if they're zero.
And so instead of an inner join, we're going to do a left outer join.
And an outer join basically means we are going to return the results of both tables.
And if there is a match, we'll include that information.
If there's not a match, it will either give you null or it will give you zero if it's numerical.
What the left means is the left table and your primary table.
Your first table in your query is always your left table.
And then what you're joining on is always your right table.
We want all employees, so that means we have to do a left join because we want all records from our
left table.
We'll take a look at what happens if we change this to right a little bit later on.
So now we've told it we want all records and let's run it.
And that looks great.
We are ordered by our employee name and we are including all 75 of our employees, and even the ones
who haven't sold anything have a zero here as the value.
So we have successfully done our report as it was given to us by our boss.
Now let's take a quick look at what we get if we change this to a right outer join and a right outer
join means return all records from the right table, which is orders.
Okay, so we're not going to get all employees.
We're going to get all orders that match this.
And if we run this, it is exactly the same results as if we did an inner join.
And so what's important here is knowing that we have to use left or right when we're doing an outer
join, and that outer join is going to give us zero or null results based on if we want them or not.
And before we finish this lecture, let me show you something that's really cool.
In the second section of this class, I showed you how to create a view, and that is with the views
interface.
So you can go to Create New View and you can use this graphic user interface to create a view.
And what I want to show you to kind of bring everything full circle is I'm going to grab this query
here.
I'm not going to get the order by clause.
And I'll tell you why in a moment.
And we'll just run that so we can see what it looks like.
That's great.
I'm going to right click copy.
And then I am going to go to Views New View.
And I'm not going to add any tables, I'm just going to hit close.
And then in the SQL statement, I am going to paste that in and just click off of that.
And it takes my SQL query and it builds the view.
So that view is built from the SQL query.
That's why you can go in and modify it and you'll see the changes.
And so let's say that this is a view that we really want to use a lot.
I can execute it, make sure I'm getting my results, and then I can just go save this and we'll save
it as V for view employee orders I can hit okay.
And then if I go under my views and right click refresh, I've got this new view and I can right click
select top 1000 rows.
And then the reason why we don't do the order by is because you always do the order by when you're querying
it.
So I could do order by order by.
And then we can do orders okay.
So now it's doing my orders.
We could even do a reverse order here using our descending keyword.
And so that's actually a really nice thing.
So if you pull out some queries that you want to keep reusing, you don't have to keep rewriting that.
You can just save it as a view.
So that's the lecture on inner and outer joins coming up.
We'll have an assignment.
We will test your knowledge on writing some join statements and answering some questions.
So I'll see you in the next assignment.