Joins

For information on configuring the join mode used by the OpenAccess SDK SQL engine, refer to the OpenAccess SDK Programmer’s Reference.

The following section provides the syntax for supported Join operations. OpenAccess SDK supports the SQL-92 and ODBC Outer Join syntax.

SQL syntax

<query-specification> ::= SELECT [ALL|DISTINCT] [TOP N] <SELECT-list> FROM
<table-ref-list> [<where-clause>]
[<group-by-clause>] [having-clause]
| SELECT [ALL|DISTINCT]<SELECT-list> FROM
<table-ref-list> [<where-clause>] FOR UPDATE [NOWAIT]
<SELECT-list> ::= <SELECT-item> [,<SELECT-item>]…
<SELECT-item> ::= <derived-column> [<as-clause>]
| <column-name> = <derived-column>
| *
<derived-column> ::= <value-expression>
<as-clause> ::= [AS] <column-name>
| [AS] <character-literal>
<table-ref-list> ::= <table-ref> [, <table-ref>]…
<table-ref> ::= <simple-table-ref>
| <derived-table-ref>
| <joined-table>
| <odbc-joined-table>
<simple-table-ref> ::= <table_name> [[AS] identifier]
| <table_name> [[AS] <character-literal>
<derived_table_ref> ::= <derived_table> [[AS] identifier]
| <derived_table> [[AS] <character-literal>
<derived_table> ::= <table_subquery>
<table_subquery> ::= <subquery>
<subquery> ::= LP <query_expression> RP
<joined-table> ::= <qualified-join>
| ( <joined-table> )
<qualified-join> ::= <table-ref> <outer_join_type> <table-ref>
ON <search-condition>
<outer-join-type> ::= [LEFT | RIGHT | FULL] [OUTER] JOIN| INNER JOIN
<odbc-joined-table> ::= <ODBC_std_esc_initiator> oj <joined-table>
<ODBC_std_esc_terminator>
| <ODBC_ext_esc_initiator> oj < joined-table>
<ODBC_ext_esc_terminator> ON search-condition

Examples

Example A: Implicit JOIN

SELECT * FROM dept, emp 
WHERE dept.deptno = emp.deptno;

Example B: OUTER JOIN between dept and emp tables

SELECT * FROM dept LEFT JOIN emp ON (dept.deptno = emp.deptno);

Example C: OUTER JOIN between div, dept and emp tables

SELECT div.divno, div.division, dept.divno, dept.deptno, dept.dname,
emp.deptno, emp.empno, emp.ename
FROM
div LEFT OUTER JOIN dept ON div.divno = dept.divno)
LEFT JOIN emp ON (dept.deptno = emp.deptno);

Example D: INNER JOIN between div and dept tables, OUTER JOIN with emp table

SELECT div.divno, div.division, dept.divno, dept.deptno, dept.dname,
emp.deptno, emp.empno, emp.ename
FROM
div INNER JOIN dept ON (div.divno = dept.divno)
LEFT JOIN emp ON (dept.deptno = emp.deptno);

Example E: Implicit JOIN between division and dept, OUTER JOIN between dept and emp table

SELECT div.divno, div.division, dept.divno, dept.deptno, dept.dname,
emp.deptno, emp.empno, emp.ename
FROM
div, dept LEFT JOIN emp ON (dept.deptno = emp.deptno)
WHERE (div.divno = dept.divno)

Example F: ODBC OUTER JOIN – 2 tables

SELECT * FROM {OJ dept LEFT JOIN emp ON (emp.deptno = dept.deptno)};

Example G: ODBC OUTER JOIN – 3 tables

SELECT div.divno, div.division, dept.divno, dept.deptno, dept.dname,
emp.deptno, emp.empno, emp.ename
FROM
{OJ emp LEFT JOIN
division div LEFT OUTER JOIN dept
ON (div.divno = dept.divno)
ON (emp.deptno = dept.deptno)};

Example H: ODBC OUTER JOIN – 4 tables

SELECT emp.ename, d2.dname, d3.dname, d4.dname
FROM
((emp LEFT JOIN dept d2 ON emp.deptno=d2.deptno)
LEFT JOIN dept d3 ON d2.deptno=d3.deptno)
LEFT JOIN dept d4 ON d3.deptno=d4.deptno;