Joined Table

Joined tables are used in a select statement to combine data from two or more tables that share one or more columns. With Open SQL for Java you can use inner join and left outer join.

Syntax Syntax

<joined table> ::= <joined table primary> 
    ( INNER | LEFT ( OUTER )? )? JOIN
     <table reference> ON <search condition>.

<joined table primary> ::= <table reference> 
                         | <joined table> 
                         | '(' <joined table> ')'.

End of the code.

If the join is a left outer join the following additional restrictions apply to the search condition:

The search condition must contain comparison predicates only
Comparison predicates can only be combined by use of AND
Each comparison predicate can compare two columns, where the two columns must be from the respective tables that are to be joined. If the left hand side of the join is itself a join, the column reference must not be from a outer table (where outer table is the right table of a left outer join). The comparison predicate can also compare one column reference from a table on the right-hand side of the join with a constant value.

Examples

Example Example

SELECT employee_name, manager_name 
    FROM employees AS e JOIN managers AS m 
        ON e.manager_id = m.manager_id

End of the code.

The INNER JOIN. This query produces a list of all employees that have a manager along with their respective managers. Here, the FROM clause contains an inner join of two tables.

Example Example

SELECT employee_name, manager_name 
    FROM employees AS e LEFT OUTER JOIN managers AS m 
        ON e.manager_id = m.manager_id

End of the code.

The LEFT OUTER JOIN. This query produces a list of all employees along with their respective managers. If an employee does not have a manager, the manager's name is NULL. Here, the FROM clause contains a left outer join of two tables.

More Information

Search Condition

Table Reference

Identifiers