How to use SQL OR

The SQL OR operator can optimise your search of records by limiting results to those that match at least one of your conditions. You can combine it with other logical operators like AND and NOT to speed up and improve complex queries.

What is SQL OR?

SQL’s AND, NOT and OR are some of the most important SQL operators for searching for relevant information. Whereas AND finds data that satisfies two or more conditions and NOT defines criteria to be omitted from results, OR allows for more lenient searches. It allows you to define several search criteria, at least one of which must be fulfilled by results. The advantage is that you can get relevant results that don’t necessarily fulfill all the listed criteria.

Since SQL OR is used to formulate search criteria, it’s often used together with SELECT and WHERE. SELECT specifies the records and columns in question and WHERE specifies the search criteria. To make your search query even more precise, you can combine OR with other logical operators like AND and NOT or comparison operators like =.

What’s the difference between SQL OR, AND and NOT?

Here are the differences between SQL OR, AND and NOT:

SQL OR SQL AND SQL NOT
Filters records that satisfy at least one of the conditions Filters data that satisfy all of the conditions Omits information that does not satisfy a condition
Expands the search with different information Narrows the search to a combination of conditions, both of which are met Omits irrelevant information using criteria for exclusion
Tip

No background in SQL? No problem! Our SQL introduction with examples provides a crash course on the basics.

What is the syntax of SQL OR?

The basic syntax of OR looks as follows:

SELECT  *
FROM  Table
WHERE  condition1  OR  condition2  OR …
sql

It uses the following parameters:

  • SELECT: Selects the columns you want to retrieve data from, using primary and foreign keys. You can use an asterisk * to select all columns.
  • FROM: Specifies which table you want to search in.
  • WHERE: Defines search conditions using operators and other parameters.
  • OR: Logical operator used with WHERE to define search criteria.

You can also combine OR with NOT and AND in your query to further narrow your search.

SQL OR combined with AND

In this example, the WHERE clause filters for results where both condition 1 and at least one of conditions 2 or 3 are true.

SELECT  *
FROM  Table
WHERE  condition1 AND (condition2 OR condition3)
sql

SQL OR combined with SQL NOT

You can combine SQL OR with NOT to exclude data that fulfill at least one of the listed conditions.

SELECT  *
FROM  Table
WHERE  NOT  (condition1  OR  condition2)
sql

What can SQL OR be used for?

You can use the SQL OR operator to search complex datasets based on multiple criteria. Here are some use cases:

  • Filtering customer, order and product data based on combined search criteria like product categories, product properties or order and complaint dates
  • Searching for companies in certain industries or regions
  • Filtering suspicious or invalid transactions using multiple conditions
  • Searching for information on employees from a specific department or team
  • Retrieving applicants that don’t have a degree or don’t have work experience
Tip

Every secure, scalable database management system needs a hosting solution suited to its needs. SQL Server Hosting from IONOS offers reliable data protection!

VPS Hosting
Fully virtualised servers with root access
  • Unlimited traffic and up to 1 Gbit/s bandwidth
  • Fast SSD NVMe storage
  • Free Plesk Web Host Edition

Examples of SQL OR

There are countless uses for the OR operator when combined with WHERE, other SQL operators and other SQL functions. Below we’ll look at 3 examples, so you can get a better idea of how OR works.

Searching for orders from certain categories

Let’s say you want to search a customer table for people who have ordered products from either or both of the categories ‘Electronics’ and ‘Hair’. The results will only include records that meet at least one of the two conditions. We’ll use the comparison operator = to specify the categories.

The syntax for that looks as follows:

SELECT  *
FROM  Customers
WHERE  (Category  =  'Electronics'  OR  Category =  'Hair')
sql

Filtering applicants

In this example, there’s a table called ‘Applicants’. We want to find the people in it that have a degree in electrical engineering or IT and have at least three years of work experience. To do this, we’ll combine OR and AND. The search looks as follows:

SELECT  *
FROM  Applicants
WHERE  (Degree  =  'Electrical engineering'  OR  Degree  =  'IT')
AND (Experience  >=  3)
sql

Searching based on region

In our final example, we want to search for customers from France or Germany in a table called ‘Customers’. The syntax for that looks as follows:

SELECT  *
FROM  Customers
WHERE  Country  =  'France'  OR   Country  =  'Germany'
sql

Are there alternatives to SQL OR?

The OR operator provides a simple and fast way to search based on several conditions. These SQL operators serve a similar purpose:

  • CASE: SQL’s CASE expression can also be used with WHERE, THEN and ELSE clauses to check for conditions and narrow down complex analyses to relevant results. It’s evaluated using an ‘IF-THEN-ELSE’ statement and works its way through conditions from bottom to top. The analysis is stopped when one of the conditions is fulfilled.
  • IN: The IN operator can be used in a WHERE clause to examine records. You can define values or strings using IN and then search for matches in tables or columns.
  • EXISTS: SQL’s EXISTS can be used in a table to check whether at least one record from another table matches the first table. If there is at least one match, the result will be added to the target table. This operator is only useful if you’re working with two related tables.
Was this article helpful?