In a previous tutorial, we learned how to work with the WHERE clause in SQL, which database developers can use when they wish to retrieve a group of records that meet a certain criteria or condition. For instance, a database administrator may wish to find every instance in a database where a user’s name is equal to “Ronnie” – or, more likely, not equal to “Ronnie”. As powerful as the WHERE clause is in SQL, we can make it even stronger when we apply SQL’s NOT, AND, and OR operators.
This database development tutorial shows db admins and programmers how to use the NOT, AND, and OR operators in SQL, complete with syntax, code examples, and use cases.
Before we get started, however, you may wish to review our tutorial: How to Use the SQL WHERE Clause.
The AND operator in SQL is used to display a given record if every condition separated by the AND operator are TRUE. It is used in conjunction with the WHERE clause to filter records based on two or more conditions.
The syntax for the AND operator in SQL is as follows:
For our SQL AND operator code example, let’s pretend we have a database named Nakamura Industries and within that database is a table named employees, which, as you can imagine, hold information pertaining to employee records. Let’s further posit that our table has the following columns:
Let’s say that this table has the following information:
Using our sample table above, to retrieve a record from a database using SQL AND, we would write the following SQL query:
In this example query, we are looking for an employee in the database whose first name is Ronnie and whose last name is Payne. If no record exists that meets both of those requirements, no records will be returned. Since we do, indeed, have a record where both sets of criteria are met, we get the result:
when we execute our query.
We can, of course, query for more than two sets of criteria in SQL using the AND operator. In those cases, every piece of criteria must be met. Here is an example showing a query where three conditions must be met:
In this example SQL query, we are asking the database to return every record that has a first name of Ronnie, a last name of Payne, and an employee ID matching 007.
Keen observers will note that the employee Ronnie Payne has an employee ID of 001; since his employee ID is not 007 – and therefore not all three conditions are met, the query would return an empty result.
If we rewrote our SQL query to reflect the following, it would, instead, return the desired result:
Using this updated SQL query and AND operator, we would get the following output:
Read: Best Online Courses to Learn SQL
Like the SQL AND operator, the OR operator works in conjunction with the WHERE clause to query database tables for information where one or more conditions are TRUE.
Unlike with the AND operator, where all conditions must be TRUE, in scenarios where OR is used, only one condition must evaluate to TRUE.
The syntax for SQL’s OR operator is as follows:
In the following example, we will use the same example database from before, and this time, create a query where we are seeking firstName values that equal either Ted or Larry:
Running the above SQL query will return no results, as table employees contains no firstName records containing Ted or Larry.
Consider this SQL example:
In this example, we set our criteria to search the column firstName for either Ted or Ronnie. Since one of the OR operator’s criteria is met, we get the result:
Like the AND operator, database programmers and database administrators can use the OR operator to retrieve data from a table using multiple criteria. Here is an example of how to use the OR operator with the WHERE clause to query for data based on three sets of criteria:
Here, we are looking for the value Ronnie, Donnie, or Ted – if any of that criteria is met, results will be returned. Below is the output of running this query:
The SQL NOT operator can be a little confusing and take some time to get used to using properly. Instead of using it to find criteria that is TRUE, instead, database programmers use it to find data where a set of criteria is NOT TRUE, which is technically different than FALSE.
Because there are values that are equal to null in SQL, the NOT operator is sometimes used so query databases. The value null is neither TRUE nor FALSE – also known as not 0 or 1. So if you wanted to query a table that had null values in it, you could use the NOT operator to find such values.
Below if the syntax for using the NOT operator in SQL queries:
Of course, the SQL NOT operator can also simply retrieve values from a table that are not equal to TRUE as well. Consider the following example SQL query:
In the above example, we are specifically looking for records that do not contain Ronnie (or, put another way, where the value of firstName is not equal to Ronnie). Since there are two records in firstName that contain the value Ronnie – or the employees Ronnie Payne and Ronnie Donnie – the query will return the results that do not contain those values.
Here would be the output of running the above query:
Finally, it is plausible to use an SQL WHERE clause that used the AND, OR, and NOT in the same statement:
Similarly, you can combine NOT with AND or OR operators too:
Read: Best Courses for Database Administrators
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved

Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.

source