SQL AND, OR, and NOT Operators

The AND, OR, and NOT operators in SQL are used with the WHERE or HAVING clauses.

SQL AND Operator

The SQL AND operator selects data if all conditions are TRUE. For example,

-- select the first_name and last_name of all customers
-- who live in 'USA' and have the last name 'Doe'

SELECT first_name, last_name
FROM Customers
WHERE country = 'USA' AND last_name = 'Doe';

Here, the SQL command selects first_name and of last_name all customers whose country is USA and last_name is Doe from the Customers table.

SQL OR Operator

The SQL OR operator selects data if any one condition is TRUE. For example,

-- select first and last name of customers
-- who either live in the USA
-- or have the last name 'Doe'

SELECT first_name, last_name
FROM Customers
WHERE country = 'USA' OR last_name = 'Doe';

Here, the SQL command selects first_name and last_name of all customers where the country is USA or if their last name is Doe from the Customers table.

SQL NOT Operator

The SQL NOT operator selects data if the given condition is FALSE. For example,

-- select customers who don't live in the USA

SELECT first_name, last_name
FROM Customers
WHERE NOT country = 'USA';

Here, the SQL command selects first_name and last_name of all customers where the country is not USA from the Customers table.

Combining Multiple Operators

It is also possible to combine multiple AND, OR and NOT operators in an SQL statement.

For example, let's suppose we want to select customers where country is either USA or UK, and age is less than 26.

-- select customers who live in either USA or UK and whose age is less than 26

SELECT *
FROM Customers
WHERE (country = 'USA' OR country = 'UK') AND age < 26;

Example: Combining Multiple Operators in SQL

Let's look at another example of combining operators.

-- exclude customers who are from the USA and have 'Doe' as their last name

SELECT *
FROM customers
WHERE NOT country = 'USA' AND NOT last_name = 'Doe';

Here, the SQL command selects all customers where the country is not USA and last_name is not Doe from the Customers table.