Wednesday, January 9, 2013

Combining the "AND" and "OR" Conditions


The SQL "AND" condition and SQL "OR" condition can be combined in a single SQL statement. It can be used in any valid SQL statement - SQL SELECT statement, SQL INSERT statement,SQL UPDATE statement, or SQL DELETE statement.
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 SQL "AND" and SQL "OR" conditions.
SELECT *
FROM suppliers
WHERE (city = 'New York' and name = 'IBM')
or (city = 'Newark');
This SQL SELECT statement 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 SELECT 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.