'Outer query is running before inner query

Why does this not work?

select *
from
(
    select membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where cast(membership_number as int) > 2

See SQL Fiddle Demo.

The subquery should filter out data that is non numeric, and the outer query is casting this to an integer so that I can look for anything > 2.

It seems like it is running the where clause of the outer query first. How do I get around this?

sqlsql-servertsql


Solution 1:[1]

Very interesting, I tried to reproduce this on SQL Server and found next. I changed your query to simple just to make sure that query will not fail and I can see the execution plan:

select *
from
(
    select membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where membership_number > '2'

Execution plan is has Table Scan with predicate:

[master].[dbo].[members].[membership_number]>'2' 
    AND NOT [master].[dbo].[members].[membership_number] like '%[^0-9]%'

So this is because SQL Optimization engine works in this way (as somebody said - nobody can guarantee you the order of where clauses). One of the ways to fix it probably is to use ISNUMERIC before 

select *
from
(
    select membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where ISNUMERIC(mem.membership_number) = 1 and cast(mem.membership_number as int) > 2

Solution 2:[2]

Maybe that:

select *
from
(
    select
        membership_number
    from
        members
    where
        membership_number not like '%[^0-9]%'
) mem
where Try_Convert(int, membership_number) > 2

Solution 3:[3]

I had the issue before. What I did was:

1, you can have a view which does:

select membership_number
    from members
    where membership_number not like '%[^0-9]%'

2, or use temp table for it

3, or use case clause:

select *
from
(
    select membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where (CASE WHEN ISNUMERIC(membership_number) THEN cast(membership_number as int) ELSE 0 END) > 2

did not have a elegant solution, but hope this helps

Solution 4:[4]

The comments explain how the execution plan can (at times) choose to evaluate the cast prior to the like. A case statement can aid the order of evaluation but as Adams mentions even this method is not 100%. 

select  *
from    members
where   case
            when membership_number like '%[^0-9]%' then 0
            when cast(membership_number as int) > 2 then 1
            else 0
        end = 1

Solution 5:[5]

You can try this, in following query first condition is executed and if it fails then it won't executes 2nd condition

select
    membership_number
  from
    members
where 
isnumeric(membership_number) = 1 and 
cast(membership_number as int) > 2

And to answer why your query is not working check this explanation here

Solution 6:[6]

Old query, but still relevant.

Found this trick to force subquery to be run first. Try this:

select *
from
(
    select TOP (9223372036854775807) membership_number
    from members
    where membership_number not like '%[^0-9]%'
) mem
where cast(membership_number as int) > 2

Value 9223372036854775807 is the BIGINT max value, probably big enough for most cases.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 outcoldman
Solution 2 mkjasinski
Solution 3 Ethan Li
Solution 4
Solution 5 Community
Solution 6 JERKER

Related Questions