SQL Queryquery to check if 100+ (or Dynamicdynamic) columns in table isare null
LetLet’s assume a table has more than 100 of columns &and I want to display all the rows when all of its columns (Col1, Col2, ......, ColN) are nulls except for the column(s) I mentioned (PK).
This is just a sample table I created to explainillustrate this question.
Sample TableSample Table with data
+----+------+------+------+------+
| PK | Col1 | Col2 | Col3 | Col4 |
+----+------+------+------+------+
| 1 | 45 | 12 | 171 | 15 |
| 2 | 13 | NULL | 15 | NULL |
| 3 | 17 | 14 | 130 | NULL |
| 4 | 10 | 11 | 110 | 12 |
| 5 | 45 | 15 | 185 | 54 |
| 6 | NULL | NULL | NULL | NULL |
| 7 | 16 | 48 | NULL | 25 |
| 8 | NULL | NULL | NULL | NULL |
| 9 | 13 | 45 | 41 | 64 |
| 10 | NULL | NULL | NULL | NULL |
| 11 | 21 | 12 | 165 | 56 |
| 12 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+
Image: https://i.sstatic.net/z17GK.png
| PK | Col1 | Col2 | Col3 | Col4 |
|---|---|---|---|---|
| 1 | 45 | 12 | 171 | 15 |
| 2 | 13 | NULL | 15 | NULL |
| 3 | 17 | 14 | 130 | NULL |
| 4 | 10 | 11 | 110 | 12 |
| 5 | 45 | 15 | 185 | 54 |
| 6 | NULL | NULL | NULL | NULL |
| 7 | 16 | 48 | NULL | 25 |
| 8 | NULL | NULL | NULL | NULL |
| 9 | 13 | 45 | 41 | 64 |
| 10 | NULL | NULL | NULL | NULL |
| 11 | 21 | 12 | 165 | 56 |
| 12 | NULL | NULL | NULL | NULL |
In this case, the rows 6, 8, 10 and 12 rows (Column name: PK) are the result I want to see since all of the columns (Col1 to Col4) are null except for the PK column.
Desired OutputDesired Output
+----+------+------+------+------+
| PK | Col1 | Col2 | Col3 | Col4 |
+----+------+------+------+------+
| 6 | NULL | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+
Image: https://i.sstatic.net/prwFo.png
| PK | Col1 | Col2 | Col3 | Col4 |
|---|---|---|---|---|
| 6 | NULL | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL | NULL |
SQL Query to check if 100+ (or Dynamic) columns in table is null
Let assume a table has more than 100 of columns & I want to display all the rows when all of its columns (Col1, Col2, ..., ColN) are nulls except for the column(s) I mentioned (PK).
This is just a sample table I created to explain this question.
Sample Table with data
+----+------+------+------+------+
| PK | Col1 | Col2 | Col3 | Col4 |
+----+------+------+------+------+
| 1 | 45 | 12 | 171 | 15 |
| 2 | 13 | NULL | 15 | NULL |
| 3 | 17 | 14 | 130 | NULL |
| 4 | 10 | 11 | 110 | 12 |
| 5 | 45 | 15 | 185 | 54 |
| 6 | NULL | NULL | NULL | NULL |
| 7 | 16 | 48 | NULL | 25 |
| 8 | NULL | NULL | NULL | NULL |
| 9 | 13 | 45 | 41 | 64 |
| 10 | NULL | NULL | NULL | NULL |
| 11 | 21 | 12 | 165 | 56 |
| 12 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+
Image: https://i.sstatic.net/z17GK.png
In this case, 6, 8, 10 and 12 rows (Column name: PK) are the result I want to see since all of the columns (Col1 to Col4) are null except for the PK column.
Desired Output
+----+------+------+------+------+
| PK | Col1 | Col2 | Col3 | Col4 |
+----+------+------+------+------+
| 6 | NULL | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+
SQL query to check if 100+ (or dynamic) columns in table are null
Let’s assume a table has more than 100 columns and I want to display all the rows when all of its columns (Col1, Col2, ..., ColN) are nulls except for the column(s) I mentioned (PK).
This is just a sample table I created to illustrate this question.
Sample Table with data
| PK | Col1 | Col2 | Col3 | Col4 |
|---|---|---|---|---|
| 1 | 45 | 12 | 171 | 15 |
| 2 | 13 | NULL | 15 | NULL |
| 3 | 17 | 14 | 130 | NULL |
| 4 | 10 | 11 | 110 | 12 |
| 5 | 45 | 15 | 185 | 54 |
| 6 | NULL | NULL | NULL | NULL |
| 7 | 16 | 48 | NULL | 25 |
| 8 | NULL | NULL | NULL | NULL |
| 9 | 13 | 45 | 41 | 64 |
| 10 | NULL | NULL | NULL | NULL |
| 11 | 21 | 12 | 165 | 56 |
| 12 | NULL | NULL | NULL | NULL |
In this case, the rows 6, 8, 10 and 12 (Column name: PK) are the result I want to see since all of the columns (Col1 to Col4) are null except for the PK column.
| PK | Col1 | Col2 | Col3 | Col4 |
|---|---|---|---|---|
| 6 | NULL | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL | NULL |
╔════╦══════╦══════╦══════╦══════╗+----+------+------+------+------+
║| PK ║| Col1 ║| Col2 ║| Col3 ║| Col4 ║|
╠════╬══════╬══════╬══════╬══════╣+----+------+------+------+------+
║| 1 ║| 45 ║| 12 ║| 171 ║| 15 ║|
║| 2 ║| 13 ║| NULL ║| 15 ║| NULL ║|
║| 3 ║| 17 ║| 14 ║| 130 ║| NULL ║|
║| 4 ║| 10 ║| 11 ║| 110 ║| 12 ║|
║| 5 ║| 45 ║| 15 ║| 185 ║| 54 ║|
║| 6 ║| NULL ║| NULL ║| NULL ║| NULL ║|
║| 7 ║| 16 ║| 48 ║| NULL ║| 25 ║|
║| 8 ║| NULL ║| NULL ║| NULL ║| NULL ║|
║| 9 ║| 13 ║| 45 ║| 41 ║| 64 ║|
║| 10 ║| NULL ║| NULL ║| NULL ║| NULL ║|
║| 11 ║| 21 ║| 12 ║| 165 ║| 56 ║|
║| 12 ║| NULL ║| NULL ║| NULL ║| NULL ║|
╚════╩══════╩══════╩══════╩══════╝+----+------+------+------+------+
╔════╦══════╦══════╦══════╦══════╗+----+------+------+------+------+
║| PK ║| Col1 ║| Col2 ║| Col3 ║| Col4 ║|
╠════╬══════╬══════╬══════╬══════╣+----+------+------+------+------+
║| 6 ║| NULL ║| NULL ║| NULL ║| NULL ║|
║| 8 ║| NULL ║| NULL ║| NULL ║| NULL ║|
║| 10 ║| NULL ║| NULL ║| NULL ║| NULL ║|
║| 12 ║| NULL ║| NULL ║| NULL ║| NULL ║|
╚════╩══════╩══════╩══════╩══════╝+----+------+------+------+------+
╔════╦══════╦══════╦══════╦══════╗
║ PK ║ Col1 ║ Col2 ║ Col3 ║ Col4 ║
╠════╬══════╬══════╬══════╬══════╣
║ 1 ║ 45 ║ 12 ║ 171 ║ 15 ║
║ 2 ║ 13 ║ NULL ║ 15 ║ NULL ║
║ 3 ║ 17 ║ 14 ║ 130 ║ NULL ║
║ 4 ║ 10 ║ 11 ║ 110 ║ 12 ║
║ 5 ║ 45 ║ 15 ║ 185 ║ 54 ║
║ 6 ║ NULL ║ NULL ║ NULL ║ NULL ║
║ 7 ║ 16 ║ 48 ║ NULL ║ 25 ║
║ 8 ║ NULL ║ NULL ║ NULL ║ NULL ║
║ 9 ║ 13 ║ 45 ║ 41 ║ 64 ║
║ 10 ║ NULL ║ NULL ║ NULL ║ NULL ║
║ 11 ║ 21 ║ 12 ║ 165 ║ 56 ║
║ 12 ║ NULL ║ NULL ║ NULL ║ NULL ║
╚════╩══════╩══════╩══════╩══════╝
╔════╦══════╦══════╦══════╦══════╗
║ PK ║ Col1 ║ Col2 ║ Col3 ║ Col4 ║
╠════╬══════╬══════╬══════╬══════╣
║ 6 ║ NULL ║ NULL ║ NULL ║ NULL ║
║ 8 ║ NULL ║ NULL ║ NULL ║ NULL ║
║ 10 ║ NULL ║ NULL ║ NULL ║ NULL ║
║ 12 ║ NULL ║ NULL ║ NULL ║ NULL ║
╚════╩══════╩══════╩══════╩══════╝
+----+------+------+------+------+
| PK | Col1 | Col2 | Col3 | Col4 |
+----+------+------+------+------+
| 1 | 45 | 12 | 171 | 15 |
| 2 | 13 | NULL | 15 | NULL |
| 3 | 17 | 14 | 130 | NULL |
| 4 | 10 | 11 | 110 | 12 |
| 5 | 45 | 15 | 185 | 54 |
| 6 | NULL | NULL | NULL | NULL |
| 7 | 16 | 48 | NULL | 25 |
| 8 | NULL | NULL | NULL | NULL |
| 9 | 13 | 45 | 41 | 64 |
| 10 | NULL | NULL | NULL | NULL |
| 11 | 21 | 12 | 165 | 56 |
| 12 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+
+----+------+------+------+------+
| PK | Col1 | Col2 | Col3 | Col4 |
+----+------+------+------+------+
| 6 | NULL | NULL | NULL | NULL |
| 8 | NULL | NULL | NULL | NULL |
| 10 | NULL | NULL | NULL | NULL |
| 12 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+