What is the sequence of execution of SQL statements?

1

Hello

I have the query below:

SELECT DISTINCT [col1],
CASE WHEN [Col2] = 'Text1'
THEN   'Text2'
WHEN [col3] IS NULL
THEN 'Text3'
ELSE [col3] END AS [col3],
'Text4' AS [col4]       
FROM Table1
WHERE ([col3] IN ('text5','text6') OR [col3] IS NULL)

My questions are:

  1. what happens first and which second? The CASE statement (i.e. the text replacements that will form col3 or the WHERE statement (i.e. the filtering of the original col3)?
  2. why when I replace the last WHERE statement with the following, I get a different number of results?
    WHERE [col3] IN ('text5','text6',NULL)

Thanks!

2

For the Case statement, the first true wins. For the where Clause, when you move the NULL to the in clause it is treated like a string. You need to use Col3 Is null to get the null values

2 Likes
3

The best practice is as suggested by @mike01

if you want the NULL to be treated as NULL
inside an IN clause , you will have to
SET ANSI_NULLS OFF

4

The FROM is processed first - then the WHERE and finally the SELECT. The reference to [col3] in your code is referencing the column in the table, not your calculated value from the CASE expression.

If you want to filter based on the calculation - you have a couple of options:

  1. Put the code in a CTE - then in the final query you can reference the CTE column values
  2. Put the calculation into a CROSS APPLY - then use the returned column in the WHERE clause
  3. Use a derived table to calculate the values
  4. Use the case expression in the WHERE clause
1 Like
5

  1. The WHERE is evaluated first. SQL doesn't waste resources processing the SELECT clauses until it confirms that the row is actually valid for the query, that is, that it passed the WHERE requirements.

  2. NULL is never equal to any value, including another NULL. "colA IN (val1, val2, ...)" is just short hand for "(colA = val1 OR colA = val2 OR ...)". Thus, col3 IN (..., NULL) will not match when col3 is NULL because NULL is never "=" NULL. (SQL is not treating the keyword NULL as a string (and never does); if you want a string of NULL, then you must write 'NULL'.).

2 Likes