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

按此处以在 DocCommentXchange 中显示和讨论此页。以后您将会被自动转到哪里。

 SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Querying and modifying data » Joins: Retrieving data from several tables » Inner and outer joins

SQL Anywhere 12.0.0 » SQL Anywhere Server - SQL Usage » Querying and modifying data » Joins: Retrieving data from several tables » Inner and outer joins

 

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

Note

Support for the Transact-SQL outer join operators *= and =* is deprecated and will be removed in a future release.

In accordance with ANSI/ISO SQL standards, SQL Anywhere supports the LEFT OUTER, RIGHT OUTER, and FULL OUTER keywords. For compatibility with Adaptive Server Enterprise before version 12, SQL Anywhere also supports the Transact-SQL counterparts of these keywords, *= and =*, providing the tsql_outer_joins database option is set to On. See tsql_outer_joins option.

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" at  http://www.sybase.com/detail?id=1017447.

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 before version 12, the join condition must appear in the WHERE clause (ON was not supported).

Caution

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

 Example

The following left outer join lists all customers and finds their order dates (if any):

SELECT GivenName, Surname, OrderDate FROM Customers, SalesOrders WHERE Customers.ID *= SalesOrders.CustomerID ORDER BY OrderDate;

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

SELECT GivenName, Surname, OrderDate FROM Customers LEFT OUTER JOIN SalesOrders ON Customers.ID = SalesOrders.CustomerID ORDER BY OrderDate;

---

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