Microsoft® SQL Server 2012 Unleashed

Identifying Join Clauses

The next type of clause the Query Optimizer looks for during the query analysis phase is the join clause. A join condition is specified in the FROM clause using the JOIN keyword, as follows:

Click here to view code image

FROM table1 JOIN table2  on table1.column = table2.column

Alternatively, join conditions can be specified in the WHERE clause using the old-style join syntax, as shown in the following example:

Click here to view code image

Table1.Column Operator Table2.Column

A join clause always involves two tables, except in the case of a self-join, but even in a self-join, you must specify the table twice in the query. Here’s an example:

Click here to view code image

select employee = e.LastName + ', ' + e.FirstName, ...