Skip to content Skip to main navigation Skip to footer

Join Clauses in SQL

Objectives

This tutorial covers the following objectives:

  • Construct and execute a join with the ANSI-99 USING Clause
  • Construct and execute a join with the ANSI-99 ON Clause
  • Construct and execute an ANSI-99 query that joins three tables

Purpose

  • As you add more commands to your database vocabulary, you will be better able to design queries that return the desired result.
  • The purpose of a join is to bind data together, across tables, without repeating all of the data in every table.
  • Why ask for more data than you really need?

USING Clause

  • In a natural join, if the tables have columns with the same names but different data types, the join causes an
  • error.
  • To avoid this situation, the join clause can be modified with a USING clause.
  • The USING clause specifies the columns that should be used for the join.

A USING clause is often preferred to a natural join even when the columns have the same data type as well as the same name, because it clearly states exactly which join column is being used.

  • The query shown is an example of the USING clause.
  • The columns referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.
SELECT first_name, last_name, department_id, department_name
FROM employees JOIN departments USING (department_id);
USING Clause

If the column in the USING clause has a qualifier, the following error is returned: ORA-25154: column part of USING clause cannot have qualifier.

Note: the last line in the sample output uses … … … … to signify that more data is returned than is displayed on the slide.

  • The USING clause allows us to use WHERE to restrict rows from one or both tables:
SELECT first_name, last_name, department_id, department_name
FROM employees JOIN departments USING (department_id)
WHERE last_name = 'Higgins';
USING Clause

Aliases

  • Working with lengthy column and table names can be cumbersome.
  • Fortunately, there is a way to shorten the syntax using aliases.
  • To distinguish columns that have identical names but reside in different tables, use table aliases.
  • A table alias is similar to a column alias; it renames an object within a statement.
  • It is created by entering the new name for the table just after the table name in the from-clause.

Table Aliases

  • Table aliases are used in the query below.
SELECT last_name, e.job_id, job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id
AND department_id = 80;
Table Aliases
  • When column names are not duplicated between two tables, you do not need to add the table name or alias to the column name.
  • If a table alias is used in the FROM clause, then that table alias must be substituted for the table name throughout the SELECT statement.
  • Using the name of a table in the SELECT clause that has been given an alias in the FROM clause will result in an error.
SELECT last_name, employees.job_id, job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id
AND department_id = 80;
ORA-00904 Invalid identifier

ON Clause

  • What if the columns to be joined have different names, or if the join uses non-equality comparison operators such as <, >, or BETWEEN ?
  • We can’t use USING, so instead we use an ON clause.
  • This allows a greater variety of join conditions to be specified.
  • The ON clause also allows us to use WHERE to restrict rows from one or both tables.

ON Clause Example

  • In this example, the ON clause is used to join the employees table with the jobs table.
SELECT last_name, job_title
FROM employees e JOIN jobs j
ON (e.job_id = j.job_id);
ON Clause Example
  • A join ON clause is required when the common columns have different names in the two tables.
  • When using an ON clause on columns with the same name in both tables, you need to add a qualifier (either the table name or alias) otherwise an error will be returned. The example above uses table aliases as a qualifier e.job_id = j.job_id, but could also have been written using the table names (employees.job_id = jobs.job_id.
SELECT last_name, job_title
FROM employees e JOIN jobs j
ON (e.job_id = j.job_id);

ON Clause with WHERE Clause

  • Here is the same query with a WHERE clause to restrict the rows selected.
SELECT last_name, job_title
FROM employees e JOIN jobs j
ON (e.job_id = j.job_id)
WHERE last_name LIKE 'H%';
ON Clause with WHERE Clause

Note: As the job_id column in the employees and jobs tables have the same name and data type, the above query could also be written with a NATURAL JOIN, or a JOIN USING.

ON Clause with non-equality operator

  • Sometimes you may need to retrieve data from a table that has no corresponding column in another table.
  • Suppose we want to know the grade_level for each employees salary.
  • The job_grades table does not have a common column with the employees table.
  • Using an ON clause allows us to join the two tables
ON Clause with non-equality operator
SELECT last_name, salary, grade_level, lowest_sal, highest_sal
FROM employees JOIN job_grades
ON(salary BETWEEN lowest_sal AND highest_sal); 
ON Clause with non-equality operator

With a JOIN ON clause, you can use operators other than “=”. For example >=, <=, BETWEEN.

Joining Three Tables

  • Both USING and ON can be used to join three or more tables.
  • Suppose we need a report of our employees, their department, and the city where the department is located?
  • We need to join three tables: employees, departments and locations.
Joining Three Tables

Joining Three Tables Example

SELECT last_name, department_name AS "Department", city
FROM employees JOIN departments USING (department_id)
JOIN locations USING (location_id);
Both USING and ON can be used to join three or more tables.

Terminology

Key terms used in this tutorial included:

  • ON clause
  • USING clause

Summary

In this tutorial, you should have learned how to:

  • Construct and execute a join with the ANSI-99 USING Clause
  • Construct and execute a join with the ANSI-99 ON Clause
  • Construct and execute an ANSI-99 query that joins three tables

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *