This SQL tutorial explains how to use the AND condition and the OR circumstance collectively in a single query with syntax and examples.
The SQL AND situation and OR condition can be blended to check for more than one stipulations 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 consider each condition. (Just like when you had been studying the order of operations in Math class!)
The syntax for the AND situation and OR condition collectively in SQL is:
WHERE condition1 AND condition2 ... OR condition_n;
Parameters or Arguments
condition1, condition2, … condition_n The conditions that are evaluated to decide if the information will be selected.
The SQL AND & OR conditions permit you to check a couple of conditions. Don’t overlook the order of operation parentheses!
DDL/DML for Examples
If you prefer to follow alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your personal database!
Example – Using the “AND” and “OR” Conditions with the SELECT Statement
Now, let’s appear at an example of how to use the AND circumstance and OR situation collectively in a SELECT statement.
In this example, we have a table referred to as 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 files selected. These are the outcomes that you need to see:
supplier_id supplier_name metropolis 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 country of California however do no longer have a supplier_id equal to 900. The question will additionally return all suppliers whose supplier_id is equal to one hundred The parentheses determine the order that the AND and OR conditions are evaluated. Just like you realized 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 stipulations in an UPDATE statement.
In this example, we have a desk known as clients 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 prerequisites to update documents in a table. Enter the following UPDATE statement:
UPDATE customers SET favorite_website = 'techonthenet.com' WHERE customer_id = 6000 OR (customer_id > 7000 AND last_name <> 'Johnson');
There will be two information updated. Select the facts 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 instance would update all favorite_website values in the clients table to ‘techonthenet.com’ where the customer_id is equal to 6000 as nicely as these documents where the customer_id is increased than 7000 and the last_name is now not equal to ‘Johnson’. As you can see, the favorite_website cost in the third row and fifth row have been updated.
Example – Using the “AND” and “OR” Conditions with the DELETE Statement
Next, let’s appear at how to combine the AND and OR conditions to delete archives using the DELETE statement.
In this example, we have a desk called merchandise 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 three information deleted. Select the statistics from the merchandise 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 information from the products table the place the category_id is equal to 25. It would additionally delete all data from the merchandise desk where the product_id is much less than 4 and the product_name is no longer equal to ‘Banana’.