In the prior tutorial, we joined table data together with the USING clause. This works great for situations where the column names are the same between two tables. However, this is not always the case. For example, if we look at the customer and employee tables, the customer table has a support_rep_id which references the employee_id of the employee table:
This makes sense, because a support representative would be an employee, but it wouldn’t make contextual sense to have employee_id as the name of the column in the customer table. Since the column names are different, we could not use the USING clause. Rather, we must use the ON clause.
The structure of the statement looks like the following:
SELECT <columnlist>
FROM <table1>
INNER JOIN <table2> ON <table1column> = <table2column>;
We can start the query by filling in the table and column names that we’re joining:
SELECT <columnlist>
FROM customer
INNER JOIN employee ON support_rep_id = employee_id;
We then need to determine what columns we want to return as part of the query. We don’t want to return all of the columns. For example, we may want to have all of the customer emails and the respective employee emails so that the employees can send out an email to their customers. Since the email addresses are in an email column in both tables, we need to prefix the column with the table name, like this:
SELECT customer.email, employee.email
FROM customer
INNER JOIN employee ON support_rep_id = employee_id;
Although we would use this method when the column names between the tables do not match, it does also work if the columns match (and therefore we could use the USING clause). Let's take a look at an example of the artist and album table with an inner join with the USING clause:
SELECT title, name
FROM album
INNER JOIN artist USING (artist_id);
We could convert this by removing the USING clause and adding the artist_id prefixed with the table name:
SELECT title, name
FROM album
INNER JOIN artist ON album.artist_id = artist.artist_id;
In both cases, the result set is the same:
The only case where the result set will be different is if we use the * in the SELECT clause:
SELECT *
FROM album
INNER JOIN artist USING (artist_id);
SELECT *
FROM album
INNER JOIN artist ON album.artist_id = artist.artist_id;
Notice that in the ON clause join, the artist_id appears twice (one for each table) whereas artist_id only appears once in the USING clause. This is because the USING performs an equality join and can only be used when the column names are identical. It is not necessary to include the column twice. Other than that, they function the same.
[MUSIC PLAYING] Here we're going to explore the use of the inner join utilizing the on clause instead. So in this case here, we have two tables-- the representative table as well as department table. However, there's no consistent name and that's there between the two tables that we can utilize using command in this case here, being that here we have the representative ID. But in the current table, it's not called the representative ID. It's called the manager ID.
So when we have that kind of situation, we have to utilize the inner join utilizing on clause instead. So we can start building this out very similar to the other type of join that we have. We'll do a select star from the first table representative, join department, and then we're going to identify on instead of using. And then we're going to identify the representative table with the representative ID, which should be equal to department dot manager ID.
That will allow us to have the results based on that joint between the two tables when there's a differentiator in this case here, between the two different tables. Here we could also remove the table names being that this unique per table.
[MUSIC PLAYING]
