NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
J-1
J
Joins
A join is an operation that combines two tables or views to form a new table. A join
query is a query that requests columns from more than one table or view.
A join query should contain predicates that compare a column from one table with a
column from another table. The join concatenates (joins together) rows (from each of
the joined tables) that satisfy the predicates. Without predicates, SQL creates a Cartesian
product with all rows of each table combined with each other.
NonStop SQL/MP supports two types of joins: inner joins and left outer joins.
An inner join discards rows that do not satisfy the predicates specified in a WHERE
clause or an ON clause. Inner joins are useful for reporting information that satisfies a
given set of requirements.
An outer join returns all rows from one or more of the tables being joined; rows that do
not satisfy the search condition have missing information in columns that correspond to
the other tables being joined. An outer join is useful for generating exception reports
(retrieving information that does NOT satisfy a stated set of requirements).
A left outer join returns all rows from the left table or view - the table or view left of the
keywords LEFT JOIN in the SELECT statement - and rows from the other table that
satisfy the search condition. A left outer join is not necessarily symmetric; A LEFT
JOIN B is not necessarily the same as B LEFT JOIN A.
NonStop SQL/MP allows you to specify the type of algorithm used for a join operation
and to specify the sequence of joins within a SELECT. See CONTROL QUERY
Directive on page C-70 (the HASH JOIN option) and CONTROL TABLE Directive on
page C-72 (the JOIN METHOD and JOIN SEQUENCE options) for information about
how to do so.
See the NonStop SQL/MP Query Guide for a more detailed discussion of joins.
Examples - Joins
The following examples refer to a database consisting of the following tables:
EMPLOYEE TABLE
EMP_ID LAST_NAME FIRST_NAME DEPT_NUM MGR_ID SALARY
2703 Smith James 7620 2705 47500.00
2705 Simpson Travis 7600 6554 68000.00
2906 Nakagawa Etsuro 6400 6554 72000.00
3598 Nakamura Eichiro 6480 2906 50000.00