SQL/R A.03.00 JOIN Syntax

SQL/R supports the standard SQL JOIN syntax as well as the LEFT OUTER JOIN syntax.

Standard SQL JOIN Syntax (left join)

An inner join combines two or more tables where a corresponding record must exist in each table.

SELECT ...
FROM Table1, Table2 [, ...]
WHERE Condition [ AND ... ]

The WHERE clause must specify the join condition(s) and may specify additional filter conditions.

For example:

SELECT Orderno,Custno,Custname FROM Orders,Customers
WHERE Customers.Custno=Orders.Custno;

With inner joins the order of tables in the FROM clause is not significant and may be rearranged by the query optimizer.

LEFT OUTER JOIN Syntax

An outer join combines two or more tables where a corresponding record may not exist in all tables. Any non-existent columns have a NULL value.
A LEFT OUTER join specifies that the table specified first ("left") is read first and corresponding recordds are matched in the subsequently specified table.

SELECT ...
FROM Table1 LEFT OUTER JOIN Table2 ON Condition
          [ LEFT OUTER JOIN Table3 ON Condition ... ]
[ WHERE ... ]

The LEFT OUTER JOIN clause specfies to use a left outer join to join the tables. The ON clause is used to specify the join conditions while the optional WHERE clause may be used to specify additional filter conditions.

For example:

SELECT Orderno,Custno,Custname
FROM Customers LEFT OUTER JOIN Orders
     ON Customers.Custno=Orders.Custno;

An outer join may be combined with additional tables (inner join).

SELECT ...
FROM Table1 LEFT OUTER JOIN Table2 ON Condition1, Table3
WHERE Condition2 [ AND ... ]

The WHERE clause must specify the join condition for Table3 and Table1 and/or Table2.

In addition, SQL/R supports ODBC specific outer join syntax variants, enclosed in {oj }.

 SELECT ...
 FROM {OJ Table LEFT OUTER JOIN Table ON Condition1
                LEFT OUTER JOIN Table ON Condition2 }
 [ WHERE ... ]

This is the same as above.

SELECT ...
FROM {OJ Table LEFT OUTER JOIN Table
	       LEFT OUTER JOIN Table
      ON Condition1 AND Condition2 }
[ WHERE ... ]

This is using a single ON clause to specify all join conditions.

Notes:

• Technically, SQL/R merges all ON conditions into the WHERE clause, the internal query optimizer then picks out the join conditions. This may change in a future implementation allowing to override the optimizer decision with the ON conditions.

• To join two tables, SQL/R requires an index to be present for efficiency reasons. If there is no index on the field chosen to join the right-hand table, SQL/R will refuse to execute the statement.

• The LEFT JOIN syntax (to indicate an inner join) is not supported. Instead, use the standard SQL JOIN syntax as explained above.

• The RIGHT (OUTER) JOIN syntax is not supported. As a solution, a "reversed left-(outer-)join" may be used.