Show TOC Start of Content Area

Syntax documentation Joined Table  Locate the document in its SAP Library structure

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> ')'.

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

This graphic is explained in the accompanying text

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

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.

This graphic is explained in the accompanying text

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

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

End of Content Area