ANSI joins

Previous versions of Adaptive Server only provided syntax for a Transact-SQL join, which included the =* and *= symbols for specifying an right or a left join, respectively. Adaptive Server 12 includes the ANSI syntax for joining tables or views, which allows you to write either:

Inner joins, in which the joined table includes only the rows of the inner and outer tables that meet the conditions of the on clause. The result set of a query that includes an inner join does not include any null supplied rows for the rows of the outer table that do not meet the conditions of the on clause. The syntax for an ANSI inner join is:
```
select select_list 
from table1 inner join table2
on join_condition
```
For example:
```
select au_id, titles.title_id, title, price
from titleauthor inner join titles
on titleauthor.title_id = titles.title_id
and price > 15
```
Outer joins, in which the joined table includes all the rows from the outer table whether or not they meet the conditions of the on clause. If a row does not meet the conditions of the on clause, values from the inner table are stored in the joined table as null values. The where clause of an ANSI outer join restricts the rows that are included in the query result. ANSI syntax also allows you to write nested outer joins. The syntax for an ANSI outer join is:
```
select select_list 
from table1 {left | right} [outer] join
		table2
on predicate 
[join restriction]
```
For example:
```
select au_fname, au_lname, pub_name
from authors left join publishers
on authors.city = publishers.city
```
Sybase recommends that applications use ANSI outer joins because they unambiguously specify whether the on or where clause contains the predicate, which is ambiguous when you use Transact-SQL outer joins.

For more information see The Transact-SQL User’s Guide.