How do I force SQL Server 2005 to run a join before the where?

Question

I've got a SQL query that joins a pricing table to a table containing user-provided answers. My query is used to get the price based on the entered quantity. Below is my SQL statement:

SELECT JobQuestion.Value, Price.Min, Price.Max, Price.Amount FROM Price
    INNER JOIN JobQuestion 
     ON Price.QuestionFK=JobQuestion.QuestionFK
     AND JobQuestion.JobFK=1
WHERE Price.Min <= JobQuestion.Value 
    AND Price.Max >= JobQuestion.Value

The problem is SQL Server is running the where clause before the JOIN and it is throwing the error:

Conversion failed when converting the varchar value 'TEST' to data type int.

because it is doing the min and max comparisons before the join ('TEST' is a valid user entered value in the JobQuestion table, but should not be returned when JobQuestion is joined to Price). I believe SQL Server is choosing to run the WHERE because for some reason the parser thinks that would be a more efficient query. If i Just run

SELECT JobQuestion.Value, Price.Min, Price.Max, Price.Amount FROM Price
    INNER JOIN JobQuestion 
     ON Price.QuestionFK=JobQuestion.QuestionFK
     AND JobQuestion.JobFK=1

I get these results back:

500 1    500  272.00
500 501  1000 442.00
500 1001 2000 782.00

So, adding the WHERE should filter out the last two and just return the first record. How do I force SQL to run the JOIN first or use another technique to filter out just the records I need?

Answer 1

You shouldn't be comparing strings to ints. If you have any influence at all over your table design, then split the two different uses of the JobQuestion.Value column into two different columns.

Answer 2

In case you have no influence over your table design - Could you try to filter out those records with numeric values using ISNUMERIC()? I would guess adding this to your where clause could help.

Answer 3

Try "re-phrasing" the query as follows:

SELECT *
FROM   (
          SELECT JobQuestion.Value, 
                 Price.Min, 
                 Price.Max, 
                 Price.Amount 
          FROM   Price
          INNER 
          JOIN   JobQuestion 
                 ON Price.QuestionFK = JobQuestion.QuestionFK
                 AND JobQuestion.JobFK = 1
       ) SQ
WHERE  SQ.Min <= SQ.Value 
AND    SQ.Max >= SQ.Value

As per the answer from Christian Hayter, if you have the choice, change the table design =)

Answer 4

First, this is very likely sign of poor design.
If you cannot change schema, then maybe you could force this behavior using hints. Quote:

Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query.

And some more:

Caution:
Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that < join_hint>, < query_hint>, and < table_hint> be used only as a last resort by experienced developers and database administrators.

Answer 5

You can likely remove the where... and just add those as predicates to your join. Since it's a inner join this should work

SELECT JobQuestion.Value, Price.Min, Price.Max, Price.Amount 
FROM Price    
INNER JOIN JobQuestion
ON Price.QuestionFK=JobQuestion.QuestionFK       
AND JobQuestion.JobFK=1
AND Price.Min <= JobQuestion.Value
AND Price.Max >= JobQuestion.Value