Basic about AND Condition – using and example

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

Description

The SQL AND situation (also acknowledged as the AND operator) is used to test for two or extra prerequisites in a SELECT, INSERT, UPDATE, or DELETE statement. All prerequisites should be met for a record to be selected.

Syntax

The syntax for the AND condition in SQL is:

WHERE condition1
AND condition2
...
AND condition_n;

Parameters or Arguments

condition1, condition2, … condition_n Multiple stipulations that will be examined for each record. All stipulations need to be met to be blanketed in the result set.

DDL/DML for Examples

If you prefer to observe alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then strive the examples in your own database!

Get DDL/DML

Example – Using “AND” Condition with the SELECT Statement

Let’s seem at an instance that shows how to use the AND situation in a SELECT assertion to check for two stipulations that need to be met for the data to be selected.

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 display how to use the AND condition. Enter the following SELECT statement:

Try It

SELECT *
FROM customers
WHERE favorite_website = 'techonthenet.com'
AND customer_id > 6000
ORDER BY last_name;

There will be 1 file selected. These are the effects that you have to see:

customer_id last_name first_name favorite_website 9000 Johnson Derek techonthenet.com

This example would return all clients whose favorite_website is techonthenet.com and the customer_id is larger than 6000. Because the * is used in the SQL SELECT statement, all fields from the clients table would appear in the end result set.

Example – Using “AND” Condition with the UPDATE Statement

Now, let’s seem at an example of how to use the AND condition in an UPDATE statement. This will take a look at for multiple stipulations to be met before a record is updated.

In this example, we have a desk referred to as suppliers with the following data:

supplier_id supplier_name town 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 UPDATE statement:

Try It

UPDATE suppliers
SET supplier_name = 'TBD'
WHERE city = 'Redwood City'
AND supplier_id <> 900;

There will be 1 document updated. Select the statistics from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name metropolis state 100 Microsoft Redmond Washington 200 Google Mountain View California 300 TBD 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

This example would replace all supplier_name values in the suppliers desk to TBD where the metropolis used to be Redwood City and the supplier_id was not equal to 900. As you can see, the supplier_name in the 0.33 row has been updated.

Example – Using “AND” Condition with the DELETE Statement

Next, let’s see how to use the AND circumstance in the DELETE statement to check for two conditions to be met earlier than a report is deleted.

In this example, we have a table referred to as 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 = 50
AND product_name <> 'Pear';

There will be 3 records deleted. Select the information from the merchandise table again:

SELECT * FROM products;

These are the results that you should see:

product_id product_name category_id 1 Pear 50 5 Bread 75 6 Sliced Ham 25 7 Kleenex NULL

This instance would delete all documents from the merchandise table whose category_id is 50 and whose product_name is no longer Pear.