How to use the AND and OR operators in SQL

Key takeaways

  • The SQL AND operator helps in narrowing down query results by ensuring all specified conditions are true.

  • The SQL OR operator expands the scope by returning results if at least one condition is met.

  • SQL prioritizes AND over OR operator in its evaluations, so grouping conditions with parentheses () ensures the query works as intended.

  • Parentheses are necessary to avoid logical errors when mixing AND and OR operators.

In SQL, the AND and OR operators are used to select data based on multiple conditions. It allows for precise data filtering based on multiple conditions. When used correctly, these operators help extract meaningful data from complex datasets by applying multiple constraints within a query.

In the examples below, we will be using the following table and data where we have a table named Person with columns such as ID, Name, Age, Gender, and State:

-- Creating the table
CREATE TABLE Person (
  ID int,
  Name varchar(100),
  Age int,
  Gender varchar(10),
  State varchar(15)
);
-- Inserting the data
INSERT INTO Person VALUES 
(1, 'Sharon Peller', 16, 'Female', 'Kogi'),
(2, 'Paul Dons', 20, 'Male', 'Lagos'),
(3, 'Ameera Abedayo', 28, 'Female', 'Imo'),
(4, 'Maria Elijah', 25, 'Female', 'Lagos'),
(5, 'David Hassan', 30, 'Male', 'Abuja'),
(6, 'Niniola Disu', 28, 'Female', 'Lagos'),
(7, 'Praise Dominion', 26, 'Female', 'Abuja'),
(8, 'Divine Favour', 29, 'Female', 'Abuja'),
(9, 'Praise Steven', 31, 'Female', 'Lagos'),
(10, 'Joe Smith', 16, 'Male', 'Lagos');

The AND operator

The AND operator ensures all specified conditions must be true for a record to appear in the results. This makes it useful for narrowing down datasets and applying strict filters.

Syntax

The syntax for AND operator in SQL is as follows:

SELECT column_name
FROM table_name
WHERE condition
AND condition1 AND condition2 AND condition3 ...;
Syntax for AND operator in SQL

Code example of AND operator

Let’s imagine a scenario where you need to fetch individuals between 20 and 30 years of age who live in Lagos. This is how you will write an SQL query using the AND operator:

SELECT Name, Age, State 
FROM Person 
WHERE Age >= 20 AND Age <= 30 
AND State = 'Lagos';

Explanation

  • Line 1: We retrieve the name, age, and state of each person from the Person table.

  • Line 2: We specify the table to query the data from.

  • Lines 3–4: We filter records based on the following conditions:

    • Age >= 20 statement ensures a person is at least 20 years old.

    • AND Age <= 30 statement ensures a person is no older than 30.

    • AND State = 'Lagos' statement restricts the results to people who live in Lagos.

Implement the AND operator in a real-world use case in this project, Build a Python Airline Reservation System.

The OR operator

The OR operator broadens search results, returning records if any of the listed conditions are true. This helps when multiple criteria can fulfill the query’s intent.

Syntax

The syntax for OR operator in SQL is as follows:

SELECT column_name
FROM table_name
WHERE condition
OR condition1 OR condition2 OR condition3 ...;
Syntax for OR operator in SQL

Code example of OR operator

Let’s imagine a scenario where you need to retrieve individuals who are either younger than 18 or live in Abuja. This is how you will write an SQL query using the OR operator:

SELECT Name, Age, State 
FROM Person 
WHERE Age < 18 OR State = 'Abuja';

Explanation

  • Line 1: We retrieve the name, age, and state of each person from the Person table.

  • Line 2: We specify the table to query the data from.

  • Line 3: We use the WHERE clause that applies two conditions:

    • Age < 18 returns records where the age is below 18.

    • OR State = 'Abuja' adds records where the state is Abuja, even if they don’t meet the age condition.

Get a hands-on practice with SQL operators with this project, Build a CRUD Application Using Golang and React.

Combining AND and OR operators

When using both AND and OR operators in a query, parentheses, (), are essential to dictate the order of evaluation. SQL evaluates AND operators first unless parentheses explicitly alter the sequence.

Code example

Let’s look at an example where you need to find individuals aged between 20 and 29 who reside in either Lagos or Abuja:

SELECT Name, Age, State 
FROM Person 
WHERE (Age >= 20 AND Age < 30) 
AND (State = 'Lagos' OR State = 'Abuja');

Explanation

  • Line 1: We retrieve the name, age, and state columns.

  • Line 2: We specify the table to query the data from.

  • Lines 3–4: We group conditions using parentheses:

    • (Age >= 20 AND Age < 30) ensures only individuals in their twenties are selected.

    • (State = 'Lagos' OR State = 'Abuja') selects people from either Lagos or Abuja.

    • The AND operator between the two grouped conditions ensures that both age and state conditions must be met.

Practice combining AND and OR operators in an actual project by attempting this project, Create a Chat Application using Angular, Flask, and Socket.IO.

Understanding how to properly use the AND and OR operators in SQL are essential for creating the right queries. These operators form the foundation for building complex queries, allowing you to extract relevant data.

Frequently asked questions

Haven’t found what you were looking for? Contact Us

Can you use AND and OR together in SQL?

Yes, combining these operators allows for complex filtering. However, ensure you use parentheses to group conditions correctly.

What happens if you omit parentheses in an AND-OR query?

SQL evaluates the AND operator before the OR operator by default. Therefore, omitting parentheses in an AND-OR query can lead to incorrect results. Use parentheses to ensure the intended logic is applied.

How can AND and OR operators affect query performance?

Using multiple AND operators may slow down the query due to higher filtering precision, while OR conditions may fetch larger datasets, potentially affecting performance.

Which operator takes precedence, AND or OR?

SQL prioritizes AND over OR unless parentheses explicitly define the order of operations.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)