SELECT Statement Question

Question

Post reply

SELECT Statement Question

robertafricker

Mr or Mrs. 500

Points: 561
More actions
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.

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

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

zubamark Right there with Babe Points: 735 More actions · Answer 1

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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'

😎

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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?

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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.

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

robertafricker Mr or Mrs. 500 Points: 561 More actions · Answer 7

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. ; )

robertafricker Mr or Mrs. 500 Points: 561 More actions · Answer 8

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...; )

robertafricker Mr or Mrs. 500 Points: 561 More actions · Answer 9

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.

robertafricker Mr or Mrs. 500 Points: 561 More actions · Answer 10

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.

🙂

robertafricker Mr or Mrs. 500 Points: 561 More actions · Answer 11

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....;)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 12

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)

robertafricker Mr or Mrs. 500 Points: 561 More actions · Answer 13

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.

GSquared SSC Guru Points: 260824 More actions · Answer 14

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