SQL OR Statement: The Basics

Now and then, you need to create a complex SQL query using multiple statements together. While these query types can be tedious to create, they make your data better suitable for reporting purposes. To understand these query types, you must first have some knowledge of logical operators.

A logical operator allows you to combine two or more statements into one. Also, it lets you control the results based on certain conditions. It makes your queries incredibly powerful tools for creating customized reports of your data. This post will focus specifically on one type: the OR Operator.

The OR operator in SQL can be one of the most confusing parts. Besides, it can impact query effectiveness significantly. This article will explain what the OR operator in SQL does, why it is used, and how you can make sure you get the most out of it. Let’s get started!

What is an SQL Operator?

The original name for this function was SQL UNION. Later the name was altered to avoid confusion with other union-related functions in SQL.

To understand how the SQL OR operator works, we have to first talk about how it handles Boolean logic. In SQL, everything falls into one of two categories: true or false. Boolean logic uses AND to mean true when both statements are true and OR to mean that either statement can be true.

Therefore, your results will include rows where either statement is true. Let’s assume that you are looking for duplicates across multiple columns. In this case, use commas to separate each column rather than OR. Commas indicate that all columns must match.

What is OR for?

When using an OR statement in a SELECT clause, we get rows that match either criteria from the two columns listed. Assume that you want to find out if someone in your database has blue or brown eyes, or likes sushi or steak. The best way to accomplish this would be by using the OR operator.

OR operator example

The standard form of an SQL OR statement is:

SELECT * FROM table WHERE column1=value1 OR column2=value2; 

Using the OR record returns the result it only one of the conditions is met:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

If you need to list more than two columns, continue with another set of OR statements.

AND operator example

You can learn who has blue eyes and likes sushi at the same time. For that, you would use AND, which is probably the most exciting operator of all. Put simply, the AND operator will return the record if both conditions you’ve set out for are true:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

NOT operator example

The NOT operator helps you find out if you need to spend any time searching for people in the database. It may turn out none of them have blue eyes! Thus, using the NOT operator will return the result when the condition(s) are false:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Let’s have a look at the elementary example. The OR statement will return records that meet specific criteria without any other data requirements, such as all students who have either A or B grades:

SELECT * FROM Students WHERE Grade = 'A' or Grade = 'B'

Or, assume that we want to define employees according to several conditions. There should be all employees in department 10. Also, we need to define all employees who earn a commission. Finally, we are looking for all employees in department 20 who earn at most $2,000:

1. select *
2. from emp
3. where deptno = 10
4. or comm is not null
5. or sal <= 2000 and deptno=20

You can use a combination of AND, OR, and parentheses to return rows that satisfy multiple conditions. In the example, the WHERE clause finds rows that:

• The DEPTNO is 10
• The COMM is not NULL
• The salary is $2,000 or less for any employee in DEPTNO 20.

(This example is from the SQL Cookbook: Query Solutions and Techniques for All SQL Users by Anthony Molinaro and Robert de Graaf).

More Details About Using the OR, AND operators

The word OR means at least one condition must be met, but not all. If any condition in an OR statement is TRUE, the entire expression will return as TRUE.

Another example is if the color is red or the color is blue. The SQL statement would return TRUE if one of the colors was red and also if one of the colors was blue.

Thus, the result of the expression will be TRUE if one or more of the conditions are true, and FALSE otherwise.

SQL OR also stands for the opposite. It reverses the logic of SQL AND. Where AND produces TRUE if the conditions are met, OR produces TRUE if at least one condition is met.

Here’s an example of using OR to choose employees in a company who joined in 1990 or 1991:

SELECT
	first_name,
	last_name,
	hire_date
FROM
	employees
WHERE
	YEAR (hire_date) = 1990 OR YEAR (hire_date) = 1991
ORDER BY
	first_name,
	last_name;

The result will return employees who joined in one of these years (their first and last name and hire date).

Combining WHERE, OR Statements

The OR statement is a function that combines two or more conditional statements. Most commonly, we use the OR statement in SQL to execute one of two sets of commands. It depends on whether the expression in parentheses evaluates to true or false.

You can also use it as an operator in other parts of your query, like WHERE clauses and SQL ORDER BY clauses. For example:

SELECT * FROM Product WHERE Category = 'Misc' OR Category = 'Hardware'.

How does OR Influence the Effectiveness of a Query?

The first question is why we need AND/OR operators in SQL. The short answer is that writing complex queries would be extremely difficult without them.

There are two main uses for these logical operators: to combine search criteria within a single statement or to filter records returned by a statement. It depends on how you use them. They may provide added value to your workload or slow down your applications.

OR can be used to test for multiple conditions simultaneously. When it is present in an SQL statement, it may affect the outcome of that statement’s execution.

If an OR condition returns TRUE, any following statements will also return TRUE. It can lead to one long chain of successful results without performing additional tests that would be necessary with only AND used.

For example, here’s a request:

select * from table1 where column1 = 5000

Then we add OR:

select * from table1 where column1 = 5000 OR column1 = 6000

And, we add the following:

select * from table1 where column1 = 5000 OR column1 = 6000 OR column1 = 7000

In other words, OR influences the effectiveness of a query in SQL by allowing multiple terms to match – each term doesn’t need to meet the requirement. However, there is a catch. Using OR can save time when writing queries, but it has performance implications.

An SQL query executes each logical statement sequentially. Any logical statements combined with OR will be executed slower because it’s adding another layer of processing for your database server to complete. As a result, your application or end-user will have to wait longer for their results.

This doesn’t mean you should never use the OR operator in queries. But you need to understand how to use it properly. Otherwise, its drawbacks could outweigh the benefits.

Replacing SQL OR with SQL IN

This nifty little trick allows you to cut down the amount of code and get fewer ORs in your code:

select * from table1 where column1 = 5000 OR column1 = 6000 OR column1 = 7000

Can be simplified:

select * from table1 where column1 IN (5000, 6000, 7000)

Summary

The SQL OR statement is a logical operator commonly used with multiple search conditions to find records that match at least one of those conditions. The keyword OR separates two or more conditions.

We can use the OR statement as an argument to a function, such as SELECT and WHERE. That will allow you to perform operations on sets of data based on what your criteria are. Don’t overdo it with OR if you don’t want slower performances!

OR is one of the most foundational and powerful operators if used correctly. You can use it to search the whole Internet for the things you need, like a better job, valuable resources, beautiful people, and anything else you can imagine.

We hope we could help you see how it works, plain and simple (without Schnorr signatures, gravitational fields with negative mass, and all that). Stay tuned!

Last modified: October 28, 2022