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.
Four Examples of Outer Joins
Below, from slowest to fastest, are four examples of how to list all the invoice numbers in an invoice table, whether or not they have related rows in a transx table.
In each example, invoice has 1,000 rows and transx has 8,000 rows. There are 20 matches and 980 non-matches. In other words, in the first three examples the first SELECT (inner join) finds 20 rows and the second SELECT (outer join) finds 980 rows. And, in the last example, the LEFT OUTER JOIN performs the query with just one SELECT.
Uncorrelated Sub-SELECT
l
This first example shows how to list the invoice numbers with a simple sub-SELECT that doesn't have a WHERE clause correlating it to the main SELECT. It's slow, taking a long time to complete.
SELECT t2.InvId, SUM(t3.TPrice) +
FROM Invoice t2, Transx t3 +
WHERE t3.InvId = t2.InvId +
GROUP BY t2.InvId +
UNION +
SELECT Invoice.InvId, $0.00 +
FROM Invoice +
WHERE InvId NOT IN +
(SELECT InvId FROM Transx)
Correlated Sub-SELECT
Adding a correlated WHERE clause to the sub-SELECT makes it many times faster.
SELECT t2.InvId, SUM(t3.TPrice) +
FROM Invoice t2, Transx t3 +
WHERE t3.InvId = t2.InvId +
GROUP BY t2.InvId +
UNION +
SELECT t1.InvId, $0.00 +
FROM Invoice t1 +
WHERE InvId NOT IN +
(SELECT InvId FROM Transx +
WHERE Transx.InvId = t1.InvId)
Correlated and NOT EXISTS
By changing NOT IN to NOT EXISTS for use with the correlated sub-SELECT, you can add a little more speed.
SELECT t2.InvId, SUM(t3.TPrice) +
FROM Invoice t2, Transx t3 +
WHERE t3.InvId = t2.InvId +
GROUP BY t2.InvId +
UNION +
SELECT tl.InvId, $0.00 +
FROM Invoice t1 +
WHERE NOT EXISTS +
(SELECT InvId FROM Transx +
WHERE Transx.InvId = t1.InvId)
LEFT OUTER JOIN
By using a LEFT OUTER JOIN, and bypassing the second SELECT, you can add even more speed.
SELECT t2.InvId, SUM(t3.TPrice) +
FROM Invoice t2 LEFT OUTER JOIN Transx t3 ON +
t3.InvId = t2.InvId +
GROUP BY t2.InvId
