FROM TABLE

This SHOWPLAN step indicates the table that the query is reading from. In most queries, the FROM TABLE will be followed on the next line by the name of the table from which it is being selected. If an alias was specified for the table in the FROM clause, it is also shown in the next line. In other cases, it might indicate that it is selecting from a worktable (discussed later). The main importance of examining the table names after the FROM TABLE output is to determine the order in which the query optimizer is joining the tables. The order of the tables listed after the FROM TABLE statements in the SHOWPLAN output indicates the same order that the tables were joined; this order might be (and often is) different from the order in which they are listed in the FROM clause of the query or the order in which they appear in the WHERE clause of the query. This is because the query optimizer examines all different join orders for the tables involved and picks the join order that will require the least amount of I/Os.

Query:

SELECT authors.au_id, au_fname, au_lname
FROM authors, titleauthor, titles
WHERE authors.au_id = titleauthor.au_id
AND titleauthor.title_id = titles.title_id
AND titles.type = 'psychology'

SHOWPLAN:

STEP 1
The type of query is SELECT
FROM TABLE
titles
Nested iteration
Table Scan
FROM TABLE
titleauthor
Nested iteration
Table Scan
FROM TABLE
authors
Nested iteration
Table Scan

This query illustrates the order in which the SQL Server query optimizer joins the tables; it is not the order in which they were listed in the FROM clause or the WHERE clause. By examining the order of the FROM TABLE statements, you can see that the qualifying rows from the titles table are first located (using the search clause titles.type = 'psychology'). Those rows are then joined with the titleauthor table (using the join clause titleauthor.title_id = titles.title_id), and finally the titleauthor table is joined with the authors table to retrieve the desired columns (using the join clause authors.au_id = titleauthor.au_id).