Are you saying that you want the list of columns returned to potentially change every time? If so, why? Answer from theseyeahthese on reddit.com
Reddit
reddit.com › r/sql › select only columns from table where column doesn't have even one null value?
r/SQL on Reddit: Select only columns from table where column doesn't have even one null value?
May 29, 2023 -
Is there a way to select only the columns from a particular table, where no row in the table will have null value for that column?
Top answer 1 of 9
9
that's a request that is ~not~ something you would do in SQL SELECT a, b, c, d FROM tbl WHERE ... you see, the problem is, you won't know that a column has no nulls in it until you've returned the entire table then how do you conditionally decide that you want to return a, b, and d but not c? you've already returned all of c to discover that it contains a null, so how do you remove it from the SELECT list? what is your actual use case here?
2 of 9
6
Are you saying that you want the list of columns returned to potentially change every time? If so, why?
Top answer 1 of 9
32
You'll have to explicitly state the condition on each column, so e.g.
SELECT *
FROM schedule
WHERE id IS NOT NULL
AND foo IS NOT NULL
AND bar IS NOT NULL; -- and so on..
2 of 9
12
You need to get a list of the columns of your table, by looking at the information_schema database.
Let's suppose that your database is called mydata and your table in question is named mytable. You can get the list of the table's nullable columns by issuing the following statement:
SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'mydata'
AND `TABLE_NAME` = 'mytable'
AND `IS_NULLABLE` = 'YES'
Our final query will look like this:
SELECT * FROM `mydata`.`mytable`
WHERE CONCAT(<list of columns>) IS NOT NULL
All we are missing now is the list of nullable columns, comma-separated. We're going to use the GROUP_CONCAT function to produce the final statement, which we will execute like this:
SET @query = CONCAT(
'SELECT * FROM `mydata`.`mytable` WHERE CONCAT(',
(SELECT GROUP_CONCAT(COLUMN_NAME)
FROM `information_schema`.`COLUMNS`
WHERE `TABLE_SCHEMA` = 'mydata' AND
`TABLE_NAME` = 'mytable'
AND `IS_NULLABLE` = 'YES'),
') IS NOT NULL');
PREPARE stmt_name FROM @query;
EXECUTE stmt_name;
References:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html
Videos
SQLServerCentral
sqlservercentral.com › forums › topic › how-to-select-only-non-null-columns-out-of-100-plus-columns-table
how to select only non null columns out of 100 plus columns table – SQLServerCentral Forums
February 19, 2017 - 'Address' is the name of the table you are interested in. use AdventureWorks go select * from sys.all_columns c join sys.all_objects o on c.object_id=o.object_id where o.name='Address' and c.is_nullable=0 order by column_id ... You can use a CROSS APPLY to adjust the row to make it much easier to analyze values. I can't give you more details than this right now because your initial request is too vague. SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear."
W3Schools
w3schools.com › sql › sql_null_values.asp
SQL NULL Values - IS NULL and IS NOT NULL
We will have to use the IS NULL and IS NOT NULL operators instead. SELECT column_names FROM table_name WHERE column_name IS NULL;
Top answer 1 of 2
6
Let's pick a sample table on my machine:
mysql> show create table weisci_jaws_staging2.users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL DEFAULT '',
`passwd` varchar(32) NOT NULL DEFAULT '',
`user_type` tinyint(4) DEFAULT '2',
`recovery_key` varchar(48) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`url` varchar(255) DEFAULT NULL,
`timezone` varchar(5) DEFAULT NULL,
`language` varchar(5) DEFAULT NULL,
`theme` varchar(24) DEFAULT NULL,
`editor` varchar(24) DEFAULT NULL,
`last_login` datetime DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
`change_passwd` tinyint(1) NOT NULL DEFAULT '1',
`never_expire` tinyint(1) NOT NULL DEFAULT '1',
`bad_passwd_count` smallint(6) DEFAULT '0',
`last_access` bigint(20) DEFAULT '0',
`enabled` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `users_username_idx` (`username`)
) ENGINE=MyISAM AUTO_INCREMENT=160 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
mysql> select count(1) from weisci_jaws_staging2.users;
+----------+
| count(1) |
+----------+
| 117 |
+----------+
1 row in set (0.00 sec)
mysql>
With this table, here are two questions:
- Which columns are nullable ?
- Which columns are not nullable ?
This query will find out for you:
select is_nullable,GROUP_CONCAT(column_name) column_list
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and table_name = 'users'
group by is_nullable;
Check out the result of that query for the table:
mysql> select is_nullable,GROUP_CONCAT(column_name) column_list
-> from information_schema.columns
-> where table_schema = 'weisci_jaws_staging2'
-> and table_name = 'users'
-> group by is_nullable;
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| is_nullable | column_list |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| NO | id,never_expire,change_passwd,enabled,username,passwd |
| YES | recovery_key,last_access,bad_passwd_count,updatetime,createtime,last_login,editor,user_type,language,timezone,url,email,name,theme |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql>
OK, we have two lists. What do we learn from this?
- If you get two lists, no need to inspect the actual table because the table has non-NULL columns by definition.
- If you get one list, then
- If you get only is_nullable='NO', no need to inspect the actual table because the table has non-NULL columns by definition.
- If you get only is_nullable='YES', the actual table would be a little brittle. There would be no PRIMARY KEY, you poor, tormented soul !!! NOW, you have to resort to reading every row from the actual table.
If you are looking for just the non-null columns, then this would be your desired query:
select GROUP_CONCAT(column_name) nonnull_columns
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and table_name = 'users'
and is_nullable = 'NO';
Here is the output of this query:
mysql> select GROUP_CONCAT(column_name) nonnull_columns
-> from information_schema.columns
-> where table_schema = 'weisci_jaws_staging2'
-> and table_name = 'users'
-> and is_nullable = 'NO';
+-------------------------------------------------------+
| nonnull_columns |
+-------------------------------------------------------+
| id,username,passwd,change_passwd,never_expire,enabled |
+-------------------------------------------------------+
1 row in set (0.01 sec)
mysql>
Removing the GROUP_CONCAT, you get this:
mysql> select column_name nonnull_column
-> from information_schema.columns
-> where table_schema = 'weisci_jaws_staging2'
-> and table_name = 'users'
-> and is_nullable = 'NO';
+----------------+
| nonnull_column |
+----------------+
| id |
| username |
| passwd |
| change_passwd |
| never_expire |
| enabled |
+----------------+
6 rows in set (0.01 sec)
mysql>
Give it a Try !!!
NOTE : Please notice that I do not need to read the actual table's data content. That's far more efficient than reading the entire table.
UPDATE 2012-11-15 13:40 EDT
The code from @sensware's answer gives NULL columns. The original question asked for non-NULL columns. I augmented the code to test just my table:
SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
SELECT CONCAT(
'SELECT * FROM ('
, GROUP_CONCAT(
CONCAT(
'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
, 'IF('
, 'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
, 'NULL,'
, QUOTE(COLUMN_NAME)
, ') AS `column` '
, 'FROM `',
REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
REPLACE(TABLE_NAME, '`', '``'), '`'
)
SEPARATOR ' UNION ALL '
)
, ') t WHERE `column` IS NOT NULL'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'weisci_jaws_staging2'
AND TABLE_NAME = 'users';
SELECT @sql\G
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Here is the output:
mysql> SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONCAT(
-> 'SELECT * FROM ('
-> , GROUP_CONCAT(
-> CONCAT(
-> 'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
-> , 'IF('
-> , 'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
-> , 'NULL,'
-> , QUOTE(COLUMN_NAME)
-> , ') AS `column` '
-> , 'FROM `',
-> REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
-> REPLACE(TABLE_NAME, '`', '``'), '`'
-> )
-> SEPARATOR ' UNION ALL '
-> )
-> , ') t WHERE `column` IS NOT NULL'
-> )
-> INTO @sql
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'weisci_jaws_staging2'
-> AND TABLE_NAME = 'users';
Query OK, 1 row affected (0.02 sec)
mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT * FROM (SELECT 'users' AS `table`,IF(COUNT(`id`),NULL,'id') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`username`),NULL,'username') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`passwd`),NULL,'passwd') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`user_type`),NULL,'user_type') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`recovery_key`),NULL,'recovery_key') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`name`),NULL,'name') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`email`),NULL,'email') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`url`),NULL,'url') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`timezone`),NULL,'timezone') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`language`),NULL,'language') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`theme`),NULL,'theme') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`editor`),NULL,'editor') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_login`),NULL,'last_login') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`createtime`),NULL,'createtime') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`updatetime`),NULL,'updatetime') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`change_passwd`),NULL,'change_passwd') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`never_expire`),NULL,'never_expire') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`bad_passwd_count`),NULL,'bad_passwd_count') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_access`),NULL,'last_access') AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`enabled`),NULL,'enabled') AS `column` FROM `weisci_jaws_staging2`.`users`) t WHERE `column` IS NOT NULL
1 row in set (0.00 sec)
mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> EXECUTE stmt;
+-------+--------+
| table | column |
+-------+--------+
| users | theme |
+-------+--------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql>
This give NULL columns. The original question asked for non-NULL columns. I'll change the code to generated non-NULL. I'll do that by flipping the order of the IF..COUNT:
SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
SELECT CONCAT(
'SELECT * FROM ('
, GROUP_CONCAT(
CONCAT(
'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
, 'IF('
, 'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
, QUOTE(COLUMN_NAME)
, ',NULL'
, ') AS `column` '
, 'FROM `',
REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
REPLACE(TABLE_NAME, '`', '``'), '`'
)
SEPARATOR ' UNION ALL '
)
, ') t WHERE `column` IS NOT NULL'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'weisci_jaws_staging2'
AND TABLE_NAME = 'users';
SELECT @sql\G
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Let's run it now...
mysql> SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONCAT(
-> 'SELECT * FROM ('
-> , GROUP_CONCAT(
-> CONCAT(
-> 'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
-> , 'IF('
-> , 'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
-> , QUOTE(COLUMN_NAME)
-> , ',NULL'
-> , ') AS `column` '
-> , 'FROM `',
-> REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
-> REPLACE(TABLE_NAME, '`', '``'), '`'
-> )
-> SEPARATOR ' UNION ALL '
-> )
-> , ') t WHERE `column` IS NOT NULL'
-> )
-> INTO @sql
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_SCHEMA = 'weisci_jaws_staging2'
-> AND TABLE_NAME = 'users';
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT * FROM (SELECT 'users' AS `table`,IF(COUNT(`id`),'id',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`username`),'username',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`passwd`),'passwd',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`user_type`),'user_type',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`recovery_key`),'recovery_key',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`name`),'name',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`email`),'email',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`url`),'url',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`timezone`),'timezone',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`language`),'language',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`theme`),'theme',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`editor`),'editor',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_login`),'last_login',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`createtime`),'createtime',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`updatetime`),'updatetime',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`change_passwd`),'change_passwd',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`never_expire`),'never_expire',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`bad_passwd_count`),'bad_passwd_count',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`last_access`),'last_access',NULL) AS `column` FROM `weisci_jaws_staging2`.`users` UNION ALL SELECT 'users' AS `table`,IF(COUNT(`enabled`),'enabled',NULL) AS `column` FROM `weisci_jaws_staging2`.`users`) t WHERE `column` IS NOT NULL
1 row in set (0.00 sec)
mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
+-------+------------------+
| table | column |
+-------+------------------+
| users | id |
| users | username |
| users | passwd |
| users | user_type |
| users | recovery_key |
| users | name |
| users | email |
| users | url |
| users | timezone |
| users | language |
| users | editor |
| users | last_login |
| users | createtime |
| users | updatetime |
| users | change_passwd |
| users | never_expire |
| users | bad_passwd_count |
| users | last_access |
| users | enabled |
+-------+------------------+
19 rows in set (0.01 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql>
OK it works now. There is a problem still present. The query requires reading the entire table. My test table only has 117 rows and 20 columns. What about bigger tables with millions of rows or dozens of columns? I am not going to speculate because I know that the code would be orders of magnitude worse.
That's why I recommend my answer
select GROUP_CONCAT(column_name) nonnull_columns
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and table_name = 'users'
and is_nullable = 'NO';
or
select column_name nonnull_column
from information_schema.columns
where table_schema = 'weisci_jaws_staging2'
and table_name = 'users'
and is_nullable = 'NO';
because the actual data content does not have to be inspected.
The augmented code I made should only be used on a table where all column allow NULL values, which is extremely rare.
2 of 2
3
I think this was answered here:
https://stackoverflow.com/questions/12091272/find-all-those-columns-which-have-only-null-values-in-a-mysql-table
Code was copied below:
SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation
SELECT CONCAT(
'SELECT * FROM ('
, GROUP_CONCAT(
CONCAT(
'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
, 'IF('
, 'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
, 'NULL,'
, QUOTE(COLUMN_NAME)
, ') AS `column` '
, 'FROM `', REPLACE(TABLE_NAME, '`', '``'), '`'
)
SEPARATOR ' UNION ALL '
)
, ') t WHERE `column` IS NOT NULL'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE();
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Top answer 1 of 7
10
Have a look as statistics information, it may be useful for you:
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');
PL/SQL procedure successfully completed.
SQL> select num_rows from all_tables where owner='SCOTT' and table_name='EMP';
NUM_ROWS
----------
14
SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns
2 where owner='SCOTT' and table_name='EMP' order by column_id;
COLUMN_NAME N NUM_DISTINCT NUM_NULLS
------------------------------ - ------------ ----------
EMPNO N 14 0
ENAME Y 14 0
JOB Y 5 0
MGR Y 6 1
HIREDATE Y 13 0
SAL Y 12 0
COMM Y 4 10
DEPTNO Y 3 0
8 rows selected.
For example you can check if NUM_NULLS = NUM_ROWS to identify "empty" columns.
Reference: ALL_TAB_COLUMNS, ALL_TABLES.
2 of 7
6
select column_name
from user_tab_columns
where table_name='Table_name' and num_nulls=0;
Here is simple code to get non null columns..
MSSQLTips
mssqltips.com › home › sql where is not null examples
SQL WHERE IS NOT NULL Examples
March 13, 2023 - We would use the following SQL statement: SELECT * FROM MyEmployeeTable WHERE email IS NOT NULL; GO ... This will return all rows from the employees table where the email column IS NOT NULL.
Microsoft Learn
social.msdn.microsoft.com › Forums › SECURITY › en-US › 98fdf8cc-80b9-4951-8d00-70957605a689 › how-to-retreive-only-columns-which-having-at-least-one-not-null-value-in-any-row
How to retreive only columns which having at least one not null ...
DECLARE @DynSQL nvarchar(2048) ,@comma nchar(1) SET @DynSQL ='SELECT' set @comma = ' ' if(exists(select 'x' from ##TableDemo where col1 is not null)) begin set @DynSQL = @DynSQL+@comma+'Col1 '; set @comma = ',' end if(exists(select 'x' from ##TableDemo where col2 is not null)) begin set @DynSQL = @DynSQL+@comma+'Col2 '; set @comma = ',' end if(exists(select 'x' from ##TableDemo where col3 is not null)) begin set @DynSQL = @DynSQL+@comma+'Col3 '; set @comma = ',' end if(exists(select 'x' from ##TableDemo where col4 is not null)) begin set @DynSQL = @DynSQL+@comma+'Col4 '; set @comma = ',' end i
Microsoft
social.msdn.microsoft.com › Forums › sqlserver › en-US › 994bc3d1-0653-42aa-be27-0af70bf02611 › select-all-columns-which-are-not-null
select all columns which are not null | Microsoft Learn
Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/ ... A possible solution is to first unpivot the data to a dataset with three column: server column value · After that you can filter out the null valued rows with a conditional split.
Quora
quora.com › How-can-you-find-all-values-that-are-not-null-using-a-single-query-in-SQL-Server
How to find all values that are not null using a single query in SQL Server - Quora
Answer (1 of 4): We can use the below for single SELECT * FROM user WHERE username IS NOT NULL; We can use the below for multiple SELECT * FROM user WHERE username IS NOT NULL OR email IS NOT NULL OR description IS NOT NULL;
LearnSQL.com
learnsql.com › cookbook › how-to-filter-rows-without-null-in-a-column
How to Filter Rows without NULL in a column | LearnSQL.com
SELECT name, price FROM product WHERE price IS NOT NULL;
Hightouch
hightouch.com › sql-dictionary › sql-is-not-null
SQL IS NOT NULL - Syntax, Use Cases, and Examples | Hightouch
December 29, 2023 - This is useful when you want to ... The syntax for using the IS NOT NULL operator is as follows: SELECT columns FROM table_name WHERE column_name IS NOT NULL;...
Database Guide
database.guide › how-to-select-rows-where-a-column-is-null-in-sql
How to Select Rows Where a Column is NULL in SQL
In SQL we use IS NULL instead of = NULL. Likewise, we use IS NOT NULL instead of <> NULL or != NULL to select those columns that don’t have a NULL value.
Top answer 1 of 16
92
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
2 of 16
23
SELECT cols
FROM table
WHERE cols IS NULL
Top answer 1 of 4
6
You need to explicitly list each column. I would recommend:
select t.*
from t
where col1 is not null and col2 is not null and . . .
Some people might prefer a more concise (but slower) method such as:
where concat(col1, col2, col3, . . . ) is not null
This is not actually a simple way to express this, although you can construct the query using metadata table or a spreadsheet.
2 of 4
2
While I would recommend writing out each column name and refactoring your table as suggested, here's an option using dynamic sql:
SET @sql = NULL;
SELECT CONCAT('SELECT * FROM table1 WHERE ',
GROUP_CONCAT(c.COLUMN_NAME SEPARATOR ' IS NOT NULL AND '),
' IS NOT NULL') INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'table1'
ORDER BY c.ORDINAL_POSITION;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
- DB Fiddle Demo
Snowflake Community
community.snowflake.com › s › question › 0D50Z00009E2oomSAB › how-to-exlude-all-null-columns-in-results
How to exlude all null columns in results?
Join our community of data professionals to learn, connect, share and innovate together
Top answer 1 of 3
5
T-SQL:
SELECT COALESCE(column1, column2) from table
it select the value of the first non-null of this column1 and column2
2 of 3
0
A SQL query is fixed in the columns that are returned. So, you cannot do what you want with a simple select query. You could use dynamic SQL.
However, you can only return the values that you want as a single string:
select name, concat_ws(',', column1, column2, column3, column4, column5, column6)
from t
where . . . ;
That said, the real issue is probably your data structure. in general, you do not want to store such column values in a pivoted form. Instead, include a single value per row. In other words, you want a junction/association table with one row per value and per name.
Then the query would be:
select name, group_concat(value)
from association_table
group by name;
Top answer 1 of 5
3
I would write this like the following in order to use indexes if any exists:
Select *
From TableName
Where ColumnA Is Not NULL And ColumnA <> ''
And ColumnB Is Not NULL And ColumnB <> ''
.......
2 of 5
1
If you don't want to be row-based, you could union together a bunch of individual column evaluations:
SELECT 'ColumnA' AS ColumnName, ColumnA AS ColumnValue
FROM TableName
WHERE NOT ColumnA IS NULL
UNION ALL
SELECT 'ColumnB' AS ColumnName, ColumnB AS ColumnValue
FROM TableName
WHERE NOT ColumnB IS NULL
UNION ALL
-- etc....
Some of the other answers also show more flexible ways of using IsNull to help weed out null vs. empty values...would be good to add that in here as well. You might also want to select key fields from the table to allow you to identify source rows.