Back to articles list January 28, 2021 - 4 minutes read Using AND, OR, and NOT Operators in SQL Andrew Bone Andrew is a technical writer from Dublin, Ireland. He started his career as a full-stack software developer. However, over time, he found himself enjoying writing documentation and user guides more than programming. He is now focused on breaking down complex subjects so that the writing is clear and understandable, regardless of the readers’ technical background. Tags: sql learn sql sql operators WHERE 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. idFirstNameLastNameAgeDateJoinedLocation 1WillSmith522020-08-23Los Angeles 2BradPitt562018-06-15San Francisco 3TomHanks642017-02-27San Diego 4AngelinaJolie452019-04-02Los Angeles 5MerylStreep712014-03-21Manhattan 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'; idFirstNameLastNameAgeDateJoinedLocation 4AngelinaJolie452019-04-02Los 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: idFirstNameLastNameAgeDateJoinedLocation 1WillSmith522020-08-23Los Angeles 3TomHanks642017-02-27San Diego 4AngelinaJolie452019-04-02Los Angeles 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: idFirstNameLastNameAgeDateJoinedLocation 2BradPitt562018-06-15San Francisco 3TomHanks642017-02-27San Diego 5MerylStreep712014-03-21Manhattan 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. idFirstNameLastNameAgeDateJoinedLocation 1WillSmith522020-08-23Los Angeles 2BradPitt562018-06-15San Francisco 3TomHanks642017-02-27San Diego 4AngelinaJolie452019-04-02Los Angeles 5MerylStreep712014-03-21Manhattan 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: idFirstNameLastNameAgeDateJoinedLocation 1WillSmith522020-08-23Los Angeles 5MerylStreep712014-03-21Manhattan 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. Tags: sql learn sql sql operators WHERE You may also like What Is the SQL WHERE Clause? Are you confused about the SQL WHERE clause? Learn what it does and how it can improve your SQL queries. Read more HAVING vs. WHERE in SQL: What You Should Know To take advantage of SQL’s great power, you must understand HAVING vs. WHERE clauses. How do you use them? What are their differences? Read more What SQL Practice Do You Need to Prepare for a Technical Job Interview? Need some SQL practice before a technical job interview? Here are six ways to prepare for various types of database and SQL jobs! Read more Learning SQL? 12 Ways to Practice SQL Online Want to learn SQL or practice SQL online? We’ve rounded up the top ways to practice SQL so you can go from learning to earning! Read more Why Window Functions Are Not Allowed in WHERE Clauses Window functions can only appear in SELECT and ORDER BY but not in WHERE clauses. The reason is the logical order in which SQL queries are processed. Read more Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.