SQL: Using Parentheses with And / OR condition is necessary with where clause to gives expected result

Are parentheses necessary in SQL: In this article will explain the difference between a simple SQL select query using with parentheses and using without parentheses one, and you will notice the different result of each query. One day, my junior asked me one question why I am using parentheses ( brackets ) in most of my SQL query and is it really necessary to use round bracket. Then I thought why not make a post on it, so it helps other.

My answer was YES, I use parenthesis to bind components of where clauses, i.e., If the query has multiple and or condition, then it’s necessary to use parentheses to ensure correct order of operations. In short for complex queries it necessary to use round Bracket and for the simple query, you can avoid round bracket.

 

Let’s see with example

Example 1: Simple SQL query with/without parenthesis

  • Simple Query without parenthesis:
SELECT * 
FROM employee
WHERE department_id=1

Here’s the output of above simple SQL query:

simple sql query without parenthesis

 

  • Simple Query with parenthesis: Now we write the same query with parenthesis.
SELECT *
FROM employee
WHERE (department_id=1)

Here’s the output of above query:simple sql query with parenthesis
As you will see both gives the same result. Here in example 1, it won’t affect the result as its a simple query. Now let’s see to Example 2

Also Read: 1 Best SQL Script to take Backup of all DataBases.


Example 2: Complex SQL query with/without parenthesis (using and/or condition)

  • Complex SQL Query without parenthesis:
SELECT *
FROM employee
WHERE department_id=1 OR department_id=2
AND salary < 3000

Here’s the result:

sql select quert without parenthesis round bracket SQL query without parenthesis.

 

  • Complex SQL Query without parenthesis:
    Now we write the same query with parenthesis .i.e using round bracket over and or condition.
SELECT *
FROM employee
WHERE (department_id=1 OR department_id=2)
AND (salary < 3000)

Here’s the result of above query:Complex Sql query with parenthesis

 

You can easily get an idea both results are different. Here in example 2, round brackets determine the order that the AND and OR conditions are evaluated, same like what we learned in math order of operation (BODMAS).

Parentheses tell SQL Server to ignore the order of evaluation and assess what is in the parentheses first then evaluate the second part of the WHERE clause.

Also Read: How to Get Time part from Datetime in MS SQL server?

Hope you enjoy this tutorial.

Reference Link:

 

Share:    
Satinder Singh

Hi all, am Founder and Author of Codepedia.info, also atypical polyglot programmer from Bombay, India. I have a passion to create, solve, and deploy software applications.
       ForEach ( minute in MyLife ) { myExperience++ ; }



Post Comment


Your email address will not be published. Required fields are marked *