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.

Syntax

The syntax for the SQL AND Condition is:

WHERE condition1
AND condition2
...
AND condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n

All of the conditions that must be met for the records to be selected.

Note

• The SQL AND condition allows you to test 2 or more conditions.
• The SQL AND condition requires that all of the conditions (ie: condition1, condition2, condition_n) be must be met for the record to be included in the result set.

Example - With SELECT Statement

The first SQL AND condition query involves a SELECT statement with 2 conditions.

For example:

SELECT *
FROM suppliers
WHERE city = 'New York'
AND ranking > 5;

This SQL AND example would return all suppliers that reside in New York and have a ranking greater than 5. Because the * is used in the SQL SELECT statement, all fields from the suppliers table would appear in the result set.

Example - JOINING Tables

Our next AND condition example demonstrates how the SQL AND condition can be used to join multiple tables in a SQL statement.

For example:

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
AND suppliers.supplier_name = 'IBM';

Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.

For example:

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'IBM';

This SQL AND condition example would return all rows where the supplier_name is IBM. And the suppliers and orders tables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: orders.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the suppliers and orders tables.

In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the select statement.).

Example - With INSERT Statement

This next AND condition example demonstrates how the SQL AND condition can be used in the INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_name = 'IBM'
AND employees <= 1000;

This SQL AND condition example would insert into the suppliers table, all account_no and name records from the customers table whose customer_name is IBM and have less than or equal to 1000 employees.

Example - With UPDATE Statement

This AND condition example shows how the AND condition can be used in the UPDATE statement.

For example:

UPDATE suppliers
SET supplier_name = 'HP'
WHERE supplier_name = 'IBM'
AND offices = 8;

This SQL AND condition example would update all supplier_name values in the suppliers table to HP where the supplier_name was IBM with 8 offices.

Example - With DELETE Statement

Finally, this last AND condition example demonstrates how the SQL AND condition can be used in the DELETE statement.

For example:

DELETE FROM suppliers
WHERE supplier_name = 'IBM'
AND product = 'PC computers';

This SQL AND condition example would delete all suppliers from the suppliers table whose supplier_name was IBM and product was PC computers.