INNER JOIN Operation

Specify an explicit join clause.

Syntax

TableExpression  [ INNER ] JOIN TableExpression  
{ ON boolean-expression }

Description

See also Query Capabilities and Limitations.

A join clause can be specified using an ON with a boolean expression. The scope of expressions in the ON clause includes the current tables and any tables in outer query blocks to the current SELECT. In the following example, the ON clause refers to the current tables:

Select * from trade.securities INNER JOIN trade.portfolio 
     ON SECURITIES.sec_id = PORTFOLIO.sid and PORTFOLIO.tid = ?

The ON clause can reference tables not being joined and does not have to reference either of the tables being joined (though typically it does).

Example

-- Join the Customers and Portfolio tables
-- select all the columns from the customers table and
-- add the SID and QTY from the Portfolio table
-- to each row of the result
select C.* , F.sid, F.QTY from trade.customers  C JOIN trade.portfolio F ON C.cid = F.cid