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)
Check if columns are NULL or contains NULL in table. - MS SQL Server - Stack Overflow
Counting rows where ALL columns are either null or empty in the row?
sql - Find out column having null values - Stack Overflow
sql - How do I check if a column is empty or null in MySQL? - Stack Overflow
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
I'd rather not write a bunch of AND clauses, so is there a quick, efficient way to do this?
I'm importing some data with 10 fields into a SQL Server from a CSV file. Occasionally this file has null/empty values across all the cells/columns.
What I'd like to do is just write one relatively short sql statement to simply count (at first) all these rows. I'd rather do it without doing something like:
...and (column1 is null or column1 = '')
...and (column2 is null or column2 = '')
etc...
Is there a good way to do this, or am I stuck with the above?
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!
No, you cannot do what you want. You need to list all the columns out.
However, your query will not do this. You need to use IS NULL rather than = NULL:
UPDATE item_list
SET is_tradable = 'no'
WHERE item_name = :itemname AND
(element_1 IS NULL OR element_2 IS NULL or element_3... etc.)
Also, remember the parentheses when mixing AND and OR.
There are ways to "simplify" the calculation. For instance, concat() will return NULL if any argument is NULL:
UPDATE item_list
SET is_tradable = 'no'
WHERE item_name = :itemname AND
concat(element_1, element_2, . . . ) IS NULL
As do the arithmetic operators (but your question does not state the type of the arguments).
There is a way but you need to mention all the column names in null comparison something as
mysql> select least(null,'aa',null,'bb') ;
+----------------------------+
| least(null,'aa',null,'bb') |
+----------------------------+
| NULL |
+----------------------------+
1 row in set (0.00 sec)
Here its getting the least values from a group of values.
So you may use as
WHERE item_name=:itemname
AND least(element_1,element_2,element_3...) is null
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
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.
Hello, I’m trying to do a system where I need to check if an given row has any value that is null. But the thing is, I don’t actually need to ensure not all columns are null, but rather an subset of them. And that subset is different based on user input.
For example I have
Column 1, column 2 , Column 3, column 4.
Sometimes I’d want to check if column 1,2 and 4 are not null, and sometimes I’d want to check if columns 1 and 4 aren’t null. Etc, it could be any subset of 1,2,3,4.
I thought that I could just load it in python and check for the dict keys, but I wanted to do it in a pure MySql way.
smells like an anti-pattern.
dont treat your columns as arrays or elements addressable by position
Sometimes I’d want to check if column 1,2 and 4 are not null
WHERE column1 IS NOT NULL
AND column2 IS NOT NULL
AND column4 IS NOT NULLand sometimes I’d want to check if columns 1 and 4 aren’t null.
WHERE column1 IS NOT NULL
AND column4 IS NOT NULL