JOIN

---

You can specify a single table or view, multiple tables, or a single view and multiple tables. When you specify more than one table, the tables are said to be joined.

Syntax

join-definition ::= table-reference INNER JOIN table-reference ON search-condition 
| table-reference CROSS JOIN table-reference 
| outer-join-definition 

 
outer-join-definition ::= table-reference outer-join-type JOIN table-reference 
ON search-condition 
 
outer-join-type ::= LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ OUTER ] 
 

The following example illustrates a two-table outer join:

SELECT * FROM Person LEFT OUTER JOIN Faculty ON Person.ID = Faculty.ID 

The following example shows an outer join embedded in a vendor string. The "OJ" can be either upper or lower case.

SELECT t1.deptno, ename FROM {OJ emp t2 LEFT OUTER JOIN dept t1 ON t2.deptno=t1.deptno} 

The SRDE supports two-table outer joins as specified in the Microsoft ODBC Programmer's Reference.

In addition to simple two-table outer joins, the SRDE supports n-way nested outer joins.

The outer join may or may not be embedded in a vendor string. If a vendor string is used, SRDE strips it off and parses the actual outer join text.

LEFT OUTER

The Pervasive.SQL 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.

TableRefList : 
TableRef [, TableRefList] 
| TableRef 
| OuterJoinVendorString [, TableRefList] 

TableRef : 
TableName [CorrelationName] 
| LeftOuterJoin 
| ( LeftOuterJoin ) 

LeftOuterJoin : 
TableRef LEFT OUTER JOIN TableRef ON SearchCond 

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 implementation of left outer join goes beyond the syntax in the Microsoft ODBC Programmer's Reference.

Vendor Strings

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.SQL 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 3-7 .

Examples

The following four tables are used in the examples in this section.

 
Table 3-4 Emp Table

FirstName	LastName	DeptID	EmpID
Franky	Avalon	D103	E1
Gordon	Lightfoot	D102	E2
Lawrence	Welk	D101	E3
Bruce	Cockburn	D102	E4

 
Table 3-5 Dept Table

DeptID	LocID	Name
D101	L1	TV
D102	L2	Folk

 
Table 3-6 Addr Table

EmpID	Street
E1	101 Mem Lane
E2	14 Young St.

 
Table 3-7 Loc Table

LocID	Name
L1	PlanetX
L2	PlanetY

The following example shows a simple two-way Left Outer Join:

SELECT * FROM Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID 

This two-way outer join produces the following result set:

 
Table 3-8 Two-way Left Outer Join

Emp				Dept
FirstName	LastName	DeptID	EmpID	DeptID	LocID	Name
Franky	Avalon	D103	E1	NULL	NULL	NULL
Gordon	Lightfoot	D102	E2	D102	L2	Folk
Lawrence	Welk	D101	E3	D101	L1	TV
Bruce	Cockburn	D102	E4	D102	L2	Folk

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.

Algorithm

The algorithm that the Pervasive.SQL 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.

---

Note
Although irrelevant to the algorithm, the appending of the left table to the right table assumes proper projection as specified in the select list of the query. This projection ranges from all columns (for example, SELECT * FROM . . .) to only one column in the result set (for example, SELECT FirstName FROM . . .).

---

---

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.

SELECT * FROM (Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID) LEFT OUTER JOIN Addr ON Emp.EmpID = Addr.EmpID 

 
Table 3-9 Three-way Radiating Left Outer Join

Emp				Dept			Addr
First Name	Last Name	Dept ID	Emp ID	Dept ID	Loc ID	Name	Emp ID	Street
Franky	Avalon	D103	E1	NULL	NULL	NULL	E1	101 Mem Lane
Gordon	Lightfoot	D102	E2	D102	L2	Folk	E2	14 Young St
Lawrence	Welk	D101	E3	D101	L1	TV	NULL	NULL
Bruce	Cockburn	D102	E4	D101	L1	TV	NULL	NULL

---

In a chaining left outer join, one table is joined to another, and that table, in turn, is joined to another. The following example illustrates a three-way chaining left outer join:

SELECT * FROM (Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID) LEFT OUTER JOIN Loc ON Dept.LocID = Loc.LocID


  
 

 

Table 3-10	 Three-way Chaining Left Outer Join


  

    
 
Emp
    
 


    
 


    
 


    
 
Dept
    
 


    
 


    
 
Loc
    
 


  
  

    
 
First Name
    
 
Last Name
    
 
Dept ID
    
 
Emp ID
    
 
Dept ID
    
 
Loc ID
    
 
Name
    
 
Loc ID
    
 
Name
  
  

    
 
Franky
    
 
Avalon
    
 
D103
    
 
E1
    
 
NULL
    
 
NULL
    
 
NULL
    
 
NULL
    
 
NULL
  
  

    
 
Gordon
    
 
Lightfoot
    
 
D102
    
 
E2
    
 
D102
    
 
L2
    
 
Folk
    
 
L2
    
 
PlanetY
  
  

    
 
Lawrence
    
 
Welk
    
 
D101
    
 
E3
    
 
D101
    
 
L1
    
 
TV 
    
 
L1
    
 
PlanetX
  
  

    
 
Bruce
    
 
Cockburn
    
 
D102
    
 
E4
    
 
D101
    
 
L1
    
 
TV 
    
 
L1
    
 
PlanetX
  




 

This join could also be expressed as:

SELECT * FROM Emp LEFT OUTER JOIN (Dept LEFT OUTER JOIN Loc ON Dept.LocID = Loc.LocID) ON Emp.DeptID = Dept.DeptID 

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:

SELECT * FROM Emp LEFT OUTER JOIN (Dept LEFT OUTER JOIN Addr ON Emp.EmpID = Addr.EmpID) ON Emp.DeptID = Dept.DeptID 

---

The following example shows a three-way radiating left outer join, less optimized:

SELECT * FROM Emp E1 LEFT OUTER JOIN Dept ON E1.DeptID = Dept.DeptID, Emp E2 LEFT OUTER JOIN Addr ON E2.EmpID = Addr.EmpID WHERE E1.EmpID = E2.EmpID


  
 

 

Table 3-11	 Three-way Radiating Left Outer Join, Less Optimized


  

    
 
Emp
    
 


    
 


    
 


    
 
Dept
    
 


    
 


    
 
Addr
    
 


  
  

    
 
First Name
    
 
Last Name
    
 
Dept ID
    
 
Emp ID
    
 
Dept ID
    
 
Loc ID
    
 
Name
    
 
Emp ID
    
 
Street
  
  

    
 
Franky
    
 
Avalon
    
 
D103
    
 
E1
    
 
NULL
    
 
NULL
    
 
NULL
    
 
E1
    
 
101 Mem Lane
  
  

    
 
Gordon
    
 
Lightfoot
    
 
D102
    
 
E2
    
 
D102
    
 
L2
    
 
Folk
    
 
E2
    
 
14 Young St
  
  

    
 
Lawrence
    
 
Welk
    
 
D101
    
 
E3
    
 
D101
    
 
L1
    
 
TV 
    
 
NULL
    
 
NULL
  
  

    
 
Bruce
    
 
Cockburn
    
 
D102
    
 
E4
    
 
D101
    
 
L1
    
 
TV 
    
 
NULL
    
 
NULL
  




 

This query returns the same results as shown in Table 3-10, 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 3-10 and can be much slower.

See Also

SELECT