SQL Logic Operator Precedence: And and Or

by

And has precedence over Or, so, even if a <=> a1 Or a2

Where a And b

is not the same as

Where a1 Or a2 And b,

because that would be Executed as 

Where a1 Or (a2 And b)

and what you want, to make them the same, is the following (using parentheses to override rules of precedence):

 Where (a1 Or a2) And b

Here’s an example to illustrate:

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 T
Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- outputs F

For those who like to consult references (in alphabetic order):

More Related Contents:

  1. SQL UPDATE order of evaluation
  2. PostgreSQL does not accept column alias in WHERE clause
  3. The difference between ‘AND’ and ‘&&’ in SQL
  4. SQL Server search using like while ignoring blank spaces
  5. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  6. Run a query with a LIMIT/OFFSET and also get the total number of rows
  7. How to retrieve the current value of an oracle sequence without increment it?
  8. T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition
  9. Left Join without duplicate rows from left table
  10. How do I find the data directory for a SQL Server instance?
  11. How to update rows with a random date
  12. How to create a new database with the hstore extension already installed?
  13. Oracle date “Between” Query
  14. How do I compare two columns for equality in SQL Server?
  15. How important are lookup tables?
  16. Finding unmatched records with SQL
  17. Designing SQL database to represent OO class hierarchy
  18. Oracle RAC and sequences
  19. Find last sunday
  20. How to concatenate all columns in a select with SQL Server
  21. SQL select join: is it possible to prefix all columns as ‘prefix.*’?
  22. Removing duplicate rows (based on values from multiple columns) from SQL table
  23. Padding zeros to the left in postgreSQL
  24. Hierarchical Queries in SQL Server 2005
  25. What is “Connect Timeout” in sql server connection string?
  26. how to use LIKE with column name
  27. How to implement a Keyword Search in MySQL?
  28. sql query joins multiple tables – too slow (8 tables)
  29. how to get the full resultset from SSMS
  30. T-SQL calculate moving average

Leave a Comment