Suppose there are more than 1 condition in the WHERE clause & only the
logical operator 'AND' is being used in the WHERE clause. For eg. (note
that none of the conditions are bracketted)

SELECT...FROM...WHERE ColA>4 AND ColB<2 AND ColC<=10 AND ColD=5

Under such circumstances, will the conditions be evaluated in the order
in which they have been coded? Like, for e.g. in the above case, will
ColA>4 be evaluated first followed by ColB<2 after which ColC<=10 &
finally ColD=5 will be executed or will the 4 conditions be evaluated
together at the same time?

Whatever is the correct order of execution in the above example, does
that hold true if WHERE is replaced by ON? For e.g. in the following
query:

---------------------------------------------
SELECT t1.SID,t1.FName,t1.LName,t2.Total FROM
Table1 AS t1
JOIN
Table2 AS t2
ON
t1.SID=t2.SID AND t2.SID>20
---------------------------------------------

will t1.SID=t2.SID be evaluated first after which t2.SID>20 will be
evaluated or will both the conditions t1.SID=t2.SID & t2.SID>20 be
evaluated together at the same time?

Thanks,

Arpan

Arpan,

  With WHERE or ON, you have no control over the order of
evaluation of conditions.  The optimizer may rearrange things in
order to produce a more efficient query plan, and the order may
even change from one execution of the query to the next.  The
only way to control the order is with CASE, as in

WHERE
  CASE WHEN ColA <= 4 THEN 0
  WHEN ColB >= 2 THEN 0
  WHEN ColC > 10 THEN 0
  WHEN ColD <> 5 THEN 0
  ELSE 1 END = 1

If any of the columns can be NULL, this may give a different result,
and you will need to adjust the expression accordingly.

Chances are that the CASE expression will make your query less efficient,
because it will be impossible for the optimizer to use indexes to help
with the
evaluation.

Also worth noting, there is no difference between putting a condition in
the ON clause as opposed to the WHERE clause of an inner join.  An
ON clause is required, and no condition can go before the tables it refers
to have been mentioned, but where a condition can be moved, moving it
makes no difference.

Because the optimizer cannot always consider every possible query plan,
it is certainly possible that the order you specify will affect the
ultimate order
of evaluation, but don't assume that the ultimate order will be the
exact order
you specify.

Steve Kass
Drew University

Arpan wrote:

Show quoteHide quote

Using CASE in WHERE clause
Tempdb is growing and I can't tell what process is to blame
Beating a dead horse - Drop table from proc
Stored Procedure (Weird Question)
Basic Query Help