Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find All Rows With Null Value(s) in Any Column

Tags:

I'm trying to create a query that will return all the rows that have a null value across all but 1 column. Some rows will have more than one null entry somewhere. There is one column I will want to exclude, because at this moment in time all of the entries are null and it is the only column that is allowed to have null values. I am stuck because I don't know how to include all of the columns in the WHERE.

SELECT * FROM Analytics WHERE * IS NULL 

Alternatively, I can do a count for one column, but the table has about 67 columns.

SELECT COUNT(*) FROM Analytics WHERE P_Id IS NULL 
like image 522
Snake_Plissken Avatar asked Jan 23 '13 20:01

Snake_Plissken


People also ask

How do you find rows in which a particular column contains a null value?

Use the IS NULL operator in a condition with WHERE to find records with NULL in a column. Of course, you can also use any expression instead of a name of a column and check if it returns NULL. Nothing more than the name of a column and the IS NULL operator is needed (in our example, middle_name IS NULL ).

Does Count () include NULL?

COUNT(expression) returns the number of values in expression, which is a table column name or an expression that evaluates to a column of data. COUNT(expression) does not count NULL values. This query returns the number of non-NULL values in the Name column of Sample. Person.

How do you count rows with NULL values?

How to Count SQL NULL values in a column? The COUNT() function is used to obtain the total number of the rows in the result set. When we use this function with the star sign it count all rows from the table regardless of NULL values.


2 Answers

In SQL Server you can borrow the idea from this answer

;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns) SELECT * FROM   Analytics WHERE  (SELECT Analytics.*         FOR xml path('row'), elements xsinil, type         ).value('count(//*[local-name() != "colToIgnore"]/@ns:nil)', 'int') > 0 

SQL Fiddle

Likely constructing a query with 67 columns will be more efficient but it saves some typing or need for dynamic SQL to generate it.

like image 170
Martin Smith Avatar answered Sep 28 '22 13:09

Martin Smith


Depending on which RDBMS you're using, I think your only option (rather than explicitly saying WHERE col1 IS NULL and col2 IS NULL and col3 IS NULL ...) would be to use Dynamic SQL.

For example, if you want to get all the column names from a SQL Server database, you could use something like this to return those names:

SELECT      name FROM      sys.columns WHERE      object_id = OBJECT_ID('DB.Schema.Table') 

You could use FOR XML to create your WHERE clause:

SELECT Name + ' IS NULL AND ' AS [text()] FROM sys.columns c1 WHERE     object_id = OBJECT_ID('DB.Schema.Table') ORDER BY Name FOR XML PATH('') 

Hope this helps get you started.

Good luck.

like image 43
sgeddes Avatar answered Sep 28 '22 13:09

sgeddes