SQL: Combining the AND and OR Conditions

This SQL tutorial explains how to use the AND condition and the OR condition together in a single query with syntax and examples.

Description

The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)

Syntax

The syntax for the AND condition and OR condition together in SQL is:

WHERE condition1
AND condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n

The conditions that are evaluated to determine if the records will be selected.

Note

• The SQL AND & OR conditions allow you to test multiple conditions.
• Don't forget the order of operation parentheses!

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 the "AND" and "OR" Conditions with the SELECT Statement

Now, let's look at an example of how to use the AND condition and OR condition together in a SELECT statement.

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

Enter the following SQL statement:

SELECT *
FROM suppliers
WHERE (state = 'California' AND supplier_id <> 900)
OR (supplier_id = 100);

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

supplier_id	supplier_name	city	state
100	Microsoft	Redmond	Washington
200	Google	Mountain View	California
300	Oracle	Redwood City	California
700	Dole Food Company	Westlake Village	California

This example would return all suppliers that are in the state of California but do not have a supplier_id equal to 900. The query will also return all suppliers whose supplier_id is equal to 100. The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

Example - Using the "AND" and "OR" Conditions with the UPDATE Statement

Next, let's look at how to use the AND and OR conditions in an UPDATE statement.

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

Now let's demonstrate how to use the AND and OR conditions to update records in a table. Enter the following UPDATE statement:

UPDATE customers
SET favorite_website = 'google.com'
WHERE customer_id = 6000
OR (customer_id > 7000 AND last_name <> 'Johnson');

There will be 2 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	digminecraft.com
6000	Ferguson	Samantha	google.com
7000	Reynolds	Allen	checkyourmath.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 equal to 6000 as well as those records where the customer_id is greater than 7000 and the last_name is not equal to 'Johnson'. As you can see, the favorite_website value in the third row and fifth row have been updated.

Example - Using the "AND" and "OR" Conditions with the DELETE Statement

Next, let's look at how to combine the AND and OR conditions to delete records using the DELETE statement.

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 category_id = 25
OR (product_id < 4 AND product_name <> 'Banana');

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
4	Apple	50
5	Bread	75
7	Kleenex	NULL

This example would delete all records from the products table where the category_id is equal to 25. It would also delete all records from the products table where the product_id is less than 4 and the product_name is not equal to 'Banana'.