AND / OR logic in WHERE statement

Mastering AND / OR Logic in SQL WHERE Clauses

Unlock the power of conditional filtering in SQL. Learn to combine AND and OR operators effectively to retrieve precise datasets, understand operator precedence, and avoid common pitfalls for robust query construction.

The WHERE clause is fundamental to filtering data in SQL. While simple equality checks are straightforward, real-world data retrieval often requires combining multiple conditions. This is where logical operators like AND and OR become indispensable. Understanding how these operators work, especially when used together, is crucial for writing efficient, accurate, and maintainable SQL queries. This article delves into the nuances of AND and OR logic, including operator precedence and best practices.

The Basics: AND and OR Operators

AND and OR are binary logical operators that combine two Boolean expressions. The result of a condition involving these operators is either TRUE, FALSE, or UNKNOWN (when NULL values are involved). These operators allow you to specify multiple criteria that rows must satisfy to be included in the result set.

The AND Operator

The AND operator returns TRUE only if all the combined conditions are TRUE. If even one condition is FALSE, the entire AND expression evaluates to FALSE. It is used when you need to narrow down your results to rows that meet every specified criterion.

SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department,
    Salary
FROM
    Employees
WHERE
    Department = 'Sales' AND Salary > 50000;

This query retrieves employees from the 'Sales' department who also have a salary greater than 50,000. Both conditions must be met.

The OR Operator

The OR operator returns TRUE if any of the combined conditions are TRUE. It only returns FALSE if all combined conditions are FALSE. OR is used when you want to broaden your results to include rows that meet at least one of several specified criteria.

SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department,
    Salary
FROM
    Employees
WHERE
    Department = 'Marketing' OR Department = 'HR';

This query retrieves employees who are either in the 'Marketing' department or the 'HR' department. If an employee belongs to either, they are included.

Operator Precedence and Parentheses

When AND and OR operators are used together in a single WHERE clause, their order of evaluation matters. SQL follows specific rules of operator precedence, much like arithmetic operations. In SQL, the AND operator has a higher precedence than the OR operator. This means that AND conditions are evaluated before OR conditions.

Consider the following query:

SELECT * FROM Products WHERE Category = 'Electronics' OR Price > 100 AND Quantity < 5;

Without parentheses, this query is interpreted as:

SELECT * FROM Products WHERE Category = 'Electronics' OR (Price > 100 AND Quantity < 5);

The (Price > 100 AND Quantity < 5) part is evaluated first. Only then is its result combined with Category = 'Electronics' using OR. This can lead to unexpected results if you intended a different grouping of conditions.

SQL Operator Precedence: AND before OR

To override the default precedence and explicitly control the order of evaluation, you should use parentheses (). Parentheses force the enclosed conditions to be evaluated first, ensuring your query logic is applied exactly as intended.

SELECT
    ProductID,
    ProductName,
    Category,
    Price,
    Quantity
FROM
    Products
WHERE
    (Category = 'Electronics' OR Category = 'Appliances') AND Price < 500;

This query correctly filters for products that are either 'Electronics' or 'Appliances' AND have a price less than 500. The parentheses ensure the OR conditions are grouped first.

Practical Examples and Best Practices

Combining AND and OR effectively is a common requirement in data analysis and application development. Let's look at more complex scenarios and some best practices.

Example: Filtering by Multiple Criteria with Exceptions

Suppose you need to find all orders placed by customers from 'USA' or 'Canada', but exclude any orders that have a 'Cancelled' status.

SELECT
    OrderID,
    CustomerID,
    OrderDate,
    Country,
    OrderStatus
FROM
    Orders
WHERE
    (Country = 'USA' OR Country = 'Canada') AND OrderStatus <> 'Cancelled';

This query uses parentheses to group the country conditions, then applies the AND operator to exclude cancelled orders. Note: <> is a common SQL operator for 'not equal to'.

Example: Using IN for Multiple OR Conditions

When you have many OR conditions against the same column, the IN operator provides a more concise and often more readable alternative.

SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department
FROM
    Employees
WHERE
    Department IN ('Sales', 'Marketing', 'IT');

This is equivalent to Department = 'Sales' OR Department = 'Marketing' OR Department = 'IT' but is much cleaner.

Handling NULL Values

It's important to remember that AND and OR interact with NULL values in specific ways, leading to UNKNOWN results. For instance, TRUE AND NULL evaluates to UNKNOWN, and FALSE OR NULL evaluates to UNKNOWN. When filtering for NULL values, always use IS NULL or IS NOT NULL instead of = or <>.

SELECT
    CustomerID,
    ContactName,
    Email
FROM
    Customers
WHERE
    Email IS NULL OR (City = 'London' AND Country = 'UK');

This query retrieves customers with no email address OR customers from London, UK. Email IS NULL correctly identifies rows where the email column has a NULL value.