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 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'
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.
|=||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|
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 NOT||Alternative spelling|
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.