Pages

Sunday, September 11, 2011

SQL: "AND" Condition


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.
The syntax for the AND condition is:
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
Our next example 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 = 'IBM';
This 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.).

No comments:

Post a Comment