No. There are ways to code it quicker, but there are no shortcuts like you imply. Taken from an answer I gave on dba.stackexchange:
DECLARE @tb NVARCHAR(255), @sql NVARCHAR(MAX);
SET @tb = N'dbo.[table]';
SET @sql = N'SELECT * FROM ' + @tb + ' WHERE 1 = 0';
SELECT @sql = @sql + N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb);
EXEC sp_executesql @sql;
No. There are ways to code it quicker, but there are no shortcuts like you imply. Taken from an answer I gave on dba.stackexchange:
DECLARE @tb NVARCHAR(255), @sql NVARCHAR(MAX);
SET @tb = N'dbo.[table]';
SET @sql = N'SELECT * FROM ' + @tb + ' WHERE 1 = 0';
SELECT @sql = @sql + N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb);
EXEC sp_executesql @sql;
You can find the column names using something like this:
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = <table_name>
Then, I would write a procedure using this, and that would loop through the entries in your table and the column names.
Source: http://codesnippets.joyent.com/posts/show/337
An extension to @db2's answer with less (read:zero) hand-wrangling:
DECLARE @tb nvarchar(512) = N'dbo.[table]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ N' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + N' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb)
AND is_nullable = 1;
EXEC sys.sp_executesql @sql;
You should list out all the columns as per JNK's comment.
WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL
A somewhat less efficient approach that avoids this is below though.
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT *
FROM YourTable AS T1
WHERE (
SELECT T1.*
FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
).exist('//*/@ns:nil') = 1
(Based on this SO answer)
It looks like you need something like:
IF EXISTS(SELECT TU.Tagged
FROM TopicUser TU
WHERE TU.TopicId = @TopicId
AND TU.UserId = @UserId
AND TU.Tagged IS NOT NULL)
BEGIN
--do stuff
END
Otherwise, you're checking only if records meeting your criteria exist, but those records could have a NULL value in the TU.Tagged column.
Solution 1 : Use IsNULL() Function, When below query return null value IsNULL function replace null value with 0 and if condition treated as False.
IF EXISTS (SELECT IsNULL(TU.Tagged,0) FROM TopicUser TU
WHERE TU.TopicId = @TopicId and TU.UserId = @UserId)
BEGIN
END
Solution 2 : Use (IS NULL OR IS NOT NULL) Property.
IF EXISTS (SELECT TU.Tagged FROM TopicUser TU
WHERE TU.TopicId = @TopicId and TU.UserId = @UserId
AND TU.Tagged IS NOT NULL)
BEGIN
END
This will select all rows where some_col is NULL or '' (empty string)
SELECT * FROM table WHERE some_col IS NULL OR some_col = '';
As defined by the SQL-92 Standard, when comparing two strings of differing widths, the narrower value is right-padded with spaces to make it is same width as the wider value. Therefore, all string values that consist entirely of spaces (including zero spaces) will be deemed to be equal e.g.
'' = ' ' IS TRUE
'' = ' ' IS TRUE
' ' = ' ' IS TRUE
' ' = ' ' IS TRUE
etc
Therefore, this should work regardless of how many spaces make up the some_col value:
SELECT *
FROM T
WHERE some_col IS NULL
OR some_col = ' ';
or more succinctly:
SELECT *
FROM T
WHERE NULLIF(some_col, ' ') IS NULL;
You can use COALESCE for this. COALESCE returns the first non-null value, if any. This will likely not perform any better, but is much more readable.
Example:
where coalesce(column_a, column_b, column_c, column_x) is not null
Depending on the cardinality of your data, you may be able to add indexes to help performance.
Another possibility is to use persisted computed column that tells you whether all four columns are NULL or not.
One way to attack this might be to add an additional bit column that keeps track of whether there are any values or not.
Pros
- Can be implemented with triggers so you don't need to change the rest of your code
- Doesn't require scanning the other columns
- That column can be indexed
Cons
- Your data would be de-normalized
- More complicated / more maintenance
- More storage space for the additional column
Whether the pros outweigh the cons depend on how much of a performance hit you're taking by looking at the other columns. Profile it before committing!
Count(columnName) will NEVER count NULL values, count skips NULLS when you specify a column name and does count NULLS when you use *
run this
CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)
SELECT count(*) FROM testnulls WHERE ID IS NULL --1
SELECT count(ID) FROM testnulls WHERE ID IS NULL --0
I would use exists instead since it is a boolean operation and will stop at the first occurance of NULL
IF EXISTS (SELECT 1 FROM testnulls WHERE ID IS NULL)
PRINT 'YES'
ELSE
PRINT 'NO'
Building on kquinn's answer, in Oracle that would be
SELECT COL1 FROM TABLE1 WHERE COL1 IS NULL AND ROWNUM = 1;
That way the DBMS only has to read a single row before giving you your answer;
That statement is misleading, however. It has to read all rows until it finds one with the missing column value. Then it can stop and return that row.
If there is no such row, it will read the whole table.
so it might be possible to satisfy the query with an index on COL1, making the query faster still.
Specifying only COL1 will not have too much impact, at least on Oracle, where (regular B-Tree) indices cannot be used to find NULL values.
You may want to select more columns anyway (such as the primary key value) if you are interested in identifiying the row later.
Two Solutions (Column is All NULLs, Column Contains Some NULLs)
I have slightly altered your original example in order to provide two solutions:
Column_1 Column_2 Column_3
-------- -------- --------
1 2 NULL
1 NULL NULL
5 6 NULL
First, test for NULLs and count them:
select
sum(case when Column_1 is null then 1 else 0 end) as Column_1,
sum(case when Column_2 is null then 1 else 0 end) as Column_2,
sum(case when Column_3 is null then 1 else 0 end) as Column_3,
from TestTable
Yields a count of NULLs:
Column_1 Column_2 Column_3
0 1 3
Where the result is 0, there are no NULLs.
Second, let's count the non-NULLs:
select
sum(case when Column_1 is null then 0 else 1 end) as Column_1,
sum(case when Column_2 is null then 0 else 1 end) as Column_2,
sum(case when Column_3 is null then 0 else 1 end) as Column_3,
from TestTable
...But because we're counting non-NULLs here, this can be simplified to:
select
count(Column_1) as Column_1,
count(Column_2) as Column_2,
count(Column_3) as Column_3,
from TestTable
Either one yields:
Column_1 Column_2 Column_3
3 2 0
Where the result is 0, the column is entirely made up of NULLs.
If you only need to check a given column, then TOP 1 is quicker because it should stop at the first hit:
select count(*) from (select top 1 'There is at least one NULL' AS note from TestTable where Column_3 is NULL) a
0 = There are no NULLs, 1 = There is at least one NULL
SELECT COUNT(*) FROM (SELECT TOP 1 'There is at least one non-NULL' AS note FROM sat_data_active_season_group WHERE season_group IS NOT NULL) a
0 = They are all NULL, 1 = There is at least one non-NULL
I hope this helps.
we can check with the help of IN like
...WHERE NULL IN (Column_2, Column_3)
from your comment Well the multiple column will be Column_3, Column_2 in format
might be this is helpful for you
select * from (select Column_3, Column_2 from @temp where null in (Column_3, Column_2)) as Result