totn SQL

SQL: AND Condition

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

Description

The SQL AND condition (also known as the AND operator) is used to test for two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement. All conditions must 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 conditions that will be tested for each record. All conditions must 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 "AND" Condition with the SELECT Statement

Let's look at an example that shows how to use the AND condition in a SELECT statement to test for 2 conditions that must be met for the records 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 demonstrate 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 record selected. These are the results that you should see:

customer_id last_name first_name favorite_website
9000 Johnson Derek techonthenet.com

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

Example - Using "AND" Condition with the UPDATE Statement

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

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 UPDATE statement:

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

There will be 1 record updated. Select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name city 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 update all supplier_name values in the suppliers table to TBD where the city was Redwood City and the supplier_id was not equal to 900. As you can see, the supplier_name in the third row has been updated.

Example - Using "AND" Condition with the DELETE Statement

Next, let's see how to use the AND condition in the DELETE statement to test for 2 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:

Try It
DELETE FROM products
WHERE category_id = 50
AND product_name <> 'Pear';

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
1 Pear 50
5 Bread 75
6 Sliced Ham 25
7 Kleenex NULL

This example would delete all records from the products table whose category_id is 50 and whose product_name is not Pear.