It looks like you need something like:
CopyIF 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.
Check if a column's value is null in SQL Server - Stack Overflow
Why doesn't SQL support "= null" instead of "is null"? - Stack Overflow
IS NULL vs =NULL - SQLTeam.com Forums
SQL is null and = null - Stack Overflow
Videos
It looks like you need something like:
CopyIF 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.
CopyIF 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.
CopyIF EXISTS (SELECT TU.Tagged FROM TopicUser TU
WHERE TU.TopicId = @TopicId and TU.UserId = @UserId
AND TU.Tagged IS NOT NULL)
BEGIN
END
The reason why it's off by default is that null is really not equal to null in a business sense. For example, if you were joining orders and customers:
Copyselect * from orders o join customers c on c.name = o.customer_name
It wouldn't make a lot of sense to match orders with an unknown customer with customers with an unknown name.
Most databases allow you to customize this behaviour. For example, in SQL Server:
Copyset ansi_nulls on
if null = null
print 'this will not print'
set ansi_nulls off
if null = null
print 'this should print'
Equality is something that can be absolutely determined. The trouble with null is that it's inherently unknown. If you follow the truth table for three-value logic, null combined with any other value is null - unknown. Asking SQL "Is my value equal to null?" would be unknown every single time, even if the input is null. I think the implementation of IS NULL makes it clear.
In SQL, a comparison between a null value and any other value (including another null) using a comparison operator (eg =, !=, <, etc) will result in a null, which is considered as false for the purposes of a where clause (strictly speaking, it's "not true", rather than "false", but the effect is the same).
The reasoning is that a null means "unknown", so the result of any comparison to a null is also "unknown". So you'll get no hit on rows by coding where my_column = null.
SQL provides the special syntax for testing if a column is null, via is null and is not null, which is a special condition to test for a null (or not a null).
Here's some SQL showing a variety of conditions and and their effect as per above.
create table t (x int, y int);
insert into t values (null, null), (null, 1), (1, 1);
select 'x = null' as test , x, y from t where x = null
union all
select 'x != null', x, y from t where x != null
union all
select 'not (x = null)', x, y from t where not (x = null)
union all
select 'x = y', x, y from t where x = y
union all
select 'not (x = y)', x, y from t where not (x = y);
returns only 1 row (as expected):
TEST X Y
x = y 1 1
See this running on SQLFiddle
It's important to note, that NULL doesn't equal NULL.
NULL is not a value, and therefore cannot be compared to another value.
where x is null checks whether x is a null value.
where x = null is checking whether x equals NULL, which will never be true