OUTER JOIN

OUTER JOIN

This clause is used to retrieve data from two tables.

Options

.column1
Defines the column on which to link.

.column2
Defines the column on which to link.

FROM lefttblview
Specifies the left table or view.

LEFT
RIGHT
FULL
Specifies the type of outer join.

lefttblview
Explicitly defines the column on which to link-specify the left table name or view.

OUTER JOIN righttblview
Specifies the right table or view.

righttblview
Explicitly defines the column on which to link-specify the right table name or view.

WHERE clause
Limits rows of data. See "WHERE Clause" entry.

About JOIN

When you perform a SQL JOIN, you specify one column from each table to join on. These two columns contain data that is shared across both tables. You can use multiple joins in the same SQL statement to query data from as many tables as you like.

JOIN Types

Depending on your requirements, you can do an "INNER" join or an "OUTER" join. The differences are:

INNER JOIN: This will only return rows when there is at least one row in both tables that match the join condition.

LEFT OUTER JOIN: This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table.

RIGHT OUTER JOIN: This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table.

FULL OUTER JOIN: This will return all rows, as long as there's matching data in one of the tables.

About OUTER JOIN

When you use an outer join, rows are not required to have matching values. The table order in the FROM clause specifies the left and right table. You can include a WHERE clause and other SELECT clause options such as GROUP BY. The result set is built from the following criteria:

In all types of outer joins, if the same values for the linking columns are found in each table, R:BASE joins the two rows.

For a left outer join, R:BASE uses each value unique to the left (first) table and completes it with nulls for the columns of the right (second) table when the linking columns do not match.

A right outer join uses unique values found in the right (second) table and completes the rows with nulls for columns of the left (first) table when the linking columns do not match.

A full outer join first joins the linking values, followed by a left and right outer join.

Example

The following example lists all of the employees and their total sales, including those employees who have not yet completed a sale.

SELECT t1.empid, SUM(t2.netamount) FROM employee t1 +
FULL OUTER JOIN transmaster t2 ON t1.empid = t2.empid +
GROUP BY t1.empid

t1.empid   SUM(t2.netamount)
102      $387,060.00
129      $347,775.00
131      $472,000.00
133      $133,140.00
160      $344,550.00
165      $29,370.00
166      $0.00
167      $3,830.00