Which of these Select to choose

Question

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 19-Feb-14 2:24am

Paulo Augusto Kunzel

Add a Solution

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

Add a Solution

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

Paulo Augusto Kunzel · Accepted Answer · 2014-04-09T06:47:00

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

Which of these Select to choose

1 solution

Solution 1

Add your solution here

Preview 0

Which of these Select to choose

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us