Hello everybody and welcome back to another interesting and exciting lecture on the topic of the Structured Query Language. In this video, we're going to be talking about SQL Joins. This is the first lesson within Module 4 of our course. We're going to have four video lessons on SQL Joins. It's a little bit more complicated than the stuff we've covered so far. So let's talk about Joins. If you want to run a query that's going to get data from multiple tables, typically the way you'll do it is to join those two tables together. Now, we did see an example of a sub-query where my sub-query can pull data from one table while the outer query pulls data from a different table. But this is different. It's a lot more capable than a sub-query. If you're going to retrieve data from multiple tables, you want to do a join. If you're going to join two tables, you've got to make sure that those two tables have a common column referred to as a key, a common key. Typically that's because there's a foreign key relationship between these two tables. What's a primary key in one table is a non-key attribute in another table, which is a foreign key. If I've got two tables with that foreign key relationship, I can join them. Now, the common columns that I'm joining on don't have to have the same name, but they do have to have the same length and datatype. It's okay if in one table that column has one name in the joining table, it's got a different name. That's okay, it'll still work. But they got to have the same datatype and link. Now, as we're learning about joins, I want to remind you that joins are one of the more resource intensive operations that you can do in a relational database. You got to be careful when you're coding joins. In the purposes of this course, with these tiny databases that we're playing with, it doesn't really matter. But imagine if you're in an organization with huge databases with tables containing tens or hundreds of millions of rows. That's a very realistic thing that really does happen. These joins can be very expensive. You have to learn to be careful how you code your joins so that they perform well. Let's take a look at an example of joining two tables using our Northwinds database. Suppose I want to join the orders table to the employees table. Can I do it? Well, let's take a look here. I've got an employee ID in my orders table and I've got an employee ID in my employees table. It looks like I can join these two tables. I want to join them on the employee ID as my common key. In order to do that, here's what's going to happen when I run this join. The orders table has 830 rows and each of those rows has an employee ID. The employees table has nine rows. Each of those rows has an employee ID. They do have a common key, which is the primary key in employees and a foreign key in orders. I'm going to tell SQL to join the rows in the employees table, in the orders table where the employee ID matches. Where this employee ID equals that employee ID, I want to grab that row as a single row in my join. This is called an inner join or sometimes called an equi-join, but I'm going to use the term inner join to describe this. Let's take a look at this query. Suppose that you've been asked to provide a listing that shows Northwinds employees. For each employee, you want to see a count of how many orders they have in the orders table because employees in Northwinds are salespeople and they get credit for the sales that they make. The orders are credited to employees. I can join those two tables and get a count of how many orders each employee gets credit for. Take a look at the query. I'm going to select the employee's last name, first name, and then do a group function count and count all their order IDs. I'm going to call this count order total. I'm going to get my data from the employees table and from the orders table. Both tables appear in the from clause. Note they're separated by a comma, that's important. Now in order for the join to work, I have to put in that equi-join or inner join where clause condition that says I want to get the rows where this employee ID from employees equals this employee ID from orders. When I get that data, I want to group it by last name and first name in order to get a count for each employee. Then I'm going to sort it by the third column in the answer set, which is this count and I want it to be descending. Let's run this query and take a look at our first example of running a SQL join. Then here we go. I'm seeing each employee and their order total and it's in descending order just as expected. Suppose I want to add the employee ID to that same query. It's really exactly the same query except I've added this column to the select statement. Let's run this thing and watch what happens. Because I am joining on employee ID, I thought it would be interesting to add that to my select statement. As I run this query, boom, I get an error, it blew up and it says bad query. Notice the error message says, the column reference "employee ID" is ambiguous. What does that mean? Well, let's take a look. Here's my error message with that term ambiguous. Why do I get that? It's because that employee ID column exists in both tables in this query and I didn't tell SQL which table to get it from. If you look at the code here, I just said get employee ID. SQL looks at this and says, well, I've got employee ID in the employee's table and I've got employee ID in the orders table. You're telling me to select it, but which one do you want. SQL stops and says, this is ambiguous. I don't know what you want. I've got to prefix the table name in front of the column name in order to disambiguate, the select statement. Whenever you've got a column that appears in multiple tables in your query, you've got to qualify that column name by putting the table name in front of it. As you can see in the next example, you've also got to add it to the group by, why? Because it's a non-group function that's in the select statement. Therefore, I have to add it to the group by. Let's take a look at this and run this query with that table name, qualifying the column name and we can make this ambiguous error go away. Here's the result of our query now, working as hoped. When I am qualifying columns, in any query, a join query, or any other query when I'm qualifying the column with the table name, it's often helpful if I define an alias for the table. I think we covered this before when we were looking at the sub-queries. I can set up an alias of just one letter and it exists only for the duration of this query. Once this query over that alias table name goes away. I can call the employees table E, and I can call the orders table O, and then when I am qualifying these column names with the table name, I can just type the alias. It looks cleaner and it saves me a lot of typing. We don't need to run that query. Now we're going to take a look at one other kind of join here. The join that I've showed you, joins these two tables with a condition in a where clause that says, all right, I want to see the rows where this employee ID is equal to that employee ID joining these two tables on this condition. This is called an implicit inner join. It's possible using a different syntax and actually using the join-on verb I can do an explicit inner join. Take a look at this syntax. I select the same stuff from this table. Join this table on this key equals to that key. You see how this syntax is a little bit different. Here I used the joint verb with the on clause stating that condition and it's just a little bit different than this one where I put the where clause. Now as a SQL coder, you're going to have to decide, do you like the implicit join or the explicit join syntax? It is completely up to you until we start doing outer joins, which is another topic for another lecture coming up soon. That's it for our first look at SQL joins. Thanks, and we'll see you next time.
