Contents Index Outer joins of views and derived tables Transact-SQL outer join limitations

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

Transact-SQL outer joins (*= or =*)


In accordance with ANSI/ISO SQL standards, Adaptive Server Anywhere supports the LEFT OUTER, RIGHT OUTER, and FULL OUTER keywords. For compatibility with Adaptive Server Enterprise prior to version 12, Adaptive Server Anywhere also supports the Transact-SQL counterparts of these keywords, *= and =*. However, there are some limitations and potential problems with the Transact-SQL semantics.

For a detailed discussion of Transact-SQL outer joins, see the whitepaper Semantics and Compatibility of Transact-SQL Outer Joins , which is available at http://www.ianywhere.com/whitepapers/tsql.html.

Warning: When you are creating outer joins, do not mix *= syntax with ON phrase syntax. This also applies to views that are referenced in the query.

In the Transact-SQL dialect, you create outer joins by supplying a comma-separated list of tables in the FROM clause, and using the special operators *= or =* in the WHERE clause. In Adaptive Server Enterprise prior to version 12, the join condition must appear in the WHERE clause (ON was not supported).

Example 

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

This statement is equivalent to the following statement, in which ANSI/ISO syntax is used:

SELECT fname, lname, order_date
FROM customer LEFT OUTER JOIN sales_order
ON customer.id = sales_order.cust_id
ORDER BY order_date

Transact-SQL outer join limitations
Using views with Transact-SQL outer joins
How NULL affects Transact-SQL joins

Contents Index Outer joins of views and derived tables Transact-SQL outer join limitations