SQL And Or Not

Combining conditions

A single condition is often not enough to describe the rows you want. The logical operators AND, OR, and NOT let you join simple conditions into a larger test. AND requires every condition to be true, OR requires at least one to be true, and NOT reverses the result of the condition that follows it.

The AND operator

Use AND when a row must satisfy two or more conditions at the same time. The following query returns only customers that are located in Germany and also in the city of Berlin, so both parts must hold for a row to appear.

SELECT CustomerName, City, Country
FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';

The OR and NOT operators

OR is the right choice when a row should qualify if any one of several conditions is met. NOT sits in front of a condition and keeps the rows where that condition is false, which is useful for excluding a group.

SELECT CustomerName, Country
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

SELECT CustomerName, Country
FROM Customers
WHERE NOT Country = 'Germany';

Mixing operators and controlling order

You can combine AND, OR, and NOT in a single WHERE clause. When you do, AND is evaluated before OR, just as multiplication happens before addition in arithmetic. To make the intended grouping clear and to force a particular order, wrap conditions in parentheses.

SELECT CustomerName, City, Country
FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Munich');

• AND narrows a result set because more conditions must be met, so fewer rows usually match.
• OR widens a result set because a row only needs to satisfy one of the conditions.
• NOT flips a single condition and can be combined with operators such as IN and BETWEEN.
• Parentheses remove any doubt about how mixed AND and OR conditions are grouped.