47. AND or OR Conditions

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.).

Learn more about joins.

SQL: Combining the "AND" and "OR" Conditions

---

The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement - select, insert, update, or delete.
When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition.

Example #1

The first example that we'll take a look at an example that combines the AND and OR conditions.

SELECT *
FROM suppliers
WHERE (city = 'New York' and name = 'IBM')
or (city = 'Newark');

This would return all suppliers that reside in New York whose name is IBM and all suppliers that reside in Newark. The brackets determine what order the AND and OR conditions are evaluated in.

Example #2

The next example takes a look at a more complex statement.

For example:

SELECT supplier_id
FROM suppliers
WHERE (name = 'IBM')
or (name = 'Hewlett Packard' and city = 'Atlantic City')
or (name = 'Gateway' and status = 'Active' and city = 'Burma');

This SQL statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway, the status is Active, and the city is Burma.