CODEWITHSUNDEEP
sqlite
kalki
kalki

Reputation: 67

check for null values in all columns of a table using SQLite

I have a table with more than 15 columns. 2 of of them are of the type varchar, and most of them of type int and float.

I am new to SQL and am trying to figure out a way by which I can check if any of the columns have a NULL value in it.

Had there been just 4 or 5 columns I could have checked them individually with

SELECT COUNT(*) FROM table_name WHERE col1 IS NULL OR col2 IS NULL OR col3 IS NULL ...

But is there any efficient way to do this on a lot of columns in SQLite specifically?

I have referred to other questions regarding this here but I cannot use xml or store anything. Also I am using SQLite and can only run a query.

Upvotes: 4

Views: 7281

Answers (2)

Chaouki
Chaouki

Reputation: 19

Try This :

PRAGMA table_info(Table_Name);

This will return information about the columns in your table, including whether they allow NULL values. Look for the not-null column in the result. If it has a value of 0 for any column, it means that column allows NULL values. If it has a value of 1 for all columns, it means that none of the columns allow NULL values.

Upvotes: 1

forpas
forpas

Reputation: 164154

There is no way (that I know of) to check all columns if they contain null without explicitly listing all the column names.

Your query is the proper way to do it.

If you want to shorten (not significantly) the code you could use these alternatives:

SELECT COUNT(*) FROM table_name WHERE col1 + col2 + col3 IS NULL;

or:

SELECT COUNT(*) FROM table_name WHERE col1 || col2 || col3 IS NULL;

or:

SELECT COUNT(*) FROM table_name WHERE MAX(col1, col2, col3) IS NULL;

The above queries work, for any data type of the columns, because if there is even only 1 column equal to null then addition, concatenation and the scalar function MAX() (and MIN()) all return null.

See the demo.

Upvotes: 5

Related Questions