Outer query is running before inner query

sql, sql-server, t-sql

Solution

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

Problem

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?

Original source

Related problems