Filtering with WHERE

Introduction

So far, we have seen how to select (retrieve) data with SELECT and FROM:

SELECT
  *
FROM
  movies;

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 36

• Now we will look at how to retrieve a filtered selection.
• This is a selection that must meet certain conditions/criteria.
• For this, we will use WHERE.

---

1. Filtering on columns with numbers

We will apply different types of filters on columns with numbers.

---

1.1. Numbers equal to a value

With WHERE and the = sign, we can filter where a number is equal to a certain value:

SELECT
  *
FROM
  movies
WHERE
  year = 1992;

id	name	year	rank
111813	Few Good Men, A	1992	7.5
276217	Reservoir Dogs	1992	8.3

Columns: 4
Rows: 2

---

1.2. Numbers greater or less than a value

With >= we can filter on numbers that are greater than or equal to a value.

SELECT
  *
FROM
  movies
WHERE
  year >= 2004;

id	name	year	rank
30959	Batman Begins	2005	(NULL)
124110	Garden State	2004	8.3
176712	Kill Bill: Vol. 2	2004	8.2

Columns: 4
Rows: 3

Similarly, you can use other operators like >, <, >=, <=.

---

1.3. Numbers not equal to a value

With <> we can filter on numbers that are not equal to the specified value.

SELECT
  *
FROM
  movies
WHERE
  year <> 2004;

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 34

---

1.4. Numbers equal to values from a list

With IN we can specify a range of values. We then filter on the occurrence of these values.

SELECT
  *
FROM
  movies
WHERE
  year IN(2004, 2005);

id	name	year	rank
30959	Batman Begins	2005	(NULL)
124110	Garden State	2004	8.3
176712	Kill Bill: Vol. 2	2004	8.2

Columns: 4
Rows: 3

---

1.5. Numbers between 2 values

With BETWEEN and AND we can specify 2 values. We then filter on the occurrence of intermediate values.

Note: Both values you specify are inclusive in the filtering.

SELECT
  *
FROM
  movies
WHERE
  year BETWEEN 2001
  AND 2003;

id	name	year	rank
176711	Kill Bill: Vol. 1	2003	8.4
194874	Lost in Translation	2003	8
224842	Mystic River	2003	8.1
238072	Ocean”s Eleven	2001	7.5
256630	Pirates of the Caribbean	2003	(NULL)
300229	Shrek	2001	8.1
350424	Vanilla Sky	2001	6.9

Columns: 4
Rows: 7

---

1.6. Numbers with missing values

With ISNULL we filter on missing values.

SELECT
  *
FROM
  movies
WHERE
  rank ISNULL;

id	name	year	rank
30959	Batman Begins	2005	(NULL)
256630	Pirates of the Caribbean	2003	(NULL)

Columns: 4
Rows: 2

---

2. Filtering on columns with text

We will apply different types of filters on columns with text.

---

2.1. Text equal to a value

With the = sign, we filter on text equal to a value.

SELECT
  *
FROM
  movies
WHERE
  name = 'Titanic';

id	name	year	rank
333856	Titanic	1997	6.9

Columns: 4
Rows: 1

---

2.2. Text equal to values from a list

With IN we specify a range of texts. We then filter on the occurrence of values from the range.

Note: text is specified with single quotes: '<text>'.

SELECT
  *
FROM
  movies
WHERE
  name IN('Fargo', 'Memento');

id	name	year	rank
109093	Fargo	1996	8.2
210511	Memento	2000	8.7

Columns: 4
Rows: 2

---

2.3. Text matches a pattern: the first letter

With LIKE we specify a pattern. We then filter on matching values with this pattern.

With pattern '<letter>%' we match on the first letter. The % sign is a wildcard, meaning any value that comes after <letter> will give a valid match. Example: - The pattern M% has a match for the values 'Moon' and 'Master' - The pattern Ma% only has a valid match with 'Master' - The pattern M only matches values that are exactly equal to 'M'.

SELECT
  *
FROM
  movies
WHERE
  name LIKE 'M%';

id	name	year	rank
207992	Matrix, The	1999	8.5
210511	Memento	2000	8.7
224842	Mystic River	2003	8.1

Columns: 4
Rows: 3

---

2.4. Text matches a pattern: the last letter

With pattern '%<letter>' we match on the last letter.

SELECT
  *
FROM
  movies
WHERE
  name LIKE '%n';

id	name	year	rank
147603	Hollow Man	2000	5.3
194874	Lost in Translation	2003	8
238072	Ocean”s Eleven	2001	7.5
256630	Pirates of the Caribbean	2003	(NULL)
267038	Pulp Fiction	1994	8.7

Columns: 4
Rows: 5

---

2.5. Text matches a pattern: the last letters

With pattern '%<letters>' we match on the last letters.

SELECT
  *
FROM
  movies
WHERE
  name LIKE '%an';

id	name	year	rank
147603	Hollow Man	2000	5.3
256630	Pirates of the Caribbean	2003	(NULL)

Columns: 4
Rows: 2

---

2.6. Text matches a pattern: the letter at a specific position

With pattern '_<letter>%' we match on the second letter. You can use multiple '_' signs to reach a certain position.

SELECT
  *
FROM
  movies
WHERE
  name LIKE '_a%';

id	name	year	rank
30959	Batman Begins	2005	(NULL)
109093	Fargo	1996	8.2
124110	Garden State	2004	8.3
207992	Matrix, The	1999	8.5
350424	Vanilla Sky	2001	6.9

Columns: 4
Rows: 5

---

2.7. Text matches a pattern: the occurrence of letters

With pattern '%<letters>%' we match on the occurrence of letters.

SELECT
  *
FROM
  movies
WHERE
  name LIKE '%ai%';

id	name	year	rank
192017	Little Mermaid, The	1989	7.3
257264	Planes, Trains & Automobiles	1987	7.2

Columns: 4
Rows: 2

---

3. Use of operators

We have already seen several operators.

Think of: =, >=, BETWEEN, LIKE, AND.

There are 2 types of operators:

1. Comparison operators.
2. Logical operators.

---

3.1. Comparison operators

With comparison operators, you make a comparison with a value.

We have already seen most comparison operators. Here is a complete overview:

Operator	Meaning
= (Equals)	Equal to
> (Greater Than)	Greater than
< (Less Than)	Less than
>= (Greater Than or Equal To)	Greater than or equal to
<= (Less Than or Equal To)	Less than or equal to
<> (Not Equal To)	Not equal to

---

3.2. Logical operators

With logical operators, you make a comparison with logic.

We have already seen some logical operators. Here is a complete overview:

Operator	Meaning
AND	TRUE if 2 Boolean comparisons are also TRUE.
OR	TRUE if one of the Boolean comparisons is TRUE.
NOT	Gives the opposite value of a Boolean operator.
IN	TRUE if it is equal to one or more values from a list.
BETWEEN	TRUE if it lies within a certain range.
LIKE	TRUE if a certain pattern is matched.
ALL	TRUE if all in a set of comparisons are TRUE.
ANY	TRUE if at least 1 in a set of comparisons is TRUE.
SOME	TRUE if some in a set of comparisons are TRUE.
EXISTS	TRUE if a subquery contains rows.

We will look at some examples.

---

3.2.1. Logical operator AND

With operator AND (and) we obtain values that meet 2 other comparisons.

SELECT
  *
FROM
  movies
WHERE
  year >= 2000
  AND rank < 7;

id	name	year	rank
147603	Hollow Man	2000	5.3
350424	Vanilla Sky	2001	6.9

Columns: 4
Rows: 2

---

3.2.2. Logical operator OR

With operator OR (or) we obtain values that meet one of 2 other comparisons.

SELECT
  *
FROM
  movies
WHERE
  year >= 2004
  OR year < 1980;

id	name	year	rank
17173	Animal House	1978	7.5
30959	Batman Begins	2005	(NULL)
124110	Garden State	2004	8.3
130128	Godfather, The	1972	9
176712	Kill Bill: Vol. 2	2004	8.2
313459	Star Wars	1977	8.8

Columns: 4
Rows: 6

---

3.2.3. Logical operator NOT

With operator NOT (not) we obtain values that do not meet a comparison.

SELECT
  *
FROM
  movies
WHERE
  NOT year = 2000;

id	name	year	rank
10920	Aliens	1986	8.2
17173	Animal House	1978	7.5
18979	Apollo 13	1995	7.5
...	...	...	...

Columns: 4
Rows: 32

---

Summary

• With WHERE you can apply a filter.

SELECT
  *
FROM
  movies
WHERE
  year = 1992;

• You use comparison and logical operators for comparisons.
• Comparison operators:

Operator	Meaning
= (Equals)	Equal to
> (Greater Than)	Greater than
< (Less Than)]	Less than
>= (Greater Than or Equal To)	Greater than or equal to
<= (Less Than or Equal To)	Less than or equal to
<> (Not Equal To)	Not equal to

• Example:

SELECT
  *
FROM
  movies
WHERE
  year >= 2004;

• Logical operators:

Operator	Meaning
AND	TRUE if 2 Boolean comparisons are also TRUE.
OR	TRUE if one of the Boolean comparisons is TRUE.
NOT	Gives the opposite value of a Boolean operator.
IN	TRUE if it is equal to one or more values from a list.
BETWEEN	TRUE if it lies within a certain range.
LIKE	TRUE if a certain pattern is matched.
ALL	TRUE if all in a set of comparisons are TRUE.
ANY	TRUE if at least 1 in a set of comparisons is TRUE.
SOME	TRUE if some in a set of comparisons are TRUE.
EXISTS	TRUE if a subquery contains rows.

• Example:

SELECT
  *
FROM
  movies
WHERE
  year >= 2004
  or year < 1980;