Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 3.3Using conditions

Part 3.3Using conditions

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.

Feedback 👍
Comments are sent via email to me.