Objectives This lesson 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.
USING Clause • 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); FIRST_NAME Jennifer Michael Pat …
USING Clause • 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';
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 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 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
DPS6L2 Join Clauses
job_grades table GRADE_LEVEL LOWEST_SAL HIGHEST_SAL A
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.
Summary In this lesson, 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