Where All Is Not Null

WHERE all IS NOT NULL

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..

how to select rows with no null values (in any column) in SQL?

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.

NULL values not filtered out with WHERE statement

use this for only want null recode

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NULL
or
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') = ''

if you get not null value then use

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') <> ''
or 
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NOT NULL

sql query to exclude not null columns

You could check the length of CONCAT this way (but it treats empty strings and NULLs the same):

SELECT * FROM dbo.emp_details
  WHERE LEN(CONCAT(Sal,Comm,...70 more columns)) > 0;

You could build that CONCAT list this with dynamic SQL:

DECLARE @sql nvarchar(max), @cols nvarchar(max);

SELECT @cols = STUFF((SELECT N',' + name FROM sys.columns
  WHERE [object_id] = OBJECT_ID(N'dbo.emp_details')
    AND name NOT IN (N'empno', N'enname')
    ORDER BY column_id
  FOR XML PATH(''), TYPE).value(N'./text()[1]',N'nvarchar(max)'),1,1,N'');
  
SET @sql = N'SELECT empno, enname, ' + @cols
  + ' FROM dbo.emp_details WHERE LEN(CONCAT(' + @cols + ')) > 0;';
  
EXEC sys.sp_executesql @sql;

• Example db<>fiddle

How to simplify IS NOT NULL for multiple columns when using QUERY function in Google Sheets?

join them. lets say your range is A:F and you want to check B:F for is not null, then try:

=ARRAYFORMULA(QUERY({A:F, TRIM(FLATTEN(QUERY(TRANSPOSE(B:F),,9^9)))}, 
 "select Col1,Col2,Col3,Col4,Col5,Col6
  where Col7 is not null", ))

Insert into without null values

Try with this insert:

INSERT INTO t.tb_c (itm, des)
SELECT itm, des
        FROM erp.tb_d
        WHERE des IS NOT NULL AND itm IS NOT NULL;

SQL query to find columns having at least one non null value

I would not recommend using count(distinct) because it incurs overhead for removing duplicate values. You can just use count().

You can construct the query for counts using a query like this:

select count(col1) as col1_cnt, count(col2) as col2_cnt, . . .
from t;

If you have a list of columns you can do this as dynamic SQL. Something like this:

declare @sql nvarchar(max);

select @sql = concat('select ',
                     string_agg(concat('count(', quotename(s.value), ') as cnt_', s.value),
                      ' from t'
                    )
from string_split(@list) s;

exec sp_executesql(@sql);

This might not quite work if your columns have special characters in them, but it illustrates the idea.

Checking if all values for user_id IS NOT NULL

Use conditional aggregation to explicitly COUNT the rows where the column has the value NULL:

SELECT GroupedColumn,
       COUNT(CASE WHEN NullableColumn IS NULL THEN 1 END) AS NullCount
FROM dbo.YourTable
GROUP BY GroupedColumn;

If you want to just have a 1 or 0 just wrap the count in a CASE expression:

CASE COUNT(CASE WHEN NullableColumn IS NULL THEN 1 END) WHEN 0 THEN 1 ELSE 0 END

---

Related Topics