Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Save some time and key-typing

Sometimes you have a denormalized table with several BIT columns used as flags.

Say you want to select every row that has at least one flag set. That's easy.

SELECT * FROM Table1 WHERE Flag1 = 1 OR Flag2 = 1 OR Flag3 = 1...

But how to easy select all records where all flags are not set?

SELECT * FROM Table1 WHERE Flag1 = 0 AND Flag2 = 0 AND Flag3 = 0...

That can be the way you normally write, and it can get very long!

But if you write like this instead to get all records where at least one flag is set

SELECT * FROM Table1
WHERE 1 IN (Flag1, Flag2, Flag3...)

The code is somewhat smaller. It can be hard the first times to understand what is happening but you will get it eventually.

But how to select all records where all flags are set? You can't write

SELECT * FROM Table1
WHERE 0 IN (Flag1, Flag2, Flag3...)

because that will only get you all records where at least one flag is not set.
Here is the perfect opportunity to introduce the NOT operator. This is the way you can get the records where all flags are set.

SELECT * FROM Table1
WHERE NOT 0 IN (Flag1, Flag2, Flag3...)

And this will get you all records where all flags are not set

SELECT * FROM Table1
WHERE NOT 1 IN (Flag1, Flag2, Flag3...)

Print | posted on Friday, September 28, 2007 12:24 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# re: Save some time and key-typing

Good article. Thanks, this will definitely help sometime.
--Suresh
10/5/2007 4:41 PM | Suresh Beniwal
Gravatar

# re: Save some time and key-typing

Nice Article. Thanks
8/9/2010 9:14 AM | dineshrajan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET