Code is there to explain the comments to the computer.
—Andy Harris

The WHERE clause in detail

In a SELECT statement such as:

SELECT code, coordinator
FROM course
WHERE coordinator = 'u0012047';

Then the WHERE clause is executed (if present). Only the rows that satisfy the condition in the WHERE are kept. All other rows disappear from memory. Afterwards the SELECT is executed. In this chapter, we will look more closely at some possibilities of WHERE.

Standard comparison operators

SQL provides the standard comparison operators: = (equal to), <> or != (not equal to), > (greater than), < (less than), >= (greater than or equal to) and <= (less than or equal to). You can compare numbers, strings and dates or timestamps. The following query shows all courses that have a start date before April 5, 2017:

SELECT *
FROM course
WHERE start_date < '2017-04-05'; --input date in ISO-format with single quotes

A date that is farther in the past is smaller than a later date, as you would logically expect.

Comparing strings is a bit more involved. Unfortunately the result is database specific. That ‘a’ < ‘b’ is obvious, but what about the comparison of ‘a’ and ‘A’? You know what, we'll just try it out in PostgreSQL:

SELECT 'a' < 'A';

The result is true. We conclude: in PostgreSQL lowercase letters come before uppercase letters. In another DBMS, it may be the other way around.

And what about a comparison between ‘magic’ and ‘monkey’ and between ‘cent’ and ‘century’? Let's test this:

SELECT 'magic' > 'monkey';
SELECT 'cent' > 'century';

The result is twice false. The comparison between two strings is in fact done letter by letter from left to right. For ‘magic’ and ‘monkey’, the first letter is the same, but not the second. The second ‘a’ of ‘magic’ is (alphabetically speaking) smaller than the ‘o’ of ‘monkey’ and so 'magic' < 'monkey'.

The word ‘cent’ is smaller than ‘century’. The letters are compared one at a time. After four matching letters, there is no fifth letter in the string ‘cent’ and so this word is smaller (shorter) than the longer word ‘century’.

IS (NOT) NULL

The value NULL in a field means ‘unknown’: there is no value for this field. It is possible to test for this special NULL value. You just have to remember not to use = but to use IS. The next query shows a list of all courses for which we do not yet know when they will stop:

SELECT *
FROM course
WHERE end_date IS NULL; -- so don't write = NULL

Conversely, the following query returns the list of all courses that do have an end date (and thus are no longer taught or for which it is already known until when these courses will be in the curriculum):

SELECT *
FROM course
WHERE end_date IS NOT NULL;

String patterns with LIKE

Sometimes you don't want to search for the full value of a string, but only for a part of it. Suppose you want a list of all courses that have the word ‘web’ somewhere in the name (does not matter whether ‘web’ is upper or lower case). We already know from a previous chapter that the function lower() will certainly be useful in this query to convert all upper case letters to lower case. But how do we test for the presence of ‘web’ anywhere in the name?

This can be done in different ways (e.g. with regular expressions). Here we look at the simplest way using LIKE and a pattern. In that pattern you can use two special characters:

• %: 0 or more characters;
• _ (underscore): exactly 1 character.

The following query searches all names that contain the substring ‘web’ (upper / lower case does not matter):

SELECT code, name, semester
FROM course
WHERE lower(name) LIKE '%web%';

Now try the following exercises.

... LIKE 'B%'
... LIKE '_e%'
... LIKE '%e%e%e%'
... LIKE '__a_b%'

... LIKE 'B%' --all strings beginning with capital B
... LIKE '_e%' --all strings with an e as second letter
... LIKE '%e%e%e%' --all strings with at least 3 times the letter e
... LIKE '__a_b%' --all strings with a as third and b as fifth letter

SELECT coordinator, name
FROM course
WHERE coordinator like '%4';
-- It's also possible to use substring(), please try this version as well

Retain rows that do NOT satisfy a condition

With a condition in the WHERE clause, we only keep those rows that meet that condition. What if you want all the other rows, so only those that don't satisfy the condition? For this purpose you can use the keyword NOT. The next query returns all courses that do not meet the condition, i.e. those that do not start with the letters ‘Web’:

SELECT code, name, semester
FROM course
WHERE NOT name LIKE 'Web%';

SELECT code, name, credits
FROM course
WHERE NOT name like '%a%'; -- adaptation: NOT lower(name) like '%a%'

Linking conditions with AND and OR

We can link multiple conditions with AND and OR:

• A AND B: only TRUE if both A and B are TRUE;
• A OR B: TRUE if either A or B or both are TRUE.

AND and OR can of course also be used in combination with NOT.

SELECT *
FROM course
WHERE language = 'nl' AND lower(name) LIKE '%prog%';

SELECT *
FROM course
WHERE language = 'nl' OR language = 'fr'; -- we will see how to write this query more concisely later

You can link more than two conditions. You have to be be careful as the following example demonstrates. List all the courses taught in Dutch or French that are still being taught. We try the following query:

SELECT *
FROM course
WHERE language = 'nl' OR language = 'fr' AND end_date IS NULL;

The order of the operators is, from high priority to low:

1. first, comparison operators such as =, <, >, <=, ...,
2. then NOT,
3. then AND,
4. and last but not least OR.

This means that the condition above is not evaluated simply left-to-right but as language = 'nl' OR (language = 'fr' AND end_date IS NULL). You can translate this condition as ‘all Dutch courses (from present or past, doesn't matter) or all French ones that are still being taught’. That's not what we wanted.

The solution is simple: use parentheses. The query as we intended it thus becomes:

SELECT *
FROM course
WHERE (language = 'nl' OR language = 'fr') AND end_date IS NULL;

For complex compound conditions, parentheses are always a good idea because they increase the readability of the condition.

BETWEEN

We begin with a simple exercise:

SELECT code, name, credits
FROM course
WHERE credits >=3 AND credits <=8;

SQL provides a somewhat shorter notation for this type of query (more consistent with common language):

SELECT code, name, credits
FROM course
WHERE credits BETWEEN 3 AND 8; -- both boundaries are included

If you have to write SQL queries on an exam, you may always choose whether you go for the BETWEEN notation or the slightly longer version. You must understand both notations, obviously.

IN

The keyword IN allows you to shorten some conditions. Again, a little exercise to get started:

SELECT code, name, coordinator
FROM course
WHERE coordinator = 'u0012047' OR coordinator = 'u0015584' OR
 coordinator = 'u0024689' OR coordinator = 'u0031447';

A bit cumbersome, isn't it? This query can be shortened with IN:

SELECT code, name, coordinator
FROM course
WHERE coordinator IN ('u0012047','u0015584','u0024689','u0031447');

Overview operators

The following table gives an overview of the operators that you can use in a WHERE clause.

Exercises SQLzoo

SQLzoo is an interactive practice platform. You enter SQL code that the system reviews for you. It's a good idea to try these exercises. This way, you'll learn to work with tables other than the ones we use in this course text.

Table ‘world’

This table contains the following data from countries:

• name
• continent
• area
• population
• gdp