Is There a Quick Way to Check If Any Column Is Null

How do I check if a column is empty or null in MySQL?

This will select all rows where some_col is NULL or '' (empty string)

SELECT * FROM table WHERE some_col IS NULL OR some_col = '';

Check if columns are NULL or contains NULL in table. - MS SQL Server

we can check with the help of IN like

...WHERE NULL IN (Column_2, Column_3)

from your comment Well the multiple column will be Column_3, Column_2 in format
might be this is helpful for you

select * from (select Column_3, Column_2 from @temp where null in (Column_3, Column_2)) as Result

Check if any column is NOT NULL

You can use COALESCE for this. COALESCE returns the first non-null value, if any. This will likely not perform any better, but is much more readable.

Example:

where coalesce(column_a, column_b, column_c, column_x) is not null 

Depending on the cardinality of your data, you may be able to add indexes to help performance.

Another possibility is to use persisted computed column that tells you whether all four columns are NULL or not.

Optimize way of Null checking for multiple columns

You can check the row for not NULL values in three ways:

1. COALESCE(col1, col2, col3) IS NOT NULL
2. col1 IS NOT NULL OR col2 IS NOT NULL OR col3 IS NOT NULL
3. ISNULL(col1, ISNULL(col2, ISNULL(col3, NULL))) IS NOT NULL

You can use the Microsoft SQL Server Management Studio to compare multiple querys.

result of comparison:

• COALESCE vs. IS NOT NULL: 57% to 43%
• COALESCE vs. ISNULL: 56% to 44%
• IS NOT NULL vs. ISNULL: 49% to 51%

So using IS NOT NULL is the fastest way to check if a row has a column without a NULL value. In case of readability the COALESCE can be much shorter than a IS NOT NULL or ISNULL comparison. You can decide between readability and speed.

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

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.

Efficient way to find columns that contain ANY null values

Spark's SQL function any can check if any value of a column meets a condition.

from pyspark.sql import functions as F

data = [[1,2,3],[None, 5, 6], [7, None, 9]]
df = spark.createDataFrame(data, schema=["col1", "col2", "col3"])

cols = [f"any({col} is null) as {col}_contains_null" for col in df.columns]
df.selectExpr(cols).show()

Output:

+------------------+------------------+------------------+
|col1_contains_null|col2_contains_null|col3_contains_null|
+------------------+------------------+------------------+
|              true|              true|             false|
+------------------+------------------+------------------+

Select columns with NULL values only

Here is the sql 2005 or later version: Replace ADDR_Address with your tablename.

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'ADDR_Address'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

---

Related Topics