Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: Did You Know IN Can Do This?

Database Programming: Did You Know IN Can Do This?

  • Comments 5
  • Likes

Courtesy of Jens Suessmeyer comes this little gem purloined from an internal discussion; cut-and-paste this into SQL Server Management Studio and see if you can answer the questions in the comments before you execute the script:

--  create and populate a table variable
DECLARE @Demonstration TABLE (
    ID INT IDENTITY(1,1),
    Col1 INT,
    Col2 INT,
    Col3 INT
    )

 

INSERT @Demonstration (Col1,Col2,Col3) VALUES (1, 2, 100)
INSERT @Demonstration (Col1,Col2,Col3) VALUES (2, 100, 1)
INSERT @Demonstration (Col1,Col2,Col3) VALUES (100, 1, 2)

 

--  this one you can probably guess..
SELECT  ID
FROM    @Demonstration
WHERE   Col1 IN (1,2)

 

--  but what's going to happen here?
SELECT  ID
FROM    @Demonstration
WHERE   1 IN (Col1,Col2)

I knew IN could examine a column for a list of values; I didn’t know that it could examine a list of columns for a value.  For the record, here’s the result set:

ID
1
2

ID
1
3

This works in every version of SQL Server as far back as I can check (SQL Server 2000 SP4).  Thanks, Jens, for debunking a case of one-dimensional thinking on my part!

-wp


this copyrighted material was originally posted at http://blogs.technet.com/wardpond. 

the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.

the author welcomes and appreciates links to and citations of his work.  however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.

Comments
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment