what does it mean "SELECT ... FROM .... WHERE X NOT IN (8)"

Question

Post reply

what does it mean "SELECT ... FROM .... WHERE X NOT IN (8)"

Ronald-637944

Grasshopper

Points: 20
More actions
October 13, 2008 at 1:56 am

#192046

Can someone explain to me what it means when adding this to the end of a SQL query 'NOT IN (8)'

Viewing 7 posts - 1 through 7 (of 7 total)

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

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 1

Hi,

it basically means the value is NOT IN the range.

I.E

ColumnA IS NOT IN (1,2,3)

This would mean ColumnA is not equal to either 1,2 or 3.

http://msdn.microsoft.com/en-us/library/ms177682.aspx

Hope this helps.

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Ronald (10/13/2008)
Can someone explain to me what it means when adding this to the end of a SQL query 'NOT IN (8)'

Hi Ronald

Try the following statement.

[font="Courier New"]SELECT *

FROM (

SELECT 1 AS number UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10

) d

WHERE number < 21

--AND number NOT IN (8)

[/font]

As posted, the NOT IN (8) is commented out.

NOT IN is usually used with a list, e.g. NOT IN (3,5,8) or with a statement e.g. NOT IN (SELECT column FROM table WHERE...). With only one value in the list, your exampkle is functionally equivalent to <> 8

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ronald-637944 Grasshopper Points: 20 More actions · Answer 3

I would like to thank you for this information, I wasn't sure if it had a special meening, but now I understand why the script I am looking uses this

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

You're welcome Ronald, thank you for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 5

(off-topic) Chris, curious, how are you formatting your code? Are you using something to help (I think I remember from other posts there's an add-on or something that formats it), or are you coloring it using the font options in each post?

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

I use this handy gadget:

http://extras.sqlservercentral.com/prettifier/prettifier.aspx

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden