The SQL that has been shown over the last few articles is pretty generic and has no limitations on what is selected. This article will focus on conditions.
What a condition is
A condition is something that must be met. In SQL, if a condition is not met on a certain record (row of a table), that record will not be included in the result of the query.
The WHERE clause
Queries will almost always use the WHERE keyword. This informs the parser that a condition is about to follow. As
with almost all SQL, because of the way the WHERE keyword is positioned toward the end of a query, any statement
which includes the WHERE keyword will read in a very English like manner.
The following conditions will use this table called 'fruits':
| Product_ID | type | quantity |
|---|---|---|
| 32 | Apple | 10 |
| 45 | Orange | 91 |
| 78 | Apricots | 25 |
| 141 | Pear | 34 |
For instance, assuming a query which is to select the record which contains the information on apples, a pseudo-query could look like:
Select all fruits where type is apple
And then the corresponding SQL query would look very similar:
SELECT * FROM `fruits` WHERE type="Apple"
In terms of efficiency, using a WHERE clause is more efficient than traversing the data with a
server side language such as PHP after receiving all the data.
Combining conditions
Conditions can be combined using logical conjunction and logical disjunction. These are better known as AND and OR.
For the previous table, assuming a query is needed to select apples and pears, the OR keyword is used as
shown below:
SELECT * FROM `fruits` WHERE type="Apple" OR type="Pear"
The following query will find all apples, and pears where the quantity is less than 9.
SELECT * FROM `fruits` WHERE type="Apple" OR type="Pear" AND quantity < 9
| Product_ID | type | quantity |
|---|---|---|
| 32 | Apple | 10 |
But wait, the table returns apples? This is because of the fact that the AND only keyword only check if the pears have a quantity less than 9.
Bracketing expressions
Selections can be bracketed as with mathematics, so for the previous query to work properly where both apples and pears are checked for a quantity of less than 9, a bracketed expression is needed:
SELECT * FROM `fruits` WHERE (type="Apple" OR type="Pear") AND quantity < 9
| Product_ID | type | quantity |
|---|
In this case, the resulting table is empty, which is what is expected.
The reason that the problem occured originally is because the AND keyword represents the logical AND (∧).
The ∧ has precedence over the logical OR (∨), meaning it is evaluated before it.
Bracketing expressions is very important, and knowing where to do this is a very important task when developing more complex queries.
