This clause is used to retrieve data from two or more tables.
Options
.column1
Defines the column on which to link.
.column2
Defines the column on which to link.
corr_name
A correlation name is an alias or nickname for a table. It lets you refer to the same table twice in one command, use a shorter name, and explicitly refer to a column when referring to the same column if that column appears in more than one table.
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 the left table name or view.
OUTER JOIN righttblview
Specifies the right table or view.
righttblview
Explicitly defines the column on which to link the right table name or view.
WHERE clause
Limits rows of data. See WHERE.
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.
Nested JOINs
Any of the JOIN types can be mixed in any sequence to create a nested join. The nested joins still require that you specify one column from each table to join on. When nesting joins, it is important to use the correct sequence of parenthesis, along with a correlation for each join.
In the example below, notice the two sets of parenthesis, which all begin after the FROM keyword and end after the linking columns. Also note the "J1" and "J2" correlations specified for each join.
SELECT ALL FROM (( TABLE1 t1 +
LEFT OUTER JOIN TABLE2 t2 ON t1.FieldT2=t2.FieldT2) J1 +
RIGHT OUTER JOIN TABLE3 t3 ON t3.FieldT3=j1.FieldT3) J2
Examples
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
Nested INNER JOIN Example:
The following example lists a specific product, all of the locations where it resides, and the components used within the product.
SELECT ProdName, Location, CompID FROM +
((Product t1 RIGHT OUTER JOIN ProdLocation t2 ON t1.Model=t2.Model) J1 +
RIGHT OUTER JOIN CompUsed t3 ON t3.Model=j1.Model) J2 +
WHERE Model = 'CX3000'
ProdName Location CompID
----------------------------------- -------- --------
Standard SVGA Color PC A-1 X1010
Standard SVGA Color PC B-1 X1010
Standard SVGA Color PC C-10 X1010
Standard SVGA Color PC A-1 X2000
Standard SVGA Color PC B-1 X2000
Standard SVGA Color PC C-10 X2000
Standard SVGA Color PC A-1 X3000
Standard SVGA Color PC B-1 X3000
Standard SVGA Color PC C-10 X3000
