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 AND Example How to use the AND operator in SQL?](/sites/tutorial2program/files/sql-and-example.png)
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 OR Example How to use the OR operator in SQL?](/sites/tutorial2program/files/sql-or-example.png)
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.
![SQL NOT Example How to use the NOT operator in SQL?](/sites/tutorial2program/files/sql-not-example.png)
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;
![SQL AND and OR Example How to use AND and OR operators together in SQL?](/sites/tutorial2program/files/sql-and-or-example.png)
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.
![SQL AND and NOT Example How to use AND and NOT operators together in SQL?](/sites/tutorial2program/files/sql-and-not-example.png)
Also Read: