CODEWITHSUNDEEP
sql
McFixit
McFixit

Reputation: 436

Sql - Explicit order of WHERE conditions?

Is there any way to explicitly state the order for WHERE conditions to take place? I realize that the query optimizer will look at all parts of the WHERE clause to determine the most efficient way to satisfy the query, as stated in these answers:

Does order of where clauses matter in SQL

SQL - Does the order of WHERE conditions matter?

However, is there no way to check for a condition that other conditions will rely on? One of the answers from those threads touches on what I'm after, but doesn't offer a solution:

select *
from INFORMATION_SCHEMA.TABLES
where ISNUMERIC(table_name) = 1 and CAST(table_name as int) <> 0

This can fail because the CAST can be evaluated before determining whether the table_name field is numeric (and therefore throw an error for invalid casting).

Surely there must be a way to achieve this?

Upvotes: 6

Views: 947

Answers (2)

TT.
TT.

Reputation: 16146

Using a derived table:

SELECT *
FROM (
      SELECT *
      FROM INFORMATION_SCHEMA.TABLES
      WHERE ISNUMERIC(table_name)=1
     ) AS i
WHERE CAST(table_name AS INT)<>0

Alternatively, and most likely run in order, you can use a CASE statement:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE 0<>(CASE WHEN ISNUMERIC(table_name)=1 
          THEN CAST(table_name AS INT)
          ELSE 0 END)

It should be noted that for SQL Server there exist situations where the CASE-trick will fail. See the documentation on CASE, Remarks:

The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.

WITH Data (value) AS 
( 
SELECT 0 
UNION ALL 
SELECT 1 
) 
SELECT 
   CASE 
      WHEN MIN(value) <= 0 THEN 0 
      WHEN MAX(1/value) >= 100 THEN 1 
   END 
FROM Data ;

I suspect this might also be true for other RDBMS implementations.

Upvotes: 5

snow_FFFFFF
snow_FFFFFF

Reputation: 3311

I think you are asking if evaluation can short-circuit - it might help to read this:

Is the SQL WHERE clause short-circuit evaluated?

So, you probably need to evaluate the first condition before attempting the second. In MSSQL, you could use a CTE to do the first. Or, another option might be to use a CASE to only perform the second under certain conditions.

Upvotes: 1

Related Questions