Chapter 4: Joins: Retrieving Data from Several Tables

Correlation name and column referencing rules for ANSI joins

The following are the correlation name and column reference rules specifically for ANSI joins. For more information about correlation names, see “Self-joins and correlation names”.

If a table or view uses a correlation name reference to the 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 name t 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 or 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.

View this book as PDF