Correlation Name and Column Referencing Rules for ANSI Joins

SAP ASE provides correlation name and column reference recommendations specifically for ANSI joins.

If a table or view uses a correlation name reference to a column or view, it must always use the same correlation name, rather than the table name or view name. That is, you cannot name a table in a query with a correlation name and then use its table name later. The following example correctly uses the correlation namet to specify the table where its pub_id column is specified:
```
select title, t.pub_id, pub_name
from titles t left join publishers p
on t.pub_id = p.pub_id
```
However, the following example incorrectly uses the table name instead of the correlation name for the titles table (t.pub_id) in the on clause of the query, and produces the subsequent error message:
```
select title, t.pub_id, pub_name
from titles t left join publishers p
on titles.pub_id = p.pub_id
```
```
Msg 107, Level 15, State 1:
Server ‘server_name’, Line 1:
The column prefix ‘t’ does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
```
The restriction specified in the on clause can reference:
- Columns that are specified in the joined table’s reference
- Columns that are specified in joined tables that are contained in the ANSI join (for example, in a nested join)
- Correlation names in subqueries for tables specified in outer query blocks
The condition specified in the on clause cannot reference columns that are introduced in ANSI joins that contain another ANSI join (typically when the joined table produced by the second join is joined with the first join).
Here is an example of an illegal column reference that produces an error:
```
select * 
from titles left join titleauthor
on titles.title_id=roysched.title_id    /*join #1*/
left join roysched
on titleauthor.title_id=roysched.title_id    /*join #2*/
where titles.title_id != "PS7777"
```
The first left join cannot reference the roysched.title_id column because this column is not introduced until the second join. You can correctly rewrite this query as:
```
select * 
from titles 
left join (titleauthor
left join roysched
on titleauthor.title_id = roysched.title_id) /*join #1*/
on titles.title_id = roysched.title_id      /*join #2*/
where titles.title_id != "PS7777"
```
And another example:
```
select title, price, titleauthor.au_id, titleauthor.title_id, pub_name, publishers.city
from roysched, titles
left join titleauthor 
on roysched.title_id=titleauthor.title_id
left join authors 
on titleauthor.au_id=roysched.au_id, publishers
```
In this query, neither the roysched table nor the publishers table are part of either left join. Because of this, neither left join can refer to columns in either the roysched or publishers tables as part of their on clause condition.