Online College Courses for Credit

+
JOIN ON to Link Tables

JOIN ON to Link Tables

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a SELECT statement that shows data from two different tables using an ON clause.

(more)
See More
Tutorial

what's covered
This tutorial explores using the JOIN ON to link table data across two tables in two parts:
  1. JOIN ON
  2. USING vs ON

1. JOIN ON

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:

Customer and Employee Table Example

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;

Query Result Example


2. USING vs ON

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:


Query Result Example

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);

Query Result Example



SELECT * 
FROM album 
INNER JOIN artist ON album.artist_id = artist.artist_id;


Query Result Example

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.


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The JOIN ON clause allows the joining of tables together on columns that do not have the same names.