Outer Joins
Go Up to InterBase Quick Start: Part IV - Retrieving Data
Outer joins produce a results table containing columns from every row in one table and a subset of rows from another table. Outer join syntax is very similar to that of inner joins.
SELECT col [, col …] | *
FROM left_table {LEFT | RIGHT | FULL} OUTER JOIN
right_table ON joincondition
[WHERE <searchcondition>]
The joincondition is of the form left_table.col = right_table.col where the equality operator can be replaced by any of the comparison operators.
With outer joins, you must specify the type of join to perform. There are three types:
- A left outer join retrieves all rows from the left table in a join, and retrieves any rows from the right table that match the search condition specified in the ON clause.
- A right outer join retrieves all rows from the right table in a join, and retrieves any rows from the left table that match the search condition specified in the ON clause.
- A full outer join retrieves all rows from both the left and right tables in a join regardless of the search condition specified in the ON clause.
Outer joins are useful for comparing a subset of data in the context of all data from which it is retrieved. For example, when listing the employees that are assigned to projects, it might be interesting to see the employees that are not assigned to projects, too.
Practicing with Joins
The following outer join retrieves employee names from the Employee table and project IDs from the Employee_project table. It retrieves all the employee names, because this is a left outer join, and Employee is the left table.
SELECT e.full_name, p.proj_id
FROM Employee e LEFT OUTER JOIN Employee_project p
ON e.emp_no = p.emp_no
ORDER BY p.proj_id
- This should produce a list of 48 names. Notice that some employees are not assigned to a project; the proj_id column displays <null> for them.
1. Now reverse the order of the tables and execute the query again.
SELECT e.full_name, p.proj_id
FROM Employee_project p LEFT OUTER JOIN Employee e
ON e.emp_no = p.emp_no
ORDER BY p.proj_id
- This produces a list of 28 names. Notice that you get different results because this time the left table is Employee_project. The left outer join is only required to produce all the rows of the Employee_project table, not all of the Employee table.
2. As a last experiment with joins, repeat the query in Exercise 2 (the one you just did), but this time do a right outer join. Before you execute the query, think about it for a moment. What do you think this query will return?
SELECT e.full_name, p.proj_id
FROM Employee_project p RIGHT OUTER JOIN Employee e
ON e.emp_no = p.emp_no
ORDER BY p.proj_id
- You should get the same result set as in Exercise 1. Did you realize that performing a right outer join on tables B JOIN A is the same as a left outer join on tables A JOIN B?