Convert IS NULL to BIT

Question

This might be a very basic question but I just came over it while writing a query.

Why can't SQL Server convert a check for NULL to BIT? I was thinking about something like this:

DECLARE @someVariable INT = NULL;
-- Do something
SELECT CONVERT(BIT, (@someVariable IS NULL))

The expected outcome would then be either 1 or 0.

Solution 1

In SQL the language, NULLs are not considered data values. They represent a missing/unknown state. Quoting from Wikipedia's article on SQL NULL:

SQL null is a state (unknown) and not a value. This usage is quite different from most programming languages, where null means not assigned to a particular instance.

This means that any comparison against that UNKNOWN value can only be UNKNOWN itself. Even comparing two NULLs can't return true: if both values are unknown, how can we say that they are equal or not?

IS NULL and IS NOT NULL are predicates that can be used in conditional expressions. That means that they don't return a value themselves. Therefore, they can't be "cast" to a bit , or treated as a boolean.

Basic SQL comparison operators always return Unknown when comparing anything with Null, so the SQL standard provides for two special Null-specific comparison predicates. The IS NULL and IS NOT NULL predicates (which use a postfix syntax) test whether data is, or is not, Null.

Any other way of treating nulls is a vendor-specific extension.

Finally, BIT is not a boolean type, it's just a single-bit number. An optional BOOLEAN type was introduced in SQL 1999 but only PostgreSQL implements it correctly, ie having TRUE, FALSE or UNKNOWN values.

Without a BOOLEAN type you can't really calculate the result of a conditional expression like A AND B or x IS NULL. You can only use functions like NULLIF or COALESCE to replace the NULL value with something else.

Solution 2

Use case:

SELECT CONVERT(BIT, (CASE WHEN @someVariable IS NULL THEN 1 ELSE 0 END))

Solution 3

not a direct cast

select cast(isnull(@null,1) as bit)

Solution 4

Or use IIF (a little more readable than CASE):

CONVERT(BIT, IIF(@x IS NULL, 0, 1))

View more solutions

Comments

[diiN__________]

I know this can be achieved by using CASE. But I was asking why SQL Server doesn't know how to handle @someVariable IS NULL.

[Álvaro González]

@diiN_ I guess we all assumed you were asking for a way to do it. Programs are simply not smart enough to do things that haven't been coded to.

[diiN__________]

@ÁlvaroGonzález There are many ways how to get the desired result. I just didn't understand why it's not possible to use it like I was trying to.

[Panagiotis Kanavos]

@ÁlvaroGonzález that is not the case. You misunderstand what IS NULL does in SQL, the language. You can't use it outside CASE or WHERE

[Panagiotis Kanavos]

@diiN_ that's how SQL works (not just SQL Server), and it actually makes sense. Unlike C/C#/Java, a value in SQL can be missing ie NULL. NULL isn't just another value, it means there is no way to make any meaningful comparison with that value, not even to NULL. Therefore, treating IS NULL as a boolean expression would be meaningless.

[Panagiotis Kanavos]

@diiN_ you can find a detailed discussion in Wikipedia's article on SQL NULL, its implications and the extensions used to handle it in various products

[diiN__________]

@PanagiotisKanavos This is exactly the type of explanation I was asking for. I'd mark it as answer if you'd write is as one.

[Álvaro González]

@PanagiotisKanavos We can discuss if the feature is implemented, is useful or makes sense but "Why doesn't program X do Y" is what my customers say all the time to try to get developer time for fee (and I'm afraid it works with my manager). Quoting Raymond Chen: "By default features don't exist. Somebody has to implement them."

[Panagiotis Kanavos]

@ÁlvaroGonzález you misunderstand NULLs and SQL. IS NULL is not an expression. If MySQL treats it like this, it's breaking the SQL standard and three-valued logic. This isn't a matter of discussion - that's how SQL is defined.

[Álvaro González]

If SQL standard explicitly bans using it as expression outside a conditional (I don't know, I haven't read any technical documentation about it) then you must be right and MySQL breaks the standard like every other DBMS out there (and I'm sure than in worse ways that others). I just said the answer to "Why program X doesn't do Y?" is "Because their authors didn't write the code to do so".

[Panagiotis Kanavos]

@ÁlvaroGonzález or because they though "we shouldn't create arbitrary extensions that we'll have to support forever, preventing us from implementing the features currently under discussion".

[Álvaro González]

Nice explanation. May I add a follow-up question? If the original question was about SELECT CONVERT(BIT, (@someVariable=123)) (i.e., no NULL involved) would you add anything else to what's been said so far?

[Panagiotis Kanavos]

@ÁlvaroGonzález no because 1) that's invalid syntax 2) is this an assignment inside the cast or an attempted comparison? and 3) this should be a separate question with an equally long answer on what are valid expressions for CAST and the differences between the standard and vendor impelementations, and finally whether a comparison can return a value in SQL

[Álvaro González]

I definitively misunderstood the question because I always thought it was all about the syntax. Never mind... and thanks for a great answer.

[Raul]

ISNULL is not the equivalent to NULLIF, it is the sqlserver equivalent to COALESCE, with the restriction to only allow two arguments.

[Panagiotis Kanavos]

@RaulSebastian thanks for the downvote. The question though wasn't about NULLIF but how NULL is treated and why IS NULL doesn't return a value

[Raul]

@PanagiotisKanavos There was no unsubstantiated downvote