2 views
in SQL

Are the two statements below equivalent?

SELECT [...]

FROM [...]

WHERE some_col in (1,2,3,4,5) AND some_other_expr

and

SELECT [...]

FROM [...]

WHERE some_col in (1,2,3) or some_col in (4,5) AND some_other_expr

Is there some sort of truth table I could use to verify this?

by (40.4k points)

Have a look at this video to learn logic Operators like AND, OR in detail.

The Logical operators like AND OR return true or false values.

• AND: This Logical Operator compares between two Booleans as expression and if both the expressions are true it returns TRUE, if any of the expression is false it results in FALSE.
• OR: This logical operator compares between two Booleans as expression and even if one of the expressions is true, then it results in true.

AND has higher precedence than OR logical operator.

Example:

Where a AND b

It is not equal to

Where a1 OR a2 AND b

As it'll be executed like this

Where a1 OR (a2 AND b)

But you want, to make them the same, as the following (Here, parentheses is used to override rules of precedence):

Where (a1 OR a2) And b

Let’s see an example for more clarification:

Declare @x tinyInt = 1

Declare @y tinyInt = 0

Declare @z tinyInt = 0

Select Case When @x=1 OR @y=1 And @z=1 Then 'T' Else 'F' End -- outputs TRUE

Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- outputs FALSE