Applying Additional Conditions to a Join

Additional Conditions

You can apply additional conditions to the join. For example, you may want to join the EMPLOYEES and DEPARTMENTS tables and, in addition, display only employees who have a manager ID of 149. To add additional conditions to the ON clause, you can add AND clauses. Alternatively, you can use a WHERE clause to apply additional conditions.

Using the AND Clause

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149


Using the WHERE clause

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149

Aliases

In the examples shown, aliases are used to identify the table names. In the FROM clause, an abbreviation is provided after the table name. This is called an alias. After the alias is set up in the FROM clause, you can refer to it throughout the statement.