Filtering the data with WHERE

If we do not need all the data from a table, we can restrict the set of records using logical conditions called predicates . The syntax for this is very simple:

SELECT <Feldliste>
  FROM <Tabelle>
 WHERE <Prädikat>

The results of the predicates can be combined with the logical operators AND, NOT and OR . In a query with WHERE clause, only the records for which the WHERE condition takes the logical value TRUE are output.

In the following we always talk about the logical values TRUE and FALSE. Here TRUE means that a predicate for a line is evaluated to a logically true value. The opposite of TRUE is FALSE.

Comparison predicates

Comparison predicates are most commonly used in the WHERE clause. That is, two things are compared with a relational operator. Usually a column with a fixed value. Typical example would be:

WHERE name = 'Brandeis'

or

WHERE id < 20

In fact, you can also compare much more. For example, two columns or calculated values. But these are topics for the advanced courses.

The operators < and > are compared in the logic of the respective data type. If, for example, integers (data type INTEGER) are compared, then 2 comes before 10, but if strings (data type NVARCHAR or VARCHAR) are compared, then ‘2’ comes after ’10’, because the individual characters are compared from left to right.

OperatorDescription
=is exactly the same
<is smaller than
>is greater than
<> or !=is unlike
<= is less than or equal to
>= is greater than or equal to
List of comparison operators

Search for patterns with the LIKE predicate

The LIKE operator can be used to make comparisons with patterns. These patterns can contain placeholders. The percent sign % stands for one or more arbitrary characters and the underscore _ for exactly one arbitrary character.

For example, in the console on the left, we see a query that finds all first names (column FIRSTNAME) from the USERS user table (table USERS) that begin with A.

Quantity comparisons with the IN predicate

If a column is to be compared with several values, this can be achieved with the IN predicate. For example, we can get all tasks in status 1, 2 or 3 with the following query:

SELECT id, 
       title, 
       status
  FROM tasks
 WHERE status IN (1, 2, 3)

Logical linking of predicates with AND, OR and NOT

Negate with NOT

By prefixing the logical operator NOT , any predicate can be negated. However, you can also put the NOT in front of the corresponding keyword, which is often easier to read.

Combine with AND and OR

The two logical operators AND and OR can be used to link two or more predicates.

The result of AND operations always has the logical value TRUE if all the predicates involved evaluate to the value TRUE .

The result of OR links is TRUE if at least one of the involved predicates evaluates to the value TRUE .

If both AND and OR are used in a condition, a bracket should always be set to make the order of evaluation transparent.

Negation with preceding NOTAlternative spelling
WHERE NOT id > 10
WHERE NOT status IN (1, 2, 3)WHERE status NOT IN (1, 2, 3)
WHERE NOT name LIKE 'A%'WHERE name NOT LIKE 'A%'
Negation with NOT

Outlook

This lesson gives only the most important predicates and of these only the simple versions. With advanced techniques, you gain much more possibilities. Here is a list of other items that are part of the filtering:

  • Filtering through the INNER JOIN – This is shown in the corresponding lesson.
  • Filtering on NULL values with the IS NULL predicate – This will be shown later in the lesson on NULL.
  • The EXISTS predicate in subqueries
  • Search for complex patterns with regular expressions
  • Subqueries in the IN predicate
  • Filtering on aggregated data with the HAVING clause
  • Filtering before the JOIN with predicates in the ON clause
  • The logical value UNKNOWN is handled in conjunction with the NULL value.