1

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.

kalki
  • 37
  • 1
  • 7

1 Answers1

2

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.

forpas
  • 160,666
  • 10
  • 38
  • 76