Filtering with AND, OR, NOT

The WHERE clause allows specifying a single condition to filter rows returned by a SELECT statement. Often, however, filtering needs are more complex. You might need to find rows that meet multiple criteria simultaneously, or rows that meet at least one of several criteria. Logical operators AND, OR, and NOT become essential tools for these scenarios. They allow combining multiple conditions within a single WHERE clause to precisely define the data subset required.

Combining Conditions with AND

The AND operator is used when you want to retrieve rows that satisfy all specified conditions. Think of it as an intersection: a row must meet condition 1 and condition 2 and so on to be included in the result set.

The basic syntax looks like this:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

Let's imagine we have a Products table with columns like product_name, category, and price. If we want to find all products that are in the 'Electronics' category and cost more than $500, we would use AND:

SELECT product_name, category, price
FROM Products
WHERE category = 'Electronics' AND price > 500;

Only products that satisfy both category = 'Electronics' and price > 500 will appear in the output. If a product is in 'Electronics' but costs $400, it won't be included. Similarly, if a product costs$600 but is in the 'Home Goods' category, it won't be included either.

You can chain multiple AND operators together if you need to satisfy more than two conditions:

SELECT product_name, category, price, stock_quantity
FROM Products
WHERE category = 'Electronics' AND price > 500 AND stock_quantity > 0;

This query finds electronic products costing over $500 that are also currently in stock.

Combining Conditions with OR

The OR operator is used when you want to retrieve rows that satisfy at least one of the specified conditions. Think of it as a union: a row just needs to meet condition 1 or condition 2 or any other condition connected by OR to be included.

The syntax is similar to AND:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

Using our Products table again, suppose we want to find all products that are either in the 'Electronics' category or in the 'Appliances' category. We use OR:

SELECT product_name, category, price
FROM Products
WHERE category = 'Electronics' OR category = 'Appliances';

This query will return products if their category is 'Electronics', or if their category is 'Appliances', or if (hypothetically) a product could belong to both. As long as one of the conditions connected by OR is true for a given row, that row is included in the results.

Just like AND, you can chain multiple OR conditions:

SELECT product_name, category, price
FROM Products
WHERE category = 'Electronics' OR category = 'Appliances' OR price < 50;

This query retrieves products that are in the 'Electronics' category, or products that are in the 'Appliances' category, or any product (regardless of category) that costs less than $50.

Negating Conditions with NOT

The NOT operator is used to reverse the logic of a condition. It selects rows for which the specified condition is false.

Its syntax usually involves placing NOT before the condition you want to negate:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

For example, to find all products that are not in the 'Electronics' category:

SELECT product_name, category, price
FROM Products
WHERE NOT category = 'Electronics';

Alternatively, and often more readable for simple equality/inequality, you can use the <> or != operator (which mean "not equal to"):

SELECT product_name, category, price
FROM Products
WHERE category <> 'Electronics'; -- Or use category != 'Electronics'

The NOT operator is particularly useful when combined with other operators like IN, BETWEEN, or LIKE. For instance, to find products whose category is not 'Electronics' and not 'Appliances':

SELECT product_name, category, price
FROM Products
WHERE category NOT IN ('Electronics', 'Appliances');

This is often clearer than writing WHERE NOT (category = 'Electronics' OR category = 'Appliances').

Operator Precedence and Using Parentheses

When you combine AND, OR, and NOT in a single WHERE clause, the database needs to know the order in which to evaluate these conditions. SQL has a standard order of precedence:

1. NOT is evaluated first.
2. AND is evaluated next.
3. OR is evaluated last.

This precedence order can sometimes lead to unexpected results if you're not careful. Consider this query: Find products that are in the 'Electronics' category and cost over $500, or products that are simply 'New'.

A naive attempt might be:

-- Potentially ambiguous query
SELECT product_name, category, price, status
FROM Products
WHERE category = 'Electronics' AND price > 500 OR status = 'New';

Because AND has higher precedence than OR, the database interprets this as: (category = 'Electronics' AND price > 500) OR status = 'New'

This means it will select:

• Any product where the category is 'Electronics' AND the price is greater than 500.
• Plus, any product where the status is 'New' (regardless of its category or price).

This might not be what you intended. If you wanted to find 'Electronics' products that meet either the condition price > 500 or the condition status = 'New', you must use parentheses () to override the default precedence and clarify your logic:

-- Clearer query using parentheses
SELECT product_name, category, price, status
FROM Products
WHERE category = 'Electronics' AND (price > 500 OR status = 'New');

Now, the condition inside the parentheses (price > 500 OR status = 'New') is evaluated first. The query will only return products that meet both of these criteria:

1. The category must be 'Electronics'.
2. The product must also satisfy either price > 500 or status = 'New'.

Best Practice: Even when the default precedence matches your intention, using parentheses makes your WHERE clause much easier to read and understand, reducing the chance of logical errors. When combining AND and OR, it's almost always a good idea to use parentheses to explicitly group your conditions.

Mastering AND, OR, and NOT, along with the strategic use of parentheses, allows you to construct sophisticated filtering logic, enabling you to isolate the exact data you need for your analysis from large datasets.