BASEWEBMASTER

Brought To You By
Tomlin Ventures Ltd

AND & OR

Using the SQL AND & OR clauses

When using the WHERE clause it is often the case that you want to return rows under multiple conditions. The AND clause and OR clause can be used to separate the different conditions of the statement and how they must relate to one another.

AND
Implies that both sides of the condition must be met.
OR
Implies that either side of the condition may be met.

The AND & OR clause will occur within the WHERE clause of the query, and it will look something like this.

SQL
SELECT * FROM table_name 
WHERE column_one = 'parameter_one' AND column_two < 'parameter_two'

You are not limited by the number of AND & OR clauses that you use.

Using Parentheses

If you require a more complex query it is possible to surround particular parts of the query with rounded brackets or parentheses ( ). The parentheses function by grouping the enclosed expressions so they can be thought of as a single declaration. An example of this would be.

SQL
SELECT * FROM table_name 
WHERE col_one = 'para_one' AND (col_two = 'para_two' OR col_three = 'para_three')

So in the above example we first use an AND meaning that both side must be met in order for a row to be returned. There are two declarations within the rounded brackets, separated by an OR clause meaning that if either one were met the bracketed portion of this query will be met.

It is by using a combination of AND & OR along with parentheses that complex query can be built.

WHERE with AND OR Example

customers
id name age gender
001 zoe 16 female
002 bob 34 male
003 ann 18 female
004 amy 39 female
005 tim 24 male

So a valid query using the WHERE clause combined with AND & OR would be.

SQL
SELECT name, age FROM users_table 
WHERE age >= 18 AND (gender = 'female' OR name = 'bob') 
ORDER BY name ASC

And the results would look like this.

Results
id name age gender
004 amy 39 female
003 ann 18 female
002 bob 34 male

Here the row is returned only if the value of the age column is greater than or equal to 18 and gender is female or name is bob the return rows are also ordered by name in alphabetical order.

Share to Facebook Share to Twitter Digg This Stumble It Share to Reddit Share to Delicious Technorati LinkedIn Google Buzz Blogger More...

blog comments powered by Disqus