Combining AND and OR Operators in SQL Queries for Complex Filtering

2 years ago
Sophia Anderson
2 minutes

In SQL, the AND and OR logical operators are fundamental for combining multiple conditions in a WHERE clause, allowing you to retrieve data that meets specific criteria. Understanding how to use them effectively, especially in combination, is crucial for complex data filtering.

Understanding AND and OR

  • AND Operator: This operator requires all conditions it connects to be true for a row to be included in the result set. If any condition linked by AND is false, the entire condition becomes false.
  • OR Operator: This operator requires at least one of the conditions it connects to be true for a row to be included. If any condition linked by OR is true, the entire condition becomes true.

Combining AND and OR Operators

When using both AND and OR in the same WHERE clause, it’s important to remember that AND has a higher precedence than OR. This means AND conditions are evaluated before OR conditions. To override this default precedence and ensure your conditions are evaluated as intended, use parentheses ().

Example Scenario:

Consider an employees table with columns like salary and location. We want to find employees who have a salary greater than 50000 AND work in either ‘New York’ OR ‘California’.

Incorrect Approach (without parentheses):

SELECT * 
FROM employees 
WHERE salary > 50000 
AND location = 'New York' OR location = 'California';

In this incorrect example, due to operator precedence, SQL would evaluate salary > 50000 AND location = 'New York' first, and then combine that result with location = 'California' using OR. This would incorrectly include employees from ‘California’ even if their salary is not over 50000.

Correct Approach (with parentheses):

SELECT * 
FROM employees 
WHERE salary > 50000 
AND (location = 'New York' OR location = 'California');

By enclosing the OR conditions within parentheses, we force SQL to evaluate (location = 'New York' OR location = 'California') first. The result of this evaluation (true or false) is then combined with the salary > 50000 condition using AND. This ensures that only employees meeting both the salary criterion AND being in either New York or California are returned.

Always use parentheses to explicitly define the order of evaluation when combining AND and OR operators to avoid unexpected results and ensure your queries accurately reflect your logical requirements.

bannerAds