====================================================================
Easy outer joins with 4.5Plus!
====================================================================
Product: R:BASE Version: 4.5 Plus! or Higher
====================================================================
Area: Programming Catalog: Programming in R:BASE
====================================================================
A regular join includes the rows that match between two tables.
Matching rows are determined by the linking column, a column that
is common to the two tables. When the tables are joined, rows that
have matching data in the linking column are included in the
result, other rows are ignored. An outer join includes the rows
that don't have matching values.
SQL 92 defines an outer join as between two tables only. You cannot
have more than two tables in the FROM clause of your SELECT command
to use this new syntax. If you need to join more than two tables,
create your outer join using the UNION operator of SELECT as
described in the article "Working with Outer Joins" in the Jan/Feb
1993 Exchange Technical Journal (Fax #682) or by using intermediate
views as described below.
The outer join designation is an additional option to the FROM
clause of the SELECT command.
SELECT...FROM lefttblview T1 LEFT OUTER JOIN righttblview T2
RIGHT
FULL
ON T1.column1 = T2.column2
The type of outer join (LEFT, RIGHT or FULL) goes between the table
names in the FROM clause, the keyword ON precedes the linking
columns. You can add an additional WHERE clause that further
qualifies the rows to be selected, as well as using the GROUP BY,
HAVING and ORDER BY clauses of SELECT.
Left outer join
---------------
Includes the matching rows between table one (T1) and table two (T2),
and rows from table one that don't have a match in table two. The
rows from table one that don't have a match in table two are null
filled for the table two columns. Table one is the first table
referenced in the FROM clause, the table on the "left".
Right outer join
----------------
Includes the matching rows between table two (T2) and table one (T1),
and rows in table two that don't have a match in table one. The rows
in table two that don't have a match in table one are null filled for
the table one columns. Table two is the second table referenced in
the FROM clause, the table on the "right".
The use of LEFT or RIGHT depends the order you list the tables and
from which table you want the rows that don't match. For example,
... FROM table1 LEFT OUTER JOIN table2 ...
includes the rows from table1 that aren't in table2
... FROM table1 RIGHT OUTER JOIN table2 ...
includes the rows from table2 that aren't in table1.
... FROM table2 RIGHT OUTER JOIN table1 ...
is the same as
... FROM table1 LEFT OUTER JOIN table2 ...
You can change from RIGHT to LEFT by simply changing the tables
instead of changing the OUTER JOIN specification.
... FROM table2 RIGHT OUTER JOIN table1 ...
does a right outer join using table1, it returns all the rows from
table1 and matching rows from table2.
... FROM table1 RIGHT OUTER JOIN table2 ...
does a right outer join using table2, it returns all the rows from
table2 and matching rows only from table1. It is the same as
... FROM table2 LEFT OUTER JOIN table1 ...
Make sure your tables are in the right order to select the desired
rows.
Full outer join
---------------
Includes all the rows from both tables, the rows that don't match are
null filled. There will be rows with data from both tables, rows with
data from table one and null for the table two columns and rows with
data from table two and null for the columns from table one. This
gives the same result as the R:BASE UNION command, but when used in a
view, a permanent table and its corresponding data is not created in
the database.
An advantage with this new syntax over creating an outer join using
the UNION operator with SELECT is you don't get "unnamed" columns
in the result. You won't need to remember to define column names
when you create your view.
The syntax limits you to creating an outer join using only two
tables at a time. Create a three table join by creating a view with
one two-table outer join, then using that view in a second outer
join. You need to be sure that the linking column for the second
join is not on the outer join side of the first join, i.e. it is
not null filled.
For example, to link the transmaster, customer and employee tables
from the Concomp sample database, first create a view joining
transmaster and employee using a left outer join. This includes
rows in the transmaster table that are not in the employee table.
We can't include rows from employee that are not in transmaster
because that fills the linking column between transmaster and
customer, custid, with nulls.
CREATE VIEW view1 AS SELECT T1.transid, T1.transdate,
T1.invoicetotal, T1.custid, T1.empid, T2.empfname, T2.emplname FROM
transmaster T1 LEFT OUTER JOIN employee T2 ON T1.empid = T2.empid
This is what the data created by the view looks like. Notice the
one row found by the outer join part of the definition -- a row in
the transmaster table that does not have a matching row in the
employee table.
transid transdat invoicetotal custid empid empfname emplname
----- -------- --------------- ---- ---- -------- ------------
4760 01/03/89 $29,457.00 100 133 Peter Coffin
4780 01/09/89 $10,364.50 105 160 Mary Simpson
4790 01/10/89 $68,733.00 104 129 Ernest Hernandez
4795 01/12/89 $192,016.00 101 102 June Wilson
4800 02/23/89 $182,469.75 105 160 Mary Simpson
4865 02/23/89 $31,775.37 102 129 Ernest Hernandez
4970 02/24/89 $166,104.75 103 131 John Smith
4975 02/27/89 $95,462.50 101 102 June Wilson
4980 02/28/89 $24,547.50 101 102 June Wilson
5000 03/01/89 $31,639.00 101 102 June Wilson
5010 03/03/89 $118,646.25 107 131 John Smith
5015 03/06/89 $45,822.00 103 131 John Smith
5050 03/07/89 $61,368.75 104 129 Ernest Hernandez
5060 03/08/89 $32,730.00 101 102 June Wilson
5065 03/14/89 $153,067.30 106 160 Mary Simpson
5070 03/15/89 $104,190.50 104 129 Ernest Hernandez
5075 03/16/89 $169,650.50 102 129 Ernest Hernandez
5080 03/20/89 $87,280.00 100 133 Peter Coffin
5085 03/19/89 $81,006.75 107 131 John Smith
5073 03/12/89 $13,921.16 101 152 -0- -0-
This view returns all the rows that are in both transmaster and
employee, and the rows in transmaster that do not have a matching
employee id number, i.e. are not in employee. It is important to
structure the outer join this way. You do not include rows from
employee that aren't in transmaster, that would return NULLS for
the custid column in transmaster, the column that is used to link
with the customer table.
Then, create the second view. This view will join the data selected
by the first view with the data from the customer table. In our
final result, we want customer rows that don't have a matching
transaction and any of the transaction records that don't have a
matching customer -- we use a full outer join. We didn't use a full
outer join in the first view because we wanted to be sure that the
custid column, our link to the customer table, always contains a
value.
CREATE VIEW view2 AS SELECT T1.*, T2.* FROM view1 T1 FULL OUTER
JOIN customer T2 ON T1.custid = T2.custid.
This is the data that results from this second view. It combines
all the rows from the first view joining transmaster and employee
with rows from the customer table.
transid transdat empid empfnam emplname cus company
------- -------- --- ----- ------- -------- --- ---------------- ---
-0- -0- ... -0- -0- -0- 110 Southwest Comput ...
4760 01/03/89 ... 133 Peter Coffin 100 PC Distribution ...
4780 01/09/89 ... 160 Mary Simpson 105 PC Consultation ...
4790 01/10/89 ... 129 Ernest Hernandez 104 Industrial Conce ...
4795 01/12/89 ... 102 June Wilson 101 Computer Distrib ...
4800 02/23/89 ... 160 Mary Simpson 105 PC Consultation ...
4865 02/23/89 ... 129 Ernest Hernandez 102 Industrial Compu ...
4970 02/24/89 ... 131 John Smith 103 Computer Mountai ...
4975 02/27/89 ... 102 June Wilson 101 Computer Distrib ...
4980 02/28/89 ... 102 June Wilson 101 Computer Distrib ...
5000 03/01/89 ... 102 June Wilson 101 Computer Distrib ...
5010 03/03/89 ... 131 John Smith 107 Midtown Computer ...
5015 03/06/89 ... 131 John Smith 103 Computer Mountai ...
5050 03/07/89 ... 129 Ernest Hernandez 104 Industrial Conce ...
5060 03/08/89 ... 102 June Wilson 101 Computer Distrib ...
5065 03/14/89 ... 160 Mary Simpson 106 Computer Warehou ...
5070 03/15/89 ... 129 Ernest Hernandez 104 Industrial Conce ...
5075 03/16/89 ... 129 Ernest Hernandez 102 Industrial Compu ...
5080 03/20/89 ... 133 Peter Coffin 100 PC Distribution ...
5085 03/19/89 ... 131 John Smith 107 Midtown Computer ...
5073 03/12/89 ... 152 -0- -0- 101 Computer Distrib ...
This view includes all the rows from the first view, view1, the
outer join between transmaster and employee, that have matching
customer information, the rows from the customer table that have no
transactions, and the rows from view1 that have no customer
information.
This example illustrates a technique you can use to do outer joins
with more than two tables. When doing this, be sure to place the
tables in the correct order to get the correct results. Be sure you
do not structure your outer joins in such a way as to end up with
nulls in a column that must be used to link with other tables. The
two table outer join view can easily be used in other selects and
other views to join with other tables.
R:BASE Exchange Technical Journal
=================================
This technical information comes from Microrim's award winning
technical journal, the R:BASE Exchange. The R:BASE Exchange provides
R:BASE users with usage tips, programming techniques and solutions to
common problems - information that can help you get even more out of
R:BASE. Available on a yearly subscription basis, or free with
Premium Support. For more information, call 1-800-628-6990.