SQL ‘Where’ Clause

The ‘where’ clause in an SQL statement is used to restrict the data returned in a ‘select’ statement. To accomplish this, the operators, discussed in the previous section, are utilised. Using the previous example of a ‘person’ table, a ‘where’ clause can be used to return records meeting criteria such as a ‘lastname’ of ‘Bloggs’ or a date of birth prior to ‘1 January 1971’. The ‘where’ clause comes after the ‘from’ clause, but before the ‘order by’ clause, if there is one.

SELECT column_name1, column_name2, column_name3 
FROM table_name
WHERE condition
ORDER BY column_name1, column_name2;

The following example uses the equality operator (=) to select only those records where the ‘lastname’ in the ‘person’ table is equal to ‘Bloggs’, whilst ordering the results by ‘lastname’ and ‘firstname’.

SELECT id, firstname, lastname, title, dob
FROM person
WHERE lastname = 'Bloggs'
ORDER BY lastname, firstname;

Only two records from the original four will be returned because there are two occurrences of the ‘lastname’ ‘Bloggs’.

id firstname lastname title dob
1 Fred Bloggs Mr 1980-05-05
3 Freida Bloggs Mrs 1970-10-12

If it were necessary to search for more than one value in a column, then the ‘in’ operator could be used. Here records with a ‘lastname’ of ‘Bloggs’ and ‘Smith’ are returned.

SELECT id, firstname, lastname, title, dob
FROM person
WHERE lastname IN ('Bloggs', 'Smith')
ORDER BY lastname, firstname;
id firstname lastname title dob
1 Fred Bloggs Mr 1980-05-05
3 Freida Bloggs Mrs 1970-10-12
2 Simon Smith Mr 1960-04-01

As well as looking for an exact match, as in the above examples, the ‘like’ operator can be used in conjunction with a wild card, such as ‘%’, to bring back records where a partial value is matched. The ‘%’ operator can be used to represent one or more characters at the start, end, or somewhere in the middle of a condition. If it were necessary to return all records where the ‘firstname’ began with ‘Fr’, then the ‘%’ could be placed after the ‘Fr’ in the condition. The result of this would bring back the same two records as in the first example above.

SELECT id, firstname, lastname, title, dob
FROM person
WHERE firstname LIKE 'Fr%'
ORDER BY lastname, firstname;

Similarly the ‘%’ operator, in conjunction with ‘like’, can be used to bring back, for example, all records where the ‘lastname’ ends with ‘ggs’. This again returns the same two records as above.

SELECT id, firstname, lastname, title, dob
FROM person
WHERE lastname LIKE '%ggs'
ORDER BY lastname, firstname;

Finally, a ‘%’ can be used at the start and end of a condition to, for example, return all records where the ‘lastname’ contains ‘log’, which will once more return the same two records as above.

SELECT id, firstname, lastname, title, dob
FROM person
WHERE lastname LIKE '%log%'
ORDER BY lastname, firstname;

Relational operators, such as less than (<) or greater than (>), can be used to compare one value with another. In the case of the ‘person’ table, all those records with a date of birth less than ‘1 January 1971’ could be returned.

SELECT id, firstname, lastname, title, dob
FROM person
WHERE dob < '1971-01-01'
ORDER BY lastname, firstname;

This would result in two records being returned.

id firstname lastname title dob
3 Freida Bloggs Mrs 1970-10-12
2 Simon Smith Mr 1960-04-01

Using the logical operators ‘and’ and ‘or’ it is possible to check for multiple conditions in the one ‘select’ statement, for example, select all the records where the ‘firstname’ equals ‘Fiona’ or the ‘lastname’ equals ‘Bloggs’.

SELECT id, firstname, lastname, title, dob
FROM person
WHERE firstname = 'Fiona' OR lastname = 'Bloggs'
ORDER BY lastname, firstname;
id firstname lastname title dob
1 Fred Bloggs Mr 1980-05-05
3 Freida Bloggs Mrs 1970-10-12
4 Fiona Jones Miss 1985-05-19

This is just a small selection of what is possible in the ‘where’ clause with the operators that are available.