CODEWITHSUNDEEP
sqlsql-servert-sql
Pepe
Pepe

Reputation: 111

Null value is considered an existing value

I have a validation into if conditional like:

IF(EXISTS
    (SELECT TOP 1 [TaskAssignationId]
     FROM [Task] AS [T]
          INNER JOIN @TaskIdTableType AS [TT] ON [T].[TaskId] = [TT].[Id]
))

But it returns NULL value because TaskAssignationId is NULL so in consequence IF condition it's true because it exist with NULL value, but I don't want to consider NULL as a value. How can add an exception of nulls? Regards

Upvotes: 0

Views: 61

Answers (3)

Nitika
Nitika

Reputation: 463

If you want to check the existence then no need to assign the column name, you can use select 1

IF(EXISTS SELECT TOP 1 1 FROM [Task] AS [T] INNER JOIN @TaskIdTableType AS [TT] ON [T].[TaskId] = [TT].[Id] )) begin ----code--- end

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74605

Exists works like "Did the (sub)query return more than zero (correlated) rows" not "did the (sub)query return a non null value"

These are perfectly valid exists:

SELECT * FROM person p
WHERE EXISTS (SELECT null FROM address a WHERE a.personid = p.id)

SELECT * FROM person p
WHERE EXISTS (SELECT 1 FROM address a WHERE a.personid = p.id)

SELECT * FROM person p
WHERE EXISTS (SELECT * FROM address a WHERE a.personid = p.id)

It doesn't matter what values you return, or how many columns, exists cares whether the rowcount is 0 or greater when determining whether results exist

Hence you have to make sure your (sub)query returns no rows if you want the exists check to fail. If Addresses that have a null type are unacceptable, the (sub)query has to exclude them with WHERE a.type IS NOT NULL so that only rows with a non null type are considered

There's also little point doing a TOP 1 in the (sub)query; the optimiser knows that the only condition it cares about is 0 or not-0 rows, so it automatically do a TOP 1 (i.e. it will stop retrieving data when it knows there is at least one row)

Upvotes: 0

Brian
Brian

Reputation: 7289

If you don't want to include rows where [TaskAssignationId] is null then add that to a WHERE clause.

IF(EXISTS
    SELECT TOP 1 [TaskAssignationId]
    FROM [Task] AS [T]
    INNER JOIN @TaskIdTableType AS [TT] ON [T].[TaskId] = [TT].[Id]
    WHERE [TaskAssignationId] is not null  
))

Upvotes: 4

Related Questions