Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Outer joins

Typically, you create joins that return rows only if they satisfy join conditions; these are called inner joins, and are the default join used when querying. However, sometimes you may want to preserve all the rows in one table. To do this, you use an outer join.

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 =*).

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 Surname, OrderDate, City
FROM Customers LEFT OUTER JOIN SalesOrders
   ON Customers.ID = SalesOrders.CustomerID
WHERE Customers.State = 'NY'
ORDER BY OrderDate;

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

• Return one row for every sales order placed by a customer. More than one row is returned when the customer placed two or more sales orders, because a row is returned for each sales order. This is the same result as an inner join. The ON condition is used to match customer and sales order rows. The WHERE clause is not used for this step.

• Include one row for every customer who has not placed any sales orders. This ensures that every row in the Customers table is included. For all of these rows, the columns from SalesOrders are filled with nulls. These rows are added because the keyword OUTER is used, and would not have appeared in an inner join. Neither the ON condition nor the WHERE clause is used for this step.

• Exclude every row where the customer does not live in New York, using the WHERE clause.

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