SQL: OR Condition

This SQL tutorial explains how to use the SQL OR condition with syntax and examples.

Description

The SQL OR condition is used to test multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement. Any one of the conditions must be met for a record to be selected.

Syntax

The syntax for the OR condition in SQL is:

WHERE condition1
OR condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n

Multiple conditions that will be tested for each record. Any condition can be met to be included in the result set.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using "OR" Condition with the SELECT Statement

Let's look at an example that shows how to use the OR condition in a SELECT statement to test for multiple conditions where any condition must be met for the records to be selected.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
400	Kimberly-Clark	Irving	Texas
500	Tyson Foods	Springdale	Arkansas
600	SC Johnson	Racine	Wisconsin
700	Dole Food Company	Westlake Village	California
800	Flowers Foods	Thomasville	Georgia
900	Electronic Arts	Redwood City	California

Now let's demonstrate how to use the OR condition to test for 2 conditions. Enter the following SELECT statement:

SELECT *
FROM suppliers
WHERE city = 'Mountain View'
OR supplier_id = 100
ORDER BY supplier_name;

There will be 2 records selected. These are the results that you should see:

supplier_id	supplier_name	city	state
200	Google	Mountain View	California
100	Microsoft	Redmond	Washington

This example would return all suppliers that are in the city of Mountain View or have a supplier_id that is equal to 100. Because the * is used in the SELECT statement, all fields from the suppliers table would appear in the result set.

Example - Using "OR" Condition with the UPDATE Statement

The OR condition can be used in the SQL UPDATE statement to test for multiple conditions.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	google.com

Enter the following UPDATE statement:

UPDATE customers
SET favorite_website = 'google.com'
WHERE customer_id = 5000
OR last_name = 'Reynolds'
OR first_name = 'Paige';

There will be 3 records updated. Select the data from the customers table again:

SELECT * FROM customers;

These are the results that you should see:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	google.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	google.com
8000	Anderson	Paige	google.com
9000	Johnson	Derek	google.com

This example would update all favorite_website values in the customers table to google.com where the customer_id is 5000 or the last_name is Reynolds or the first_name is Paige. As you can see, the favorite_website field in the 2nd, 4th and 5th rows are updated.

Example - Using "OR" Condition with the DELETE Statement

Next, let's see how to use the OR condition in the DELETE statement to test for any of the conditions to be met before a record is deleted.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Enter the following DELETE statement:

DELETE FROM products
WHERE product_name = 'Pear'
OR product_name = 'Apple'
OR category_id = 25;

There will be 3 records deleted. Select the data from the products table again:

SELECT * FROM products;

These are the results that you should see:

product_id	product_name	category_id
2	Banana	50
3	Orange	50
5	Bread	75
7	Kleenex	NULL

This condition example would delete all records from the products table where the product_name is Pear, the product_name is Apple or the category_id = 25.