A conditional expression, mainly appearing in clauses such as WHERE, HAVING and CASE, is used to judge whether a value satisfies a certain condition. Here are its most commonly seen types:
1） Comparison expression
|
Syntax |
Description |
|
= |
Equality |
|
<> |
Inequality |
|
>、<、>=、<= |
Greater than, less than, greater than or equal to, less than or equal to |
|
BETWEEN ... AND ... |
Judge whether a value is within a range or not (boundary values are included) |
|
IN (Value1, Value2, ...) |
Set member matching |
|
LIKE |
Fuzzy matching, where % matches any characters and _ matches a single character |
|
IS NULL 、 IS NOT NULL |
Judge whether a specified expression is null or not. |
2） Logical expression
|
Syntax |
Description |
|
AND |
Logical AND operator returns true if and only if both of its operands are true |
|
OR |
Logical OR operator returns true if at least one of the operands is true |
|
NOT |
Logical NOT operator inverts the truth value of a logical expression |
3） CASE conditional expression
Syntax:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE default_result
END
Example:
SELECT
EID, Dept ,
CASE Bools
WHEN 0 THEN 'unmarried man'
WHEN 1 THEN 'married man'
WHEN 2 THEN 'unmarried woman'
ELSE 'married woman'
END
AS "Gender and Marital Status"
FROM
EMPS