MySQL: AND Condition

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

Description

The MySQL AND Condition (also called the AND Operator) is used to test two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the AND Condition in MySQL 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 MySQL AND condition allows you to test 2 or more conditions.
• The MySQL 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

Let's look at some examples that show how to use the AND condition in MySQL.

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

For example:

SELECT *
FROM contacts
WHERE state = 'California'
AND contact_id > 3000;

This MySQL AND example would return all contacts that reside in the state of California and have a customer_id greater than 3000. Because the * is used in the SELECT statement, all fields from the contacts table would appear in the result set.

Example - JOINING Tables

Our next MySQL AND example shows how the AND condition can be used to join multiple tables in a SELECT statement.

For example:

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

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 = 'Dell';

This MySQL AND condition example would return all rows where the supplier_name is Dell. 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 the 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 MySQL AND example demonstrates how the AND condition can be used in the INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT customer_id, customer_name
FROM customers
WHERE customer_name = 'Oracle'
AND customer_id  2500;

This MySQL AND condition example would insert into the suppliers table, all customer_id and customer_name records from the customers table whose customer_name is Oracle and have a customer_id less than 2500.

Example - With UPDATE Statement

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

For example:

UPDATE suppliers
SET supplier_name = 'Cisco'
WHERE supplier_name = 'Sun Microsystems'
AND offices = 10;

This MySQL AND condition example would update all supplier_name values in the suppliers table to Cisco where the supplier_name was Sun Microsystems and had 10 offices.

Example - With DELETE Statement

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

For example:

DELETE FROM suppliers
WHERE supplier_name = 'Gateway'
AND product = 'computers';

This MySQL AND condition example would delete all records from the suppliers table whose supplier_name was Gateway and product was computers.

Learn more about joining tables in MySQL.