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;
