Parentheses in SQL

Parentheses

admin Oracle, SQL ,

Hey time travellers,
Though Parentheses are used pretty much everywhere in SQL but in this blog, we will talk about Parentheses in WHERE clause of SQL statement. Parentheses are widely used in each and every programming language. A wrong placement of Parentheses can ruin our hundred line code and SQL engine can slap an unexpected error. Let’s discuss the use of Parentheses and save our code.
Parentheses are also known as Small Brackets ( ).
Parentheses are used to instruct SQL engine the flow of SQL statement. It helps us to get more control over the where clause and overrides the default working process flow of SQL engine. We will take the EMPLOYEES table of the HR schema in examples.

Example-1:

 
SELECT
    *
FROM
    employees
WHERE
    department_id = 90
    OR department_id = 50
    AND manager_id = 100;

In the above code, we have not used Parentheses so SQL engine will decide and output itself and display all the records where either department_id = 90 as a first step. Then, it will check the records with the combination of department_id = 50 and manager_id = 100. It means SQL engine will consider above statement as (department_id = 90 OR (department_id = 50 AND manager_id = 100). So we can say if we do not use parentheses, SQL engine calculates the component conditions bound with AND logical condition first and then OR logical condition and shows the results.

Example-2:

 
SELECT
    *
FROM
    employees
WHERE
    (department_id = 90
    OR department_id = 50)
    AND manager_id = 100;

Now, we have instructed the SQL engine to process this SQL with more control. As a first step, it will check all the records where department_id is 90 or 50. Then, it will check records with manager_id = 100 out of those records.

Example-3:

 
SELECT
    *
FROM
    employees
WHERE
    manager_id = 100
    OR ( ( department_id = 50
           OR salary between 2100 AND 3000 )
         AND manager_id = 123 );

SQL engine processes the above code in below-mentioned steps:

  1. Identifies the employees with a salary between 2100 and 3000.
  2. Identifies the employees with department_id = 50 and merges them in the point-1 records.
  3. Keeps the employees with manager_id = 123 and removes the other records.
  4. Identifies the employees with manager_id = 100 and merges them in the point-3 records.

Tip:
It is easy to add Parentheses while writing a fresh code but sometimes, it becomes hard to read and rewriting the existing code. So if we start reading the code and solve Parentheses from inside to outside, it helps a lot.

If you have any questions/suggestions, please comment below or write me an email.
If you like this blog, do share with your friends and colleagues on your social media. For more updates join my Facebook group and do like my Facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

DUAL TABLE

Oracle_DUAL table

Hey geeks, DUAL table is a very important table available in the Oracle database. It gets created automatically during the […]

Concatenation Operator

Oracle_Concatenation Operator

Hey geeks, This is another chapter of Operators. Concatenation Operator helps to concatenate two or more strings or columns. The […]

Logical Conditions

Oracle – Logical Conditions

Hey time travellers, In this article, we will be discussing Logical Conditions. It is a very important aspect of SQL. […]

Leave a Reply

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