ASA Getting Started
  Selecting Data from Multiple Tables

Joining tables using outer joins

In the previous examples, you created joins that returned rows only if they satisfied the join conditions. These joins are called inner joins, and are the default. Sometimes, you may wish to preserve all the rows in one table. To do this, you use an outer join.

You can specify a right outer join, which preserves all the rows in the right table; a left outer join, which preserves the left table; or a full outer join, which preserves all the rows in both tables.

List all customers, and the dates of any orders they have placed

• In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.

  SELECT lname, order_date, city
  FROM customer KEY LEFT OUTER JOIN sales_order
  WHERE customer.state = 'NY'
  ORDER BY order_date

  lname	order_date	city
  Thompson	(NULL)	Bancroft
  Reiser	1993-01-22	Rockwood
  Clarke	1993-01-27	Rockwood
  Mentary	1993-01-30	Rockland
  ...	...	...

The 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 NULL.

For more information, see Outer joins.