Specifying Joins in FROM or WHERE Clauses

The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.

Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the FROM clause is not recommended, is no longer documented, and will be dropped in a future release.

For example, these queries both specify a left outer join to SELECT 23 rows that display the title identification number, title name, and the number of books sold:

-- Join in WHERE clause.

USE pubs

SELECT t.title_id, t.title, s.qty

FROM titles AS t, sales AS s

WHERE t.title_id *= s.title_id

-- Join in FROM clause.

USE pubs

SELECT t.title_id, t.title, s.qty

FROM titles AS t LEFT OUTER JOIN sales AS s

    ON t.title_id = s.title_id

In this query, a search condition is also specified in the WHERE clause:

-- Join and search condition in WHERE clause.

USE pubs

SELECT t.title_id, t.title, s.qty

FROM titles AS t, sales AS s

WHERE t.title_id *= s.title_id

    AND s.stor_id = '7066'

The condition stor_id = ‘7066’ is evaluated along with the join. The join only selects the rows for stor_id 7066 from the sales table, but because it is an outer join null values are supplied as the store information in all the other rows. This query returns 18 rows.

The join condition can be moved to the FROM clause, and the stor_id condition left in the WHERE clause:

USE pubs

SELECT t.title_id, t.title, s.qty

FROM titles AS t LEFT OUTER JOIN sales AS s

    ON t.title_id = s.title_id

WHERE s.stor_id = '7066'

This query returns only two rows because the restriction of stor_id = ‘7066’ is applied after the left outer join has been performed. This eliminates all the rows from the outer join that have NULL for their stor_id. To return the same information with the join condition in the FROM clause, specify the stor_id = ‘7066’ condition as part of the ON join_criteria section in the FROM clause and remove the WHERE clause:

USE pubs

SELECT t.title_id, t.title, s.qty

FROM titles AS t LEFT OUTER JOIN sales AS s

    ON t.title_id = s.title_id

    AND s.stor_id = '7066'

See Also