The AND
condition allows you to create an SQL statement based on 2 or more conditions
being met. It can be used in any valid SQL statement - select, insert, update,
or delete.
Syntax:
SELECT columns
FROM tables
WHERE column1 = 'value1'
and column2 =
'value2';
The AND condition requires that
each condition be must be met for the record to be included in the result set.
In this case, column1 has to equal 'value1' and column2 has to equal 'value2'.
Example #1:
The first example that we'll take a look at
involves a very simple example using the AND condition.
SELECT *
FROM suppliers
WHERE city =
'New York'
and type = 'PC
Manufacturer';
This would return all suppliers
that reside in New York and are PC Manufacturers. Because the * is used in the
select, all fields from the supplier table would appear in the result set.
Example #2:
Example #2
demonstrates how the AND condition can be used to "join" multiple
tables in an SQL statement.
SELECT orders.order_id
,
suppliers.supplier_name
FROM suppliers
, orders
WHERE
suppliers.supplier_id = orders.supplier_id
and
suppliers.supplier_name = 'ABC';
This would
return all rows where the supplier_name is ABC. 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.).