In addition to simple two-table outer joins, Pervasive PSQL supports n-way nested outer joins.

The Pervasive PSQL database engine has implemented LEFT OUTER JOIN using SQL92 (SQL2) as a model. The syntax is a subset of the entire SQL92 syntax which includes cross joins, right outer joins, full outer joins, and inner joins. The TableRefList below occurs after the FROM keyword in a SELECT statement and before any subsequent WHERE, HAVING, and other clauses. Note the recursive nature of TableRef and LeftOuterJoin—a TableRef can be a left outer join that can include TableRefs which, in turn, can be left outer joins and so forth.

The search condition (SearchCond) contains join conditions which in their usual form are LT.ColumnName = RT.ColumnName, where LT is left table, RT is right table, and ColumnName represents some column within a given domain. Each predicate in the search condition must contain some non-literal expression.

The syntax in the previous section includes but goes beyond the ODBC syntax in the Microsoft ODBC Programmer’s Reference. Furthermore, the vendor string escape sequence at the beginning and end of the left outer join does not change the core syntax of the outer join.

The Pervasive PSQL database engine accepts outer join syntax without the vendor strings. However, for applications that want to comply with ODBC across multiple databases, the vendor string construction should be used. Because ODBC vendor string outer joins do not support more than two tables, it may be necessary to use the syntax shown following Table 39 .

Notice the NULL entry for Franky Avalon in the table. That is because no DeptID of D103 was found in the Dept table. In a standard (INNER) join, Franky Avalon would have been dropped from the result set altogether.

The algorithm that the Pervasive PSQL Engine uses for the previous example is as follows:
taking the left table, traverse the right table, and for every case where the ON condition is TRUE for the current right table row, return a result set row composed of the appropriate right table row appended to the current left-table row.

If there is no right table row where the ON condition is TRUE, (it is FALSE for all right table rows given the current left table row), create a row instance of the right table with all column values NULL.

That result set, combined with the current left-table row for each row, is indexed in the returned result set. The algorithm is repeated for every left table row to build the complete result set. In the simple two-way left outer join shown previously, Emp is the left table and Dept is the right table.

With radiating left outer joins, all other tables are joined onto one central table. In the following example of a three-way radiating left outer join, Emp is the central table and all joins radiate from that table.

We recommend the first syntax because it lends itself to both the radiating and chaining joins. This second syntax cannot be used for radiating joins because nested left outer join ON conditions cannot reference columns in tables outside their nesting. In other words, in the following query, the reference to Emp.EmpID is illegal:

This query returns the same results as shown in Table 42, assuming there are no NULL values for EmpID in Emp and EmpID is a unique valued column. This query, however, is not optimized as well as the one show for Table 42 and can be much slower.