The SQL OR statement returns records who meet one of many specified conditions in a WHERE statement. The AND statement returns records who meet all the specified conditions. Both clauses use the syntax: WHERE condition AND/OR condition…;
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 Statement?
The SQL AND operator returns all the records that meet the conditions linked by the AND operator. To use the AND operator, you must specify at least two conditions in a WHERE statement.
Let’s take a look at the syntax of the AND operator:
SELECT column1, column2 FROM table WHERE condition1 AND condition2;
The AND statement is used after the first statement in an SQL WHERE clause. This query will only return records which meet both condition1 and condition2.
You can use the WHERE clause on SQL SELECT, SQL UPDATE, and SQL DELETE statements. This means that you can select, update, and delete records based on multiple conditions with the AND statement.
AND SQL Example
We have a database that stores information on the employees who work 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 have been asked by the HR department to return a list of the employees who:
- Work at the San Francisco branch.
- Earn at least $30,000 per year.
To retrieve this information, we need to add conditions which relate to two fields in our database. The “branch” column stores the name of the branch at which an employee words. “salary” stores how much each employee earns per year.
To retrieve records matching our 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 |
Our database returns a list of all employees who work in the San Francisco branch and who earn over $30,000 per year.
What is the SQL OR Statement?
The SQL OR operator returns all the records that meet one of a set of specified conditions. You can specify as many conditions as you want with an OR statement. The OR operator comes after a condition in a WHERE clause.
The syntax for the OR SQL operator is the same as the AND operator:
SELECT column FROM table WHERE condition1 OR condition2;
OR SQL Example
Let’s return to the database of employees who work at a pharmaceutical sales company. We have been asked to produce a list of all employees who are either Sales Representatives or Senior Sales Representatives.
These employees need to be notified of the latest compensation changes. To retrieve the records of all of these employees, we could use the following command:
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 |
Our database returns five records. All the records that have been returned match employees whose title is either Sales Representative or Senior Sales Representative.
Using the SQL AND and OR Operators
You can use both the AND and OR SQL operators. This allows you to create more complex SQL queries and commands that evaluate multiple different statements.
We have been asked by the HR department to produce another list of records. We need to retrieve a list of all employees who:
- Are Sales Representatives AND
- Work in Los Angeles or Cambridge.
These employees need to be notified of a new product that is going to be added to their routes. We can find these employees using both the SQL AND and OR operators:
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.
Notice that we enclosed the second condition — whether an employee works at the Los Angeles or Cambridge branch — in parentheses. Without the parenthesis, our database would interpret our statement as:
Select all the records from employees whose title is Sales Representative and who work at the Los Angeles branch. Or, return all records of the employees who work at the Cambridge Branch.
The parenthesis group our two branch statements together. So, we will only see employees who are Sales Representatives and who work at a specified branch.
Conclusion
The SQL AND operator returns the records that meet two or more conditions. An SQL OR operator returns records that meet one of many conditions. Both statements are used in WHERE clauses.
"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"
Venus, Software Engineer at Rockbot
Are you interested in learning more about SQL? Check out our How to Learn SQL guide. This guide contains top tips on how to learn SQL and a list of learning resources to help you advance your SQL knowledge.
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.