Contents Index Inner joins Outer joins and join conditions

ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Inner and outer joins

Outer joins


A left or right outer join of two tables preserves all the rows in one table, and supplies nulls for the other table when it does not meet the join condition. A left outer join preserves every row in the left-hand table, and a right outer join preserves every row in the right-hand table. In a full outer join, all rows from both tables are preserved.

The table expressions on either side of a left or right outer join are referred to as preserved and null-supplying. In a left outer join, the left-hand table expression is preserved and the right-hand table expression is null-supplying.

For information about creating outer joins with Transact-SQL syntax, see Transact-SQL outer joins (*= or =*).

Example 

For example, the following statement includes all customers, whether or not they have placed an order. If a particular customer has placed no orders, each column in the result that corresponds to order information contains the NULL value.

SELECT lname, order_date, city
FROM customer LEFT OUTER JOIN sales_order
    ON customer.id = sales_order.cust_id
WHERE customer.state = 'NY'
ORDER BY order_date
lname order_date city
Thompson (NULL) Bancroft
Reiser 2000-01-22 Rockwood
Clarke 2000-01-27 Rockwood
Mentary 2000-01-30 Rockland
... ... ...

You can interpret the outer join in this statement as follows. Note that this is a conceptual explanation, and does not represent how Adaptive Server Anywhere actually computes the result set.


Outer joins and join conditions
Understanding complex outer joins
Outer joins of views and derived tables

Contents Index Inner joins Outer joins and join conditions