Why you should use parentheses with AND & OR in SQL

If you filter the data in SQL with WHERE using multiple logical conditions tied with AND & OR, PLEASE use the parentheses them accordingly.

Because if you don't, your fellow team members are going to have a harder time understanding your intent.

You might also get unintended results based on how they are resolved:

... operators with the same precedence are left associative. This means that those operators are grouped together starting from the left and moving right.

AND has a higher order of precedence than OR, therefore, in the example below:

is_paid AND is_shipped OR customer_is_on_contract AND is_first_time_buyer

is equivalent to

( is_paid AND is_shipped) OR (customer_is_on_contract AND is_first_time_buyer).

It should also be noted that for comparison operators parentheses are required in order to resolve ambiguity since they are not associative like NOT/AND/OR.

WITH input_data AS (
  SELECT 1 AS order_id, TRUE AS is_paid, FALSE AS is_shipped, TRUE AS customer_is_on_contract, TRUE AS is_first_time_buyer
  UNION ALL
  SELECT 2 AS order_id, FALSE AS is_paid, TRUE AS is_shipped, TRUE AS customer_is_on_contract, TRUE AS is_first_time_buyer
  UNION ALL
  SELECT 3 AS order_id, FALSE AS is_paid, FALSE AS is_shipped, TRUE AS customer_is_on_contract, FALSE AS is_first_time_buyer
)

SELECT *

FROM input_data

WHERE
      --is_paid AND is_shipped OR customer_is_on_contract AND is_first_time_buyer
      --resolved as:
     (is_paid AND is_shipped) OR (customer_is_on_contract AND is_first_time_buyer)

---

Enjoyed this? Here are some related articles you might find useful:

• 9 tips on writing cleaner SQL
• Order of precedence in SQL: WHERE vs HAVING
• Easy with that SELECT DISTINCT!
• ORDER BY expressions in SQL