• Kenneth Wymore (9/20/2011)


    SQLRNNR (9/19/2011)


    Kenneth Wymore (9/19/2011)


    Any idea as to why they would allow this behavior in a join? Seems to me like it would actually introduce more confusion than convenience.

    I think they have to allow it due to the requirement that a subquery that is based on values instead of a query requires the same syntax.

    Here's an article on that. http://jasonbrimhall.info/2011/08/31/bitwise-and-derived-table-revisited/

    I reviewed your other post about this and I see what you mean about the subquery using values instead of a table select. I have never seen values used in that way exactly but I am sure there are times when it is necessary. When there is a list of static values to reference, I have usually seen it coded as follows.

    SELECT *

    FROM (

    SELECT 1 as a, 2 as b

    UNION ALL

    SELECT 3 as a, 4 as b

    UNION ALL

    SELECT 5 as a, 6 as b

    UNION ALL

    SELECT 7 as a, 8 as b

    UNION ALL

    SELECT 9 as a, 10 as b

    ) as MyTable;

    --OR using a temp table

    IF OBJECT_ID(N'TempDB..#MyTable') IS NOT NULL

    BEGIN

    DROP TABLE #MyTable

    END

    CREATE TABLE #MyTable

    (a INT, b INT)

    INSERT INTO #MyTable

    SELECT 1 as a, 2 as b

    UNION ALL

    SELECT 3 as a, 4 as b

    UNION ALL

    SELECT 5 as a, 6 as b

    UNION ALL

    SELECT 7 as a, 8 as b

    UNION ALL

    SELECT 9 as a, 10 as b

    ;

    SELECT * FROM #MyTable;

    Using the union all statements is a bit tedious but that is what I have normally seen. If the same set needs to be used differently for multiple queries then it is typically dropped into a temp table or a regular table. I have seen this option used before just to keep the main query from looking overly complicated too.

    I am guessing that using a set of values like you showed on your post would be more common when dealing with applications? For example, where you don't want to insert user supplied values into a table but instead are just using them temporarily in the subquery?

    That is one place. It is not a very common thing to see - imo. I've seen it in solutions here at SSC. I have also used that method on occasion for that very reason (and because it is faster).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events