Outer query is running before inner query

Question

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?

Answer 1

Maybe that:

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

Answer 2

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

Answer 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