I new to SQL, I am having some difficulty solving this problem.
I want to write the select query which returns all those rows which have the null value in it.
I have more than 50 columns on the table and can be added some extra column it and because of that I getting difficult to write where condition.
AFAIK we can use is null but I don't want to repeat it for that much columns.
Please help me solve it. Let me know if any additional information required.
You can try this dynamic SQL query. This query will return row if any column contain null value.
DECLARE @tb NVARCHAR(255) = N'dbo.[tablename]';
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb
+ ' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb)
AND [is_nullable]=1;
EXEC sp_executesql @sql;
SELECT @Variable = @Variable + ... FROM is a documented antipattern and should be avoided; it relies on the data engine processing your in a row by row order, which there is no guarantee of. Instead use string aggregation to achieve the same results. In recent versions of SQL Server that would be by using STRING_AGG, however, in older versions you'll need to use FOR XML PATH (and STUFF) to achieve the same results.A simple way (in that it doesn't require listing the columns explicitly) is
WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema-instance' as ns)
SELECT *
FROM t1 x
WHERE (SELECT x.*
FOR xml path('row'), elements xsinil, type
).exist('(//*/@ns:nil)') = 1
Though it does have the completely unneeded overhead of converting to XML. http://rextester.com/VTV64079
Try this: replace table_name with your table
DECLARE @COLNAME VARCHAR(MAX),@QUERY VARCHAR(MAX),@TABLE VARCHAR(MAX)
DECLARE @TEST TABLE(ID INT)
DECLARE @NULLS TABLE([TABLE] VARCHAR(MAX), [COLUMN] VARCHAR(MAX),[HAS_NULL] VARCHAR(MAX))
SET @TABLE='TABLE_NAME'
DECLARE C CURSOR FOR
SELECT NAME FROM SYS.ALL_COLUMNS WHERE OBJECT_ID=(SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME=@TABLE)
OPEN C
FETCH NEXT FROM C INTO @COLNAME
WHILE @@FETCH_STATUS=0
BEGIN
SET @QUERY='SELECT COUNT(1) FROM '+@TABLE+' WHERE '+@COLNAME+' IS NULL'
INSERT INTO @TEST EXEC (@QUERY)
IF(SELECT TOP 1 ID FROM @TEST)>0
BEGIN
INSERT INTO @NULLS VALUES ( @TABLE,@COLNAME,'NULL VALUES')
END
ELSE
BEGIN
INSERT INTO @NULLS VALUES ( @TABLE,@COLNAME,'NO NULL VALUES')
END
DELETE FROM @TEST
FETCH NEXT FROM C INTO @COLNAME
END
CLOSE C
DEALLOCATE C
SELECT * FROM @NULLS
Aside from the whys and why-was-design-so-neglected-until-now questions, there are a number of alternatives.
Sadly, unless you can guarantee some of those 50+ columns might be empty, you are either going to use an complicated predicate or tables scans.
Since this is clearly not going to be able to use a predicate cleanly, use a clean table scan to test the values and run an inner join that match the boolean statement.
WITH CTE AS (SELECT IIF (col1 IS NULL, 1, (IIF(col2 IS NULL, 1, etc) ) ) as FOUND -- if clears, return 0
FROM TABLEA AS A)
SELECT A.*
FROM CTE A
INNER JOIN TABLEA AS B ON A.ID = B.ID
WHERE A.FOUND = 1
This way SQL Server can treat it relationally and your predicate is clean. The IIF statement just compares one rows values with the check for NULL value (i would test alternate ways of declaring this to learn how the functions can vary), which significantly less complicated than trying to use the predicate in a complicated 50! Logical nightmare.
The key advantage to the inline function is that they are likely to be optimized sonce they were designed to deal cursively with data, whereas your predicate is for relational comparisons (SARGs)
Do you want to find all the specify column is null?
;WITH tb(ID,col1,col2,col3,col4,col5,col6,col7,col8,col9)AS(
SELECT 1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 2,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,NULL UNION
SELECT 3,1,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
)
SELECT * FROM tb
WHERE COALESCE(col1,col2,col3,col3,col5,col6,col7,col8,col9) IS NULL
will return id=4
ID col1 col2 col3 col4 col5 col6 col7 col8 col9 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 4 NULL NULL NULL NULL NULL NULL NULL NULL NULL
To check which column is NULL with dynamic satement:
CREATE TABLE tb(ID INT ,col1 INT ,col2 INT ,col3 INT ,col4 INT ,col5 INT ,col6 INT ,col7 INT ,col8 INT ,col9 INT )
INSERT INTO tb
SELECT 1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 2,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,NULL UNION
SELECT 3,1,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL UNION
SELECT 4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
DECLARE @cols NVARCHAR(max),@sql NVARCHAR(max)
SELECT @cols=ISNULL(@cols+',','')+'('''+c.name+ ''',['+c.name+'])' FROM sys.all_columns AS c WHERE OBJECT_NAME(c.object_id)='tb'
PRINT @cols
SET @sql='
SELECT ID,c.col_title,CASE WHEN c.col_value IS NULL THEN ''NULL NULL'' ELSE ''HAS VALUE'' END AS COMMENT FROM dbo.tb
CROSS APPLY(VALUES'+@cols+') c(col_title,col_value)'
PRINT @sql
EXEC (@sql)
ID col_title COMMENT ----------- --------- --------- 1 col1 HAS VALUE 1 col2 NULL NULL 1 col3 NULL NULL 1 col4 NULL NULL 1 col5 NULL NULL 1 col6 NULL NULL 1 col7 NULL NULL 1 col8 NULL NULL 1 col9 NULL NULL 1 ID HAS VALUE 2 col1 NULL NULL 2 col2 NULL NULL 2 col3 NULL NULL 2 col4 NULL NULL 2 col5 NULL NULL 2 col6 NULL NULL 2 col7 HAS VALUE 2 col8 NULL NULL 2 col9 NULL NULL 2 ID HAS VALUE 3 col1 HAS VALUE 3 col2 HAS VALUE 3 col3 NULL NULL 3 col4 NULL NULL 3 col5 NULL NULL 3 col6 NULL NULL 3 col7 NULL NULL 3 col8 NULL NULL 3 col9 NULL NULL 3 ID HAS VALUE 4 col1 NULL NULL 4 col2 NULL NULL 4 col3 NULL NULL 4 col4 NULL NULL 4 col5 NULL NULL 4 col6 NULL NULL 4 col7 NULL NULL 4 col8 NULL NULL 4 col9 NULL NULL 4 ID HAS VALUE
