Click here to Skip to main content
15,881,938 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I've currently joined a project that has the following select
SQL
SELECT TOP 1
     A.X
    ,A.Y
FROM
    (      
    SELECT 
        T.ENTITY_NAME   AS X
        ,T.SP_Y         AS Y
    FROM   
        dbo.TABLE       AS T
    WHERE  
         T.SP_Y      = @p_Y
     AND T.SP_EAT_ID = 16
    UNION ALL
    SELECT 
        T.ENTITY_NAME   AS X
        ,T.SP_Y         AS Y
    FROM   
        dbo.TABLE       AS T
    WHERE  
         T.SP_Y      = @p_Y
     AND T.SP_EAT_ID = 15
    ) AS A
) AS CN    ON CN.Y = H.Y


Unfortunately I can't give more details about the data, but from what was provided, how much would you guys say it could differ from this:
SQL
SELECT TOP 1
    T.ENTITY_NAME   AS X
    ,T.SP_Y         AS Y
FROM   
    dbo.TABLE       AS T
WHERE  
     T.SP_Y      = @p_Y
 AND T.SP_EAT_ID in (15, 16)
ORDER BY
    T.SP_EAT_ID desc
Posted
Comments
Richard MacCutchan 19-Feb-14 9:59am    
When you try them do you get the same results with both statements?
Paulo Augusto Kunzel 19-Feb-14 10:36am    
As far as I have tested, yes. But I'm not sure how either IN or UNION ALL perform behind the scene, that is why I'm a bit worried about any unexpected result
NeverJustHere 19-Feb-14 10:45am    
The in clause is equivalent to (T.SP_EAT_ID = 15 or T.SP_EAT_ID = 16).

You can see this when you use a column name that doesn't exist - you get an error for each value of the in clause.

The UNION ALL merges two result sets, on where T.SP_EAT_ID = 15, and one where T.SP_EAT_ID = 16.

These two ways of expressing the query look equivalent.

The difference between the your two queries is the impact of the TOP 1 and the ORDER BY statement.

The first query asks for the TOP 1 record, ie the first record in the unordered result set. This is up to the database to decide, and may be non-deterministic.

The second query asks for the TOP 1 record ordered by T.SP_EAT_ID, so the first record once the result set has been ordered by T.SP_EAT_ID. If the result set can contain multiple rows with the same value for this column, it may also be non-deterministic.
Paulo Augusto Kunzel 19-Feb-14 10:58am    
Wow. Thx man, you should put that as an answer!
Maciej Los 20-Feb-14 17:26pm    
Agree!

1 solution

Since there were no more comments to this question and no solutions were provided. I've looked into this with both university and work colleagues and we came up with the following solution:

Use

SQL
SELECT TOP 1
    T.ENTITY_NAME   AS X
    ,T.SP_Y         AS Y
FROM   
    dbo.TABLE       AS T
WHERE  
     T.SP_Y      = @p_Y
 AND T.SP_EAT_ID in (15, 16)
ORDER BY
    T.SP_EAT_ID desc


This was the chosen one due to be easier to read and achieving a similar performance to the first select.
For more details on the comparison. Please read the comment of "NeverJustHere" at 19-Feb-14.

Regards
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900