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?](http://devcdn.programiz.com/cdn/farfuture/4CzJVRmCB8Z0oLQHey6MR4QsX7iqt8Ms_CDa-ttX7VY/mtime:1677313382/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?](http://devcdn.programiz.com/cdn/farfuture/XnrOR0T62Q3brj2YgbDfX53CGPMA9AyW-TTy9Svq9R8/mtime:1677313381/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?](http://devcdn.programiz.com/cdn/farfuture/Qc_9wInR7MzvFU5Q7fmJI4eXGCy79LZZZ-BTCB1P3fo/mtime:1677313364/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?](http://devcdn.programiz.com/cdn/farfuture/OTiR_h-slCXJ0mpt9mKQukRfW6Q2UorZGXGP68WChj8/mtime:1677313365/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?](http://devcdn.programiz.com/cdn/farfuture/B18cSzewE3N0Hu1vpkJsB5x7VoeMEYM6N2i7sQ4RjC8/mtime:1677313365/sites/tutorial2program/files/sql-and-not-example.png)
Also Read: