How to count NULLS without using IS NULL in a WHERE clause

This came up the other day, someone wanted to know the percentage of NULL values in a column

Then I said "I bet you I can run that query without using a NULL in the WHERE clause, as a matter of fact, I can run that query without a WHERE clause at all!!"

The person then wanted to know more, so you know what that means.. it becomes a blog post  :-)

BTW, the PostgreSQL version of this blog post can be found here:  A quick and easy way to count the percentage of nulls without a where clause in PostgreSQL


To get started, first create this table and verify you have 9 rows

CREATE TABLE foo(bar int)
INSERT foo values(1),(null),(2),(3),(4),
 (null),(5),(6),(7)

SELECT * FROM foo

Here is what the output should be

bar
1
NULL
2
3
4
NULL
5
6
7

To get the NULL values and NON NULL values, you can do something like this

SELECT COUNT(*) as CountAll FROM foo WHERE bar IS NOT NULL
SELECT COUNT(*) as CountAll FROM foo WHERE bar IS  NULL

However, there is another way

Did you know that COUNT behaves differently if you use a column name compared to when you use *

Take a look

SELECT COUNT(*) as CountAll, 
  COUNT(bar) as CountColumn
FROM foo

If you ran that query, the result is the following

CountAll    CountColumn
----------- -----------
9           7

Warning: Null value is eliminated by an aggregate or other SET operation.


And did you notice the warning? That came from the count against the column

Let's see what Books On Line has to say


COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group, and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group, and returns the number of unique, nonnull values.

This is indeed documented behavior

So now, lets change our query to return the percentage of non null values in the column

SELECT COUNT(*) as CountAll, 
  COUNT(bar) as CountColumn, 
  (COUNT(bar)*1.0/COUNT(*))*100 as PercentageOfNonNullValues 
FROM foo

Here is the output

CountAll    CountColumn percentageOfNonNullValues
----------- ----------- ---------------------------------------
9           7           77.777777777700

I just want to point out one thing,  the reason I have this * 1.0 in the query

(COUNT(bar)*1.0/COUNT(*))*100

I am doing * 1.0 here because count returns an integer, so you will end up with integer math and the PercentageOfNonNullValues would be 0 instead of 77.7777...


That's it for this short post.. hopefully you knew this, if not, then you know it now  :-)

The Strange Case OF Nulls And Case

Okay it is not Dr. Jekyll and Mr. Hyde but I had to come up with a title. This was a question someone asked yesterday in the sql programming group


http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/126735827b7ae667#

This person had a case statement like this

SELECT
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS

This still returned NULLS. Can you spot the flaw? there is no else statement, if there is a value which is not sugar, salt or pepper then a NULL will be returned. let's take a look.

Create this table

CREATE TABLE #INV_ITEMS (COL1 varchar(23))
INSERT #INV_ITEMS VALUES('SUGAR')
INSERT #INV_ITEMS VALUES('SALT')
INSERT #INV_ITEMS VALUES('PEPPER')
INSERT #INV_ITEMS VALUES('WASABI')

Now run this
SELECT
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS

Output
--------
SGR
SAL
PEP
NULL


So we get a NULL, but which row is that? We just add the column to find out


SELECT Col1,
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
END AS items
FROM #INV_ITEMS


Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI NULL

Aha, it is the wasabi.

Let's try again by including an ELSE


SELECT Col1,
CASE
WHEN COL1 IS NULL THEN 'UNK'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
ELSE 'UNK'
END AS items
FROM #INV_ITEMS

Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI UNK

There we go, it is correct now.

Now let's make it more interesting by inserting a blank, a space and a NULL


INSERT #INV_ITEMS VALUES('')
INSERT #INV_ITEMS VALUES(' ')
INSERT #INV_ITEMS VALUES(NULL)

We can use a combination of NULLIF and RTRIM to filter out blanks, spaces and NULLS

SELECT Col1,
CASE
WHEN NULLIF(RTRIM(COL1),'') IS NULL THEN 'NullOrBlank'
WHEN COL1 = 'SUGAR' THEN 'SGR'
WHEN COL1 = 'SALT' THEN 'SAL'
WHEN COL1 = 'PEPPER' THEN 'PEP'
ELSE 'UNK'
END AS items
FROM #INV_ITEMS

Output
--------
SUGAR SGR
SALT SAL
PEPPER PEP
WASABI UNK
NullOrBlank
NullOrBlank
NULL NullOrBlank

And there it is

DROP TABLE #INV_ITEMS

Return Null If A Value Is A Certain Value

You need to return NULL only if the value of your data is a certain value. How do you do this?
There are three different ways.

NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'


SELECT NULLIF(@1,'D')


REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)


CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'


SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

Sort Values Ascending But NULLS Last

This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don't want the NULLS at the beginning.
Oracle has this syntax: ORDER BY ColumnName NULLS LAST;
SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.

The 2 approaches with a datetime data type

DECLARE @Temp table(Col datetime)
INSERT INTO @Temp VALUES(getdate())
INSERT INTO @Temp VALUES('2007-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2005-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2006-10-19 09:54:03.730')
INSERT INTO @Temp VALUES('2004-10-19 09:54:03.730')
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)

SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'9999-12-31 23:59:59.997')

SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

The 2 approaches with an integer data type

DECLARE @Temp table(Col int)
INSERT INTO @Temp VALUES(1)
INSERT INTO @Temp VALUES(555)
INSERT INTO @Temp VALUES(444)
INSERT INTO @Temp VALUES(333)
INSERT INTO @Temp VALUES(5656565)
INSERT INTO @Temp VALUES(3)
INSERT INTO @Temp VALUES(NULL)
INSERT INTO @Temp VALUES(NULL)

SELECT *
FROM @Temp
ORDER BY COALESCE(Col,'2147483647')

SELECT *
FROM @Temp
ORDER BY CASE WHEN Col Is NULL Then 1 Else 0 End, Col

Summer SQL Teaser #4 Nulls and Counts

First create this table

CREATE TABLE Teaser (ID int)
INSERT Teaser VALUES(1)
INSERT Teaser VALUES(2)
INSERT Teaser VALUES(1)
INSERT Teaser VALUES(2)
INSERT Teaser VALUES(NULL)


Without running this try to figure out what the result will be

SELECT COUNT(*),
COUNT(ID),
COUNT(DISTINCT ID)
FROM Teaser


For some more NULL fun you can read NULL Trouble In SQL Server Land

Use A Combination OF NULLIF and COALESCE TO Display A Custom Value

Let's you inherit a table with a column that can contain blanks, empty strings or NULL values. I am saying inherit because surely you would have a constraint on the column that wouldn't allow those values to begin with right? For all the values that are '',' ' or NULL you want to display 'N/A'.
What is the best way to do this? You can Use CASE and test for the values or you can use COALESCE with NULLIF which is much shorter. A lot of people don't know that you can stack these functions so that it makes your life so much easier.
Let's get started

CREATE TABLE Blah (SomeCol VARCHAR(33))

INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')

--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah


--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah


Output for both queries
-----------------------
N/A
N/A
N/A
A
B B

NULL Trouble In SQL Server Land

I am seeing a lot of searches for SQL + Nulls from this site so I decided to blog about it
Before I start I would like to point out that all the code will behave this way if ANSI_NULLS is set to on ,not to off

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)

--We get back value 1 here
SELECT * FROM testjoin WHERE ID IN(SELECT ID FROM testnulls)

--Nothing is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)

--Value 3 is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)


--value 3 is returned
SELECT * FROM testjoin j
WHERE NOT EXISTS (SELECT n.ID
FROM testnulls n
WHERE n.ID = j.ID)


--value 3 is returned
SELECT j.* FROM testjoin j
LEFT OUTER JOIN testnulls n ON n.ID = j.ID
WHERE n.ID IS NULL



--a count of 3 is returned
SELECT COUNT(*) FROM testnulls

-- a count of 2 is returned, the count ignores NULL values
SELECT COUNT(id) FROM testnulls

--By using coalesce the count is also 3
SELECT COUNT(COALESCE(id,0)) FROM testnulls

--all 3 rows are returned
SELECT * FROM testnulls

-- 1 row is returned
SELECT * FROM testnulls
WHERE ID = 1

-- only 1 row is returned the row with the NULL value is ignored
SELECT * FROM testnulls
WHERE ID <> 1


-- Now both rows that are not 1 are returned
SELECT * FROM testnulls
WHERE ID <>1
OR ID IS NULL

-- Now both rows that are not 1 are returned also
SELECT * FROM testnulls
WHERE COALESCE(ID,0) <> 1

Some more NULL Fun
You can''t compare NULL with anything
Since both values are unknown even a comparison with another NULL is unknown

DECLARE @v INT
DECLARE @v2 INT

SELECT @v =NULL,@v2 = NULL
IF @v = @v2
SELECT 'yes'
ELSE
SELECT 'No'


Be carefull with forgetting to initialize parameters while building string

DECLARE @SQL VARCHAR(500)
DECLARE @Id INT

SELECT @SQL = 'SELECT * FROM testnulls
WHERE ID =' + CONVERT(VARCHAR,@Id)

SELECT @SQL