totn SQL

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!)

subscribe button Subscribe

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:

Try It
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 techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

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

Try It
UPDATE customers
SET favorite_website = 'techonthenet.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 techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha techonthenet.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige techonthenet.com
9000 Johnson Derek techonthenet.com

This example would update all favorite_website values in the customers table to 'techonthenet.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:

Try It
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'.