Post reply

SubQuery

  • September 20, 2011 at 5:31 am

    #1383741

    good question!

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • September 20, 2011 at 7:27 am

    #1383839

    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?

  • September 20, 2011 at 9:36 am

    #1383927

    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

  • September 21, 2011 at 4:15 am

    #1384393

    Thanks for Valuable Question.Keep Posting

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • September 21, 2011 at 6:12 am

    #1384429

    Well, I got it wrong [i]sic[/i] for the right [i]sic[/i] reason: like the rest of you, if my column names don't match in my queries, I usually get errors. Usually. Not here, though, apparently.

    Interesting syntax - thanks for the question.

    I will now endeavor to forget I ever saw this, or I'll start getting sloppy and be back to column name mismatch errors! 😀

    Rich

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply