User's Guide
   PART 1. Working with Databases
     CHAPTER 6. Joins: Retrieving Data from Several Tables       

Transact-SQL outer joins

Joins that include all rows, regardless of whether or not they match the join condition, are called outer joins. Adaptive Server Anywhere supports both left and right outer joins via the LEFT OUTER and RIGHT OUTER keywords. For compatibility with Adaptive Server Enterprise, Anywhere supports the Transact-SQL-language counterparts of these keywords.

In the Transact-SQL dialect, joins are accomplished by separating table names with commas in the FROM clause. The join conditions appear in the WHERE clause, rather than in the ON phrase. Special conditional operators indicate the type of join.

  Transact-SQL left-outer joins

The left outer join operator, *=, selects all rows from the left hand table that meet the statement's restrictions. The right hand table generates values if there is a match on the join condition. Otherwise, the second table generates null values.

For example, the following left outer join lists all customers and finds their order dates (if any):

SELECT fname, lname, order_date
FROM customer, sales_order
WHERE customer.id *= sales_order.cust_id
ORDER BY order_date

Preserved and null-supplying tables 

A table is either a preserved or a null-supplying table for an outer join. If the join operator is *=, the second table is the null-supplying table; if the join operator is =*, the first table is the null-supplying table.

You can compare a column from the inner table to a constant as well as using it in the outer join. For example, you can use the following statement to find information about customers in California.

SELECT fname, lname, order_date
FROM customer, sales_order
WHERE customer.state = 'CA'
   AND customer.id *= sales_order.cust_id
ORDER BY order_date

However, the inner table in an outer join cannot also participate in a regular join clause.

  Transact-SQL right-outer joins

The right outer join, =*, selects all rows from the second table that meet the statement's restrictions. The first table generates values if there is a match on the join condition. Otherwise, the first table generates null values.

The right outer join is specified with the comparison operator =*, which indicates that all the rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.

Substituting this operator in the outer join query shown earlier gives this result:

SELECT fname, lname, order_date
FROM sales_order, customer
WHERE sales_order.cust_id =* customer.id
ORDER BY order_date

You can further restrict an outer join by comparing it to a constant. This means that you can zoom in on precisely the values you really want to see and use the outer join to list the rows that did not make the cut.

  Transact-SQL outer join restrictions

There are several restrictions for Transact-SQL outer joins:

• You cannot mix SQL/92 syntax and Transact-SQL outer join syntax in a single query. This applies to views used by a query also: if a view is defined using one dialect for an outer join, the same dialect must be used for any outer-join queries on that view.

• A table cannot participate in both a Transact-SQL outer join clause and a regular join clause. For example, the following WHERE clause is not allowed:

  WHERE R.x *= S.x
  AND S.y = T.y

  When you cannot rewrite your query to avoid using a table in both an outer join and a regular join clause, you must divide your statement into two separate queries.

• You cannot use a subquery that contains the null-supplying table of an outer join. For example, the following WHERE clause is not allowed:

  WHERE R.x *= S.y
  AND EXISTS ( SELECT *
               FROM T
               WHERE T.x = S.x )

• If you submit a query with an outer join and a qualification on a column from the null-supplying table of the outer join, the results may not be what you expect. The qualification in the query does not restrict the number of rows returned, but rather affects which rows contain the null value. For rows that do not meet the qualification, a null value appears in the inner table's columns of those rows.

  Views used with Transact-SQL outer joins

If you define a view with an outer join, and then query the view with a qualification on a column from the inner table of the outer join, the results may not be what you expect. The query returns all rows from the null-supplying table. Rows that do not meet the qualification show a NULL value in the appropriate columns of those rows.

The following rules determine what types of updates you can make to columns through join views:

• DELETE statements are not allowed on join views.

• INSERT statements are not allowed on join views created WITH CHECK OPTION.

• UPDATE statements are allowed on join views WITH CHECK OPTION. The update fails if any of the affected columns appears in the WHERE clause, in an expression that includes columns from more than one table.

• If you insert or update a row through a join view, all affected columns must belong to the same base table.

  How NULL affects Transact-SQL joins

NULL values in tables or views being joined will never match each other. Since bit columns do not permit NULLs, a value of 0 appears in an outer join when there is no match for a bit column that is in the inner table.

The result of a join of NULL with any other value is NULL. Because null values represent unknown or inapplicable values, Transact-SQL has no reason to believe that one unknown value matches another.

You can detect the presence of null values in a column from one of the tables being joined only by using an outer join. If there are two tables, each of which has a NULL in the column that will participate in the join. A left outer join displays the NULL in the first table.