LEFT OUTER JOIN Operation

Specify a join clause that preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

Syntax

TableExpression  LEFT [ OUTER ] JOIN TableExpression 
{
ON boolean-expression
}

Description

See also Query Capabilities and Limitations.

The scope of expressions in either the ON clause includes the current tables and any tables in query blocks outer to the current SELECT. 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

--match all customers and their portfolio details, if any, having a specific 
--transaction ID
select * from trade.customers C LEFT OUTER JOIN trade.portfolio F on C.cid= F.cid where F.tid = ? 
-- use the synonymous syntax, LEFT JOIN, to achieve exactly
-- the same results as in the example above
select * from trade.customers C LEFT  JOIN trade.portfolio F on C.cid= F.cid where F.tid = ?