In MySQL, Oracle and PostgreSQL an expresion like 1 = 1 can be used in where like:

where 1 = 1 as well as where (1 = 1) is not null

In MSSQL, it seem that:

  • a) when an expression is a predicate, it can not be further compared as an expression
  • b) where requires predicate

The question is - is it possible to wrap an expression like EXPR = 1 = 1 to be accepted into where EXPR as well as into where EXPR is not null for MSSQL?

In example:

  1. let EXPR string to be 1 = 1 (but can be [name] = 1 where name is a column, like any valid SQL expression)

  2. wrap EXPR here with some extra text from left or right side

  3. both of these queries must work:

  • a) SELECT (EXPR), ie. SELECT (1 = 1)
  • b) SELECT (EXPR) = 1, ie. SELECT (1 = 1) = 1

The original EXPR or the usage can not be modifed as hardcoded. The only possibility to modify the expression is to wrap it like convert(bit, EXPR) (but this does not work).

  • 1
    What you're actually asking is this: can <a DBMS implementation> implicitly cast between a boolean value and an integer, to which the answer is "read its manual". – mustaccio Sep 10 at 17:08

It boils down to SQL Server not having a boolean data type. If it did, we could say just:

WHERE myBoolCol 

And since a predicate returns true or false, then such a predicate could be used as an expression. (I'm ignoring the nuances of unknown and null here.) I.e.:

If you can say

WHERE myBoolCol

Then you can say

WHERE (1=1)

Above returns true (or false, perhaps in some other dimension). So what if you say below?

WHERE (1=1) = 1

Now, you are effectively saying:

WHERE true = 1

Now, true is a boolean (hypothetically, I already said that SQL Server don't have a boolean data type), and 1 will be interpreted as an int class type. So you are comparing a boolean to an int. What do you expect from this? As you probably know, SQL Server has implicit type conversion so if SQL Server had a boolean type, you would have to go to the rules for implicit conversions (this is all documented) and see 1) if you have implicit conversion between the hypothetical boolean and int, and 2) what direction that conversion would be.

Or, perhaps instead of the 1 in your example, really meant to say below?

WHERE (1=1) = true

Now that should be easy:

WHERE true = true

Which of course would be true. But again, SQL Server don't have a boolean type, so the discussion is purely theoretical in the SQL Server world. And, the bit data type is not a boolean type, it is an integer class type. So throwing bit into the picture doesn't change the reasoning by me considering the 1 being interpreted as an int.

Do the other products you mentioned have boolean data types?

| improve this answer | |

You can use a case statement (or IIF) to use comparisons in the manner you describe.


Note: A side effect is how NULLs are handled, in that if the expression does not evaluate to TRUE, it will be FALSE, including for NULLs.

| improve this answer | |
  • it returns zero rows: sqlfiddle.com/#!18/9eecb/96300 , I also need the EXPR (1=1) to be present only once, as computing it may be costly/slow – mvorisek Sep 10 at 20:46
  • "It returns zero rows". That is expected if one tests if 1 is NULL. My aim isn't to code for you, it's to illustrate a technique you may find helpful in solving your own problem. "I also need..." All of your requirement should be stated in the question so when others are reading this later they don't have to read all the comments to understand what you are actually looking for. – Graham Sep 11 at 21:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.