Sql-server – Can an expression be used as an expression as well as a predicate?

sql servert-sql

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

Best Answer

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?