SQL Conditional Select: Select nulls OR select non-nulls

Question

Original Question:

I am new to SQL server and can't quite find what I am looking for. I'm trying to create this logic in SQL:

IF [column] IS NULL THEN
    ( SELECT [value] as [columnname] )
ELSE
    ( SELECT [column] WHERE [column_value] IS NOT NULL )

Basically, if an entire column is null, I want to select and fill the whole column with a certain value.

Else, if that column is not completely null, I only want to select its non-null values (coalesce?).

What is the best way to do this using SQL Server 2005?

Answer (thanks to Josh and everyone else!)

IF EXISTS(SELECT myColumn
          FROM mytable
          WHERE myColumn IS NOT NULL)
    SELECT myColumn as colName
    FROM myTable
    WHERE myColumn is NOT NULL
ELSE
    SELECT [nullReplacementValue] as colName
    FROM myTable

Earlier clarification for the confused:

EDIT: For example, if I were given an input table as such:

ColA    ColB    ColC
--------------------
A       null    null
B       null    1
C       null    null

If I were running this SELECT on ColB, it should return:

ColB
----
null
null
null

(eventually I'd like to replace "null" with some value but that can come later).

If I were running this SELECT on ColC, it should return:

ColC
----
1

Duplicate of [SQL Server : check if variable is NULL for WHERE clause](http://stackoverflow.com/questions/10284164/sql-server-check-if-variable-is-null-for-where-clause) and dozens more questions like this on SO - you should **SEARCH FIRST** before asking the same question yet again... — marc_s, Apr 23 '12 at 16:55
I've been searching a bunch (my first line got cut off because I started with "hi all" or similar). I didn't realize it'd be in the WHERE clause. Sorry/thanks. — Penryn, Apr 23 '12 at 16:59
I don't believe that post quite answers my question, or at least I don't understand how it does. That post selects different values if an optional input variable is null or not, whereas I am trying to see if an existent entire column is null or not, and then select itself or part of itself. — Penryn, Apr 23 '12 at 17:10
What do you mean by "an existant entire column is null or not"? A field in a row can be null. But are you trying to check against all rows having set the field to null, or just on a row-by-row basis? — Mike Ryan, Apr 23 '12 at 17:21
I am trying to check if the entire column is null, ie: every row value is null. Or basically, the only distinct column value is null. Then I am trying to select individual rows on a row-by-row basis, based on if that column was completely null or not. I hope that makes sense... Thanks for the reply! — Penryn, Apr 23 '12 at 17:23
I have updated my original post with the answer. Thanks so much, all! And @marc_s: sorry for the head>>desks I might have caused. — Penryn, Apr 23 '12 at 17:44

score 1 · Accepted Answer · answered Apr 23 '12 at 17:23

1

If you're simply asking about a way to default values in a known column if all values in the column are NULL then you can use this:

IF EXISTS(SELECT myColumn FROM mytable WHERE myColumn IS NOT NULL)
    SELECT myColumn FROM myTable WHERE myColumn is NOT NULL
ELSE
    UPDATE myTable SET myColumn = myValue

This is checking to see if at least one value in the column you care about is not null. If it is that means you want to select. Else, you'll update all the columns because you didn't return a result when asking for columns that are not null.

answered Apr 23 '12 at 17:23

Josh

2,955
1
19
28

Thanks so much, this did the trick! I modified it since I was looking for something closer to the following: IF EXISTS(SELECT myColumn FROM mytable WHERE myColumn IS NOT NULL) SELECT myColumn as colName FROM myTable WHERE myColumn is NOT NULL ELSE SELECT [nullReplacementValue] as colName FROM myTable Lots of repeated code, and my actual implementation is a lot longer, but it gets the job done! Thanks so much and I'm sorry for all the headbanging and facepalms for not finding this from searching correctly. :/ EDIT: Oh dear, the formatting is all messed up here. I will post in my OP – Penryn Apr 23 '12 at 17:37

score -1 · Answer 2 · answered Apr 23 '12 at 17:56

You first need to understand the four basic commands in SQL. We will use the following person table as an example

Name      Married     Dependants
-------      ----------     ----------------
Bill           True         True
Jane        False
Fran        True         True
Ed           False
Tom        False        True

Select - (which it looks like you already understand) allows you to retrieve data from a table or multiple tables based on your search criteria.

Select Name From Person Where Name = 'Bill'

This statement would return the following

Name      Married     Dependants
-------      ----------     ----------------
Bill           True         True

Insert - allows you to enter new records into the database

INSERT INTO Person (Name, Married, Dependents) VALUES ('Jill', 'True', 'False')

This statement would make the person table look like the table below

Name      Married     Dependants
-------      ----------     ----------------
Bill           True         True
Jane        False
Fran        True         True
Ed           False
Jill            True
Tom        False        True

Delete - Removes records from a table

DELETE FROM People WHERE Name = 'Fran'

This statement would make the Person table look like below

Name      Married     Dependants
-------      ----------     ----------------
Bill           True         True
Jane        False
Ed           False
Jill            True
Tom        False        True

Finally the one you are looking for
***Update - allows you to update your table for any number of records based on your criteria

Until today you left the dependants column null if someone didn't have dependants. Now your boss tells you that people who do not have dependants must have 'False' in the dependants column. You would write the following statement

UPDATE Person SET Dependants = 'False' WHERE Dependants = NULL

Your table would look like the following

Name      Married     Dependants
-------      ----------     ----------------
Bill            True          True
Jane        False         False
Ed           False         False
Jill            True          False
Tom        False          True

Thanks for the reply! This is a good overview, though for this query I am not looking to modify the table, just to select certain values. — Penryn, Apr 23 '12 at 18:16

SQL Conditional Select: Select nulls OR select non-nulls

2 Answers2