sql_and_or

SQL AND, OR, and NOT Operators

The AND, OR, and NOT operators are used in the WHERE clause to filter records based on multiple conditions. These operators allow for more complex and precise data retrieval.

Understanding Logical Operators

The AND Operator

The AND operator displays a record if all specified conditions separated by AND are TRUE.

The OR Operator

The OR operator displays a record if any of the specified conditions separated by OR are TRUE.

The NOT Operator

The NOT operator displays a record if the condition(s) is NOT TRUE. It can be used to exclude specific records.

Demo Table Context

For the examples in this section, let’s assume we are working with a Customers table. This table includes columns like CustomerID, CustomerName, City, Country, and PostalCode.

AND Operator Example

The following SQL statement selects all customers from the country ‘Germany’ AND the city ‘Berlin’ from the Customers table:

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

This query will only return customers who are located in Berlin, Germany. Customers in other German cities or other Berlins worldwide will not be included.

OR Operator Example

The following SQL statement selects all customers from either the city ‘Berlin’ OR the city ‘London’ from the Customers table:

SELECT CustomerName, City, Country
FROM Customers
WHERE City = 'Berlin' OR City = 'London';

This query will return customers located in Berlin (regardless of country) and customers located in London (regardless of country).

NOT Operator Example

The following SQL statement selects all customers who are NOT from the country ‘USA’:

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

Alternatively, you can use the <> or != operator for simple negations:

SELECT CustomerName, Country
FROM Customers
WHERE Country <> 'USA';

Combining AND, OR, and NOT

You can combine AND, OR, and NOT operators. When combining these operators, it is important to use parentheses () to define the order of evaluation and ensure the query behaves as intended. Conditions within parentheses are evaluated first.

Example: Select customers from ‘Germany’ who are in ‘Berlin’ OR ‘München’.

The following SQL statement selects all customers from the country ‘Germany’ AND their city is either ‘Berlin’ OR ‘München’:

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

Without parentheses, Country = 'Germany' AND City = 'Berlin' OR City = 'München' would be interpreted as (Country = 'Germany' AND City = 'Berlin') OR City = 'München', which would also include customers from ‘München’ regardless of their country. This is a common pitfall.

Example: Select customers who are NOT from ‘Germany’ AND NOT from ‘USA’.

SELECT CustomerName, Country
FROM Customers
WHERE NOT (Country = 'Germany' OR Country = 'USA');

This can also be written as:

SELECT CustomerName, Country
FROM Customers
WHERE Country <> 'Germany' AND Country <> 'USA';

Or using the IN operator with NOT:

SELECT CustomerName, Country
FROM Customers
WHERE Country NOT IN ('Germany', 'USA');

Understanding operator precedence (NOT is highest, then AND, then OR) is crucial, but using parentheses to explicitly group conditions is always a good practice for clarity and correctness.