Using AND, OR, and NOT Operators in SQL

AND, OR, and NOT are important logical operators in SQL. They help you combine the conditions used to filter records. They are most commonly used in conjunction with the WHERE or HAVING clauses. Furthermore, this topic is a pretty common SQL job interview question.

To help you completely understand these operators, I’ll take you through some examples, fully explaining the syntax and purpose of each. Practicing using examples like these is one of the best methods for learning SQL.

For our examples, imagine we operate and own a gym with different locations around the United States. When people sign up, they give us some basic information. This will be stored in our members table, which contains a unique ID for each member, along with their first and last names, their age, the date they joined the gym, and the location of their gym.

SQL AND Syntax

The syntax for the AND operator is:

SELECT column_1, column_2, … column_n
FROM table
WHERE condition_1 AND condition_2 AND condition_n

When multiple conditions are combined using the AND operator, all rows which meet all of the given conditions will be returned.

Now it’s time to apply the AND operator to our gym scenario. Say we wanted to find all members that are under the age of 50 and live in Los Angeles. How can we accomplish this using a SQL query?

SELECT * 
FROM members 
WHERE Age < 50 AND Location = 'Los Angeles';

Although we have two members with the Location of Los Angeles, only one of those members is under the Age of 50. Remember that when using the SQL AND operator, both conditions must be met in order for a record to be returned.

SQL OR Syntax

The syntax for the OR operator is:

SELECT column_1, column_2, … column_n
FROM table
WHERE condition_1 OR condition_2 OR condition_n

When multiple conditions are combined using the OR operator, all rows which meet any of the given conditions will be returned. We can apply this to our members table by writing the following query:

SELECT * 
FROM members 
WHERE Location = 'Los Angeles' OR LastName = 'Hanks'

With this query, we are looking to retrieve rows where the LastName is Hanks or the Location is Los Angeles. Executing this query yields the following resulst:

Notice how our member Tom Hanks is included in the result set even though his gym Location is not Los Angeles. This is because he meets the second condition we specify – his last name is “Hanks”. Remember: when using the SQL OR operator, all rows which meet any of the given conditions will be returned.

SQL NOT Syntax

The syntax for the NOT operator is:

SELECT column_1, column_2, … column_n
FROM table
WHERE NOT condition 

When multiple conditions are combined using the NOT operator, all rows which do not meet the given conditions will be returned.

We can use this operator with our gym example in a number of useful ways. What if we wanted to find all of our members that were outside of Los Angeles? Using the NOT operator, we can simply write:

SELECT * 
FROM members 
WHERE NOT Location = 'Los Angeles'

Executing this query results in the following data set:

You can clearly see what effect the NOT operator has had. No members that had their Location set to Los Angeles were returned.

Combining the AND, OR, and NOT operators

There are certain situations when you may need to combine these operators in order to specify the exact data you want.

Here is our members table again; it’s the dataset we will be querying for this example.

Now let’s say we want to fetch the data for members that fit very specific criteria. We want members who are:

• Over the age of 50.
• Located in Los Angeles or Manhattan.

We can accomplish this by writing the following SQL query:

SELECT * FROM members
WHERE AGE > 50 AND (Location='Los Angeles' OR Location='Manhattan')

Notice how we used parentheses/brackets to group our operators. We do this so SQL knows exactly how to interpret the order of our operators. The result of this query is:

You can see how combining the AND, OR, and NOT operators in different ways can help us precisely filter our data.

Expand Your SQL with AND, OR, and NOT Operators

You now have the ability to retrieve the exact data you want. This opens up many possibilities.

You can use the WHERE clause in conjunction with AND, OR, and NOT, allowing you to be very precise with your SQL queries. Reading our blog posts is a great way to learn SQL syntax and see the different scenarios where SQL might be useful. However, if you want a truly comprehensive guide to writing queries, consider our SQL Basics course, which will give you lots of hands-on practice.