In the prior tutoring, we joined table data together with the USING clause. This works great for many 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 as a support_rep_id which references the employee_id of the employee table:
This makes sense as 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 but rather we must use the ON clause.
The structure of the statement will look like the following:
SELECT <columnlist>
FROM <table1>
INNER JOIN <table2> ON <table1column> = <table2column>;
Between the two tables customers and employee, 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 in both tables, the email addresses are in an email column, we need to prefix the column with the table name like:
SELECT customer.email, employee.email
FROM customer
INNER JOIN employee ON support_rep_id = employee_id;
Although we would want to use this method when the columns do not match between the tables, it also does work if the columns match similar to the USING clause. Let us 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 the column 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 as the other.
