SQL select statement filtering

Question

Ok, so I'm trying to select an amount of rows from a column that holds the value 3, but only if there are no rows containing 10 or 4, if there are rows containing 10 or 4 I only want to show those.

What would be a good syntax to do that? So far I've been attempting a CASE WHEN statement, but I can't seem to figure it out.

Any help would be greatly appreciated.

(My database is in an MS SQL 2008 server)

Answer 1

FYI: Orginal question title was "SQL CASE WHEN NULL - question"

---

CASE WHEN YourColumn IS NULL THEN x ELSE y END

Since there is nothing that compares to NULL and returns true (not even NULL itself), you cant't do

CASE YourColumn WHEN NULL THEN x ELSE y END

only

CASE ISNULL(YourColumn, '') WHEN '' THEN x ELSE y END

but then you lose the ability to differentiate between NULL and the (in this example) empty string.

Answer 2

Use a union all:

select
    // columns
from YourTable
where YourColumn = 3 and not exists (
    select 1 from YourTable where YourColumn = 10 or YourColumn = 4)

union all

select
    // columns
from YourTable
where YourColumn = 10 or YourColumn = 4

Answer 3

Depending on the size of your table and its indexes, it may be more efficient to calculate which values you want before the query

declare @UseThree as bit = 1;
if exists (select 1 from testtable where rowval in (10,4))
set @UseThree = 0;

select COUNT(*) 
from testtable
where (@UseThree = 1 AND rowval=3)
    OR
    (@UseThree = 0 AND rowval in (10,4))

Answer 4

The simplest solution would be to do this in two queries:

SELECT ... FROM YourTable WHERE SomeColumn IN (10,4)

If and only if the above query yields no results, then run the second query:

SELECT ... FROM YourTable WHERE SomeColumn = 3

Running two queries may seem "inelegant" but it has advantages:

• It's easy to code
• It's easy to debug
• It often has better performance than a very complex solution
• It's easy to understand for a programmer who has to maintain the code after you.

Running two queries may seem like it has extra overhead, but also consider that you won't run the second query every time -- only if the first query has an empty result. If you use an expensive single-query solution, remember that it will incur that expense every time.