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.

Downstage

It could, but it haven't. MySQL, for instance, could and did ;-) – Volution

@ÁlvaroGonzález MySQL contains a lot of non-standard statements and even break's SQL's logic. IS can't be used outside of a conditional expression. It's not a comparison operator. In null-valued logic, any comparison to NULL is null. IS NULL and IS NOT NULL are the only conditional operators that can be used – Kipton

@PanagiotisKanavos I understand that foo=NULL is different from foo IS NULL (and why). But, is there some specific scenario where MySQL implementation breaks something? After all, when you use WHERE deletion_date IS NULL you eventually get a boolean non-nullable decision: display the row o not. – Volution

@ÁlvaroGonzález yes there many. First of all, BIT is not BOOLEAN, it's just a number. There is a different, optional BOOLEAN type that is implemented only by PostgreSQL. Second, that cast does break 3VL. Any comparisons to NULL should return NULL, yet that arbitrary cast doesn't. If you wanted to replace NULL with 1, just use a function like NULLIF or COALESCE. Finally, you can cast a NULL to represent a missing value of a specific type. CAST(NULL as BIT) is allowed and returns a NULL BIT – Kipton

Answer 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.

Kipton

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? – Volution

@Á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 – Kipton

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

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

@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 – Kipton

@PanagiotisKanavos There was no unsubstantiated downvote – Bookstore

Answer 2

Use case:

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

Dodwell

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. – Downstage

@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. – Volution

@Á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. – Downstage

@Á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 – Kipton

@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. – Kipton

@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 – Kipton

@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. – Downstage

@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." – Volution

@Á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. – Kipton

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". – Volution

@Á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". – Kipton

Answer 3

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

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

Volitive

Answer 4

not a direct cast

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

Commoner