SQL/MP Reference Manual
HP NonStop SQL/MP Reference Manual—523352-013
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 statement. For information, see
CONTROL QUERY Directive on page C-74 (the HASH JOIN option) and CONTROL
TABLE Directive on page C-77 (the JOIN METHOD and JOIN SEQUENCE options).
For information on joins, see the SQL/MP Query Guide.
Examples - Joins
These examples refer to a database consisting of:
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
4096 Murakami Kazuo 6410 3598 36000.00
5361 Smythe Roger 7690 9069 42650.00
9069 Smith John 7690 2705 38760.00