condition on a FROM clause or WHERE clause

  • Which is better from performance point of view: having a condition in FROM clause or WHERE clause.

    I understand the difference between legacy join involving the operators and SQL-92 standard spelling out INNER JOIN or LEFT OUTER JOIN etc...

  • If you are talking about JOIN condition, it is better and more correct in the JOIN clause, not in the WHERE clause. It is also better to limit your result as soon as possible. Consider this:

    SELECT col1, col2

    FROM t1

    JOIN t2

    ON t1.c3 = @variable

    AND t1.c4 = t2.c5

    Having ON t1.c3 = @variable before the join criteria helps to limit the result set before joining. This does not work though for LEFT JOIN. In that case you are forced to go the following way:

    SELECT col1, col2

    FROM t1

    LEFT JOIN t2

    ON t1.c4 = t2.c5

    AND t1.c3 = @variable

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply