SQL WHERE statements select records from a table based on a particular condition. You don’t have to limit your WHERE statement to just one condition. You can specify as many conditions as you want.
That’s where the SQL OR and AND operators come in. The OR statement checks whether one of two conditions is met. The AND operator checks whether two or more conditions are met.
In this guide, we’re going to talk about how to use the SQL AND and OR operators. We’ll walk through an example of each of these operators to help you get started. Without further ado, let’s get started!
What is the SQL AND Operator?
The SQL AND logical operator returns TRUE if two or more conditions evaluate to TRUE.
Take this quiz to get offers and scholarships from top bootcamps and online schools!
See your matchesThe clue is in the name with this operator. Let’s take a look at the syntax:
SELECT column1, column2 FROM table WHERE condition1 AND condition2;
The AND statement is used after the first statement in a WHERE clause. This query will only return records which meet both condition1 and condition2.
You can use the WHERE clause on SELECT, UPDATE, and DELETE statements. This means that you can select, update, and delete records based on multiple conditions with the AND statement.
Let’s use the AND operator on a database. The following database contains information on employees working at a pharmaceutical sales company:
id | name | title | branch | salary |
1 | Thomas Carlton | Sales Representative | Cambridge | 29400 |
2 | Lisa Nelson | Senior Sales Representative | San Francisco | 32000 |
3 | Victoria Carlisle | Sales Director | Cambridge | 37800 |
4 | Michael Jeeves | Senior Sales Representative | San Francisco | 32000 |
5 | Julie Forsythe | Senior Sales Representative | San Francisco | 32000 |
6 | Hallie Peters | Sales Representative | Los Angeles | 29400 |
7 | Rachel Parsons | Sales Representative | San Francisco | 29400 |
We’re going to retrieve a list of employees who work at the San Francisco branch and earn over $30,000 per year. To retrieve records matching this description, we can use the AND operator:
SELECT * FROM employees WHERE branch = 'San Francisco' and salary > 30000;
id | name | title | branch | salary |
2 | Lisa Nelson | Senior Sales Representative | San Francisco | 32000 |
4 | Michael Jeeves | Senior Sales Representative | San Francisco | 32000 |
5 | Julie Forsythe | Senior Sales Representative | San Francisco | 32000 |
Only employees based in San Francisco who earn over $30,000 are returned.
What is the SQL OR Operator?
THE SQL OR logical operator returns TRUE when either of two or more conditions is TRUE.
Like the AND operator, the OR operator is used after a WHERE statement. Let’s say that we want to retrieve a list of all employees who are either Sales Representatives or Senior Sales Representatives. We can do this using the OR operator:
SELECT * FROM employees WHERE title = "Sales Representative" OR title = "Senior Sales Representative";
id | name | title | branch | salary |
1 | Thomas Carlton | Sales Representative | Cambridge | 29400 |
2 | Lisa Nelson | Senior Sales Representative | San Francisco | 32000 |
4 | Michael Jeeves | Senior Sales Representative | San Francisco | 32000 |
5 | Julie Forsythe | Senior Sales Representative | San Francisco | 32000 |
6 | Hallie Peters | Sales Representative | Los Angeles | 29400 |
Five records have been returned. All of the records that have been returned match employees whose title is either Sales Representative or Senior Sales Representative.
Using Both Operators
AND and OR conditions can be combined. This allows you to create more complex SQL queries and commands that evaluate multiple different statements.
We want to retrieve a list of all employees who:
- Are Sales Representatives AND
- Work in Los Angeles or Cambridge.
We can do this using both the AND and OR operators in conjunction:
SELECT * FROM employees WHERE title = 'Sales Representative' AND (branch = 'Los Angeles' OR branch = 'Cambridge');
Let’s run our query and see what happens:
id | name | title | branch | salary |
1 | Thomas Carlton | Sales Representative | Cambridge | 29400 |
6 | Hallie Peters | Sales Representative | Los Angeles | 29400 |
Our query only returns two values. These are the only two records that have the title Sales Representative and who work at either the Los Angeles or Cambridge branches.
Conclusion
The SQL AND operator checks whether two or more conditions are TRUE and returns TRUE if that is the case. The SQL OR operator checks whether one of two or more conditions are TRUE and returns TRUE in that case; otherwise, FALSE is returned.
If you liked this tutorial, check out our guides on SQL INSERT, SQL MIN, and SQL COUNT. Now you’re ready to start using the SQL OR and AND operators like a professional database developer!
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Read more