November 29, 2001
===================================================================
>From the Edge: Understanding Outer Joins (Part-I)
Chapter: Manipulating Data
Section: Running R:BASE Your Way!
Platform: R:BASE 2000 (ver 6.5++) DOS/Windows
Build: 1.839xRT03 and Higher ...
===================================================================
The R:BASE, an Industrial-Strength, Multi-User, Multi-Platform
and a True-Relational DBMS supports the power of OUTER JOINS.
Definition:
----------
An Outer Join is defined as all rows that match and all rows that
don't match based on the linking columns(s) between two tables.
By default, the Query Designer (QBE) creates an inner join between
tables, i.e, join them where linking columns are equal and the
result set includes the rows that match in both tables. Columns
containing NULL do not match any values when you use QBE and are
therefore excluded from the result set, i.e, NULL values do not
match other null values.
If you want to include data rows in the result set that do not
have a match in the joined table, you can create an OUTER JOIN.
Types of Outer Joins (LEFT, RIGHT, FULL):
----------------------------------------
When you join tables, the type of join that you create determines
the rows that appear in the result set.
When you create an outer join, the order in which tables appear in
the SQL statement is significant. The first table you add becomes
the "LEFT" table and the second table becomes the "RIGHT" table.
When you specify a LEFT or RIGHT OUTER JOIN, you are basically
referring to the order in which the tables were added to the SQL
query and to the order in which they appear in the SQL statement.
In simple terms:
The LEFT OUTER JOIN will return all the rows from the table on
left, i.e, the first table specified in the FROM clause of SQL
statement. Only the matching rows are returned from the second
table.
The RIGHT OUTER JOIN will return all the rows from the table on
right, i.e, the second table specified in the FROM clause of SQL
statement. Only the matching rows are returned from the first
table.
The FULL OUTER JOIN will return ALL the rows from both tables.
The rows that don't match, the extra columns are filled with NULL.
Simply, 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 UNION command,
but when used in a view, a permanent table and its corresponding
data is NOT created in the database.
Rules:
-----
01. Using the SELECT command, Outer Joins are created between
"two-tables" at a time.
02. To join more than two tables in an outer join, use UNION
option in SELECT command or create an intermediate VIEW.
Thus, 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 the outer join side of
the first join, i.e, it is not NULL filled.
03. When using the UNION option in SELECT command, make sure
that your database IDQUOTE (CHARacter SETtings) are SET
to ` i.e, single reversed quote, located on the same key
with tilde ~ symbol.
Download the PDF version of this article at:
http://www.razzak.com/fte
Next ...... Understanding Outer Joins (Part-II)
Sample R:BASE 6.5++ database and cool examples!
Very Best Regards,
Razzak.
