Specify a JOIN clause that preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table. A LEFT OUTER JOIN B is equivalent to BRIGHT OUTER JOIN A, with the columns in a different order.


TableExpression  RIGHT [ OUTER ] JOIN TableExpression 
ON boolean-expression


See also Query Capabilities and Limitations.

The scope of expressions in 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).

Note: SQLFire imposes restrictions on the types of Join Queries which are supported. Please refer to the section Query limitations – joins, sub-queries, aggregates.


--match all customers and their portfolio details, if any, having a specific 
--transaction ID
select * from trade.portfolio F RIGHT OUTER JOIN trade.customers C on C.cid= F.cid where F.tid = ? 

-- use the synonymous syntax, RIGHT JOIN, to achieve exactly
-- the same results as in the example above
select * from trade.portfolio F RIGHT JOIN trade.customers C on C.cid= F.cid where F.tid = ? 
-- a TableExpression can be a joinOperation. Therefore
-- you can have multiple join operations in a FROM clause
-- List all the customers, there portfolio details, if any, and their sell 
-- orders, if any
select * from trade.SELLORDERS SO RIGHT OUTER JOIN TRADE.PORTFOLIO F RIGHT OUTER JOIN TRADE.CUSTOMERS  c on f.cid = so.cid on c.cid= f.cid where f.tid = ?