11

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.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
diiN__________
  • 7,393
  • 6
  • 42
  • 69
  • It could, but it haven't. MySQL, for instance, could and did ;-) – Álvaro González Apr 27 '16 at 11:41
  • @Á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 – Panagiotis Kanavos Apr 27 '16 at 11:47
  • @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. – Álvaro González Apr 27 '16 at 12:02
  • 1
    @Á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 – Panagiotis Kanavos Apr 27 '16 at 12:15

4 Answers4

22

Use case:

SELECT CONVERT(BIT, (CASE WHEN @someVariable IS NULL THEN 1 ELSE 0 END))
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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`. – diiN__________ Apr 27 '16 at 11:42
  • @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. – Álvaro González Apr 27 '16 at 11:43
  • @Á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. – diiN__________ Apr 27 '16 at 11:49
  • @Á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 Apr 27 '16 at 11:49
  • @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 Apr 27 '16 at 11:51
  • @diiN_ you can find a detailed discussion in [Wikipedia's article on SQL NULL](https://en.wikipedia.org/wiki/Null_(SQL)), its implications and the extensions used to handle it in various products – Panagiotis Kanavos Apr 27 '16 at 11:52
  • @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. – diiN__________ Apr 27 '16 at 11:54
  • @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](http://blogs.technet.com/b/seanearp/archive/2007/04/12/why-doesn-t-this-feature-exist.aspx): "By default features don't exist. Somebody has to implement them." – Álvaro González Apr 27 '16 at 11:59
  • @Á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. – Panagiotis Kanavos Apr 27 '16 at 12:04
  • 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". – Álvaro González Apr 27 '16 at 12:30
  • @Á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". – Panagiotis Kanavos Apr 27 '16 at 12:37
3

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

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

Dana
  • 634
  • 7
  • 12
2

not a direct cast 

select cast(isnull(@null,1) as bit)
paparazzo
  • 44,497
  • 23
  • 105
  • 176
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.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • 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? – Álvaro González Apr 28 '16 at 08:55
  • @Á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 – Panagiotis Kanavos Apr 28 '16 at 11:18
  • I definitively misunderstood the question because I always thought it was all about the syntax. Never mind... and thanks for a great answer. – Álvaro González Apr 28 '16 at 14:02
  • ISNULL is not the equivalent to NULLIF, it is the sqlserver equivalent to COALESCE, with the restriction to only allow two arguments. – Raul May 18 '16 at 12:46
  • @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 – Panagiotis Kanavos May 18 '16 at 14:09
  • @PanagiotisKanavos There was no unsubstantiated downvote – Raul May 18 '16 at 14:56