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.
- Implies that both sides of the condition must be met.
- 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.
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.
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.
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
So a valid query using the WHERE clause combined with AND & OR would be.
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.
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.
blog comments powered by Disqus