Post reply

SELECT Statement Question

  • March 7, 2008 at 12:45 pm

    #203934

    Hello All,

    I am a Newbie to SQL Server, and just finished an extensive online course covering everything from development to optimization.

    I have found that most of my questions are regarding syntax.

    In the following SELECT statement, the goal is to select only beverages from Northwind DB:

    -----------------

    USE Northwind

    SELECT ProductName

    FROM Products

    WHERE CategoryID IN

    (SELECT CategoryID

    FROM Categories

    Where CategoryName = 'Beverages')

    ----------------------

    I am unclear as to why there is a "sub SELECT" statement after WHERE Category ID IN is. The syntax is confusing to me.

    Would you please describe in basic terms why this query is built the way it is. Thank you for your assistance.

  • March 7, 2008 at 12:54 pm

    #786872

    USE Northwind

    SELECT ProductName FROM Products WHERE CategoryID IN

    (SELECT distinct CategoryID

    FROM Categories Where CategoryName = 'Beverages')

    1. Products table doesnot have CategoryName

    2. Always put distinct in subquery.

  • March 7, 2008 at 12:56 pm

    #786873

    The subquery is identifying the CategoryID for the specified CategoryName, 'Beverages', so that the main query only returns products for that specified category.

    Another way to write that query is as follows:

    select

    prod.ProductName

    from

    dbo.Products prod

    inner join dbo.Categories cat

    on (prod.CategoryID = cat.CategoryID)

    where

    cat.CategoryName = 'Beverages'

    😎

  • March 7, 2008 at 1:01 pm

    #786874

    Roberta -

    to be exact - you're asking it to show you all of the product names of products in the 'Beverage' category (or categories, since given the it's written there COULD be 2 separate categories called 'Beverage').

    The subquery pulls all of the category ID's which have the name 'Beverage'. The list of those category ID's is passed to the outer query, which then filters the Products table to only those rows which have one of those category ID's. The ProductName column for each of those rows is returned.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • March 7, 2008 at 1:07 pm

    #786877

    I'll throw in that just from a code consistency standpoint, WHERE IN should be rewritten to a normal INNER JOIN like Lynn wrote it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 7, 2008 at 1:14 pm

    #786881

    Jeff Moden (3/7/2008)

    I'll throw in that just from a code consistency standpoint, WHERE IN should be rewritten to a normal INNER JOIN like Lynn wrote it.

    Thanks, Jeff. You should know that I started doing it that way because of you. I am trainable.

    😎

  • March 7, 2008 at 1:32 pm

    #786886

    zubamark (3/7/2008)

    2. Always put distinct in subquery.

    Don't ever put a distinct in an IN or EXISTS subquery. It's meaningless and may cause SQL to do unnecessary work.

    In terms of an IN, all SQL's looking for is whether or not the values are in the returned list/result set. How many times they are there is irrelevant.

    IN (1,2,3) and IN (1,1,1,1,3,3,3,2,3,1,2,3) are equivalent for the purposes of what values are in there.

    In terms of an EXISTS, SQL's just looking for the existence of a row. It doesn't care how many there are.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 7, 2008 at 1:55 pm

    #786898

    Thank you, your answer is straight and to the point.

    The thing that looks odd to me is that after

    WHERE CategoryID IN it looks like the query is sort

    of hanging without an ending.

    That's the thing about syntax, it's somewhat choppy.

    I'll be submitting more questions like this, so please

    keep me in mind for your expertise, thanks again. ; )

  • March 7, 2008 at 2:00 pm

    #786902

    Hello Lynn,

    Thank you for your reply, it was really helpful.

    I appreciate the "rewrite" in a JOIN query,

    it is something I am familiar with as well, especially

    in the (prod.CategoryID = cat.CategoryID) portion.

    That's the other thing about snytax, it can be put

    together in a variety of ways, which makes it a

    little more squirrely for me to grasp at this point.

    I'll be posting other questions, so please keep me

    in mind for your expertise. Thanks again...; )

  • March 7, 2008 at 2:05 pm

    #786905

    Hi Jeff,

    Thanks for weighing in re: Lynn's rewrite. Now, it makes more

    sense to me, that hanging "IN" was throwing me off!

    I'll be sending through some far meatier examples of syntax, so

    please keep an eye out. Thanks again, I see per Lynn's response,

    that you have shown her the promised land which is great

    as I'm on the path to it. Amen.

  • March 7, 2008 at 2:08 pm

    #786906

    Hi Matt,

    Thank you for putting it in plain English. When it comes to syntax

    I need to hear it in regular daily terms. This is the one area that

    my online course did not cover really at all.

    Please keep an eye out for my future "riddles." Thanks again.

    🙂

  • March 7, 2008 at 2:11 pm

    #786910

    Hi Gail,

    Thank you for your additional input on the now infamous "IN"

    portion of this statement. It was the one word that was

    giving me pause....

    As stated, I'll be submitting some bigger and badder syntax for

    expert review. This is my first post, and all of you have been

    fabu.

    P.S., I like your tag line....;)

  • March 7, 2008 at 3:50 pm

    #786963

    robertafricker (3/7/2008)

    Hi Jeff,

    Thanks for weighing in re: Lynn's rewrite. Now, it makes more

    sense to me, that hanging "IN" was throwing me off!

    I'll be sending through some far meatier examples of syntax, so

    please keep an eye out. Thanks again, I see per Lynn's response,

    that you have shown her the promised land which is great

    as I'm on the path to it. Amen.

    Heh... you made the same mistake that I did... "Lynne" is the feminine spelling of the name... "Lynn" is the masculine and Lynn Petis is a him, not a her.

    Gail is spot on about the Distinct thing.

    And, I have to say that sometimes IN can perform better than an INNER JOIN because of it's natural ability to ignore duplicates. Usually the INNER JOIN is the high road, but like anything else, it depends. Just wanted you to be aware of both options and shoot for the INNER JOIN if it makes sense to do so.

    Also, WHERE NOT IN performs just a tiny bit better than an OUTER join with a null detector. Only thing is, it's good for only one column... if you need more than one column, you can use a WHERE NOT EXISTS but that uses a correlated sub-query in most cases... that a bit less effecient and I just don't like them because they can't stand alone for troubleshooting. So, if you need a WHERE NOT IN on more than one column, I recommend using the OUTER JOIN method.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 7, 2008 at 6:40 pm

    #787000

    Hi Jeff,

    Thanks for clearing up the gender thing, although my middle name

    is Lynn....hhmmmm...:hehe:

    Besides that, your addt'l info got my head swimming for the moment,

    although I'll keep it for future reference.

    Please be "gentle" as I am a Newbie, and my brain is at such a

    tender stage of "syntaxing.".....:smooooth:

    I'll be sending some more examples through over the weekend,

    so pls keep an eye out. Thanks again.

  • March 11, 2008 at 11:06 am

    #788168

    zubamark (3/7/2008)

    USE Northwind

    SELECT ProductName FROM Products WHERE CategoryID IN

    (SELECT distinct CategoryID

    FROM Categories Where CategoryName = 'Beverages')

    1. Products table doesnot have CategoryName

    2. Always put distinct in subquery.

    NO!! Distinct in this kind of subquery just creates more work for the server without giving you any benefit. In other words, it has a cost, but no payoff.

    Test it with and without, using statistics io ("set statistics io on" at the beginning of the query). You'll see why.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 15 posts - 1 through 15 (of 35 total)

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