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
The post Working with SQL AND, OR, and NOT Operators appeared first on Database Journal.
