Working with SQL AND, OR, and NOT Operators

SQL Tutorials

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.

What is the AND Operator in SQL?

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.

SQL AND Syntax

The syntax for the AND operator in SQL is as follows:

SELECT columnA, columnB
FROM table_example
WHERE conditionA and conditionB …;

SQL AND Code Example

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:

  • firstName: Holds first name of employee
  • lastName: Holds last name of employee
  • employeeID: Holds unique number representing the employees ID number

Let’s say that this table has the following information:

firstName	lastName	employeeID
Ronnie	        Payne		001
Donnie	        Layne		002
Ronnie	        Donnie	003

How to Use SQL AND to Retrieve a Record

Using our sample table above, to retrieve a record from a database using SQL AND, we would write the following SQL query:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ronnie” AND lastName=”Payne”;

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:

Ronnie Payne 001

when we execute our query.

Querying Multiple AND Criteria in SQL

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:

SELECT fiRead: rstName, lastName, employeeID
FROM employees
WHERE firstName=”Ronnie” AND lastName=”Payne” AND employeeID=007;

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:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ronnie” AND lastName=”Payne” AND employeeID=001;

Using this updated SQL query and AND operator, we would get the following output:

Ronnie Payne 001

Read: Best Online Courses to Learn SQL

What is the SQL OR Operator?

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.

SQL OR Operator Syntax

The syntax for SQL’s OR operator is as follows:

SELECT columnA, columnB
FROM table_example
WHERE conditionA OR conditionB …;

SQL OR Example Code

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:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ted” OR firstName=”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:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ted” OR firstName=”Ronnie”;

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:

Ronnie Payne 001
Ronnie Donnie 003

Using SQL OR to Query for Multiple Criteria

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:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ted” OR firstName=”Ronnie” OR firstName=”Donnie”;

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:

Ronnie	Payne		001
Donnie	Layne		002
Ronnie	Donnie	        003

What is the SQL NOT Operator

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.

SQL NOT Operator Syntax

Below if the syntax for using the NOT operator in SQL queries:

SELECT columnA, columnB
FROM table_example
WHERE NOT conditionA …;

SQL NOT Operator Code Example

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:

SELECT firstName, lastName, employeeID
FROM employees
WHERE NOT firstName=”Ronnie”;

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:

Donnie	Layne		002

SQL Operators: NOT, AND, OR Used Together

Finally, it is plausible to use an SQL WHERE clause that used the AND, OR, and NOT in the same statement:

SELECT firstName, lastName, employeeID
FROM employees
WHERE firstName=”Ronnie” AND lastName=”Payne” OR lastName=”Donnie”;

Similarly, you can combine NOT with AND or OR operators too:

SELECT firstName, lastName, employeeID
FROM employees
WHERE NOT firstName=”Ronnie” AND NOT lastName=”Payne” OR lastName=”Donnie”;

Read: Best Courses for Database Administrators

Ronnie Payne
Ronnie Payne
Ronnie Payne has a background in e-commerce marketing and web development, having acted as a digital marketing consultant and operator of several online stores. He specializes in e-commerce, Internet marketing, content marketing, and web design.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles