1.1 List of conditional functions
The SQL language has a list of functions that are somewhat similar to the operator if-elsein switchJava.
There are 4 such functions in total:
| Functions | Description | |
|---|---|---|
| 1 | CASE | analog switch |
| 2 | IF() | An analogue of the ternary operator or if-else |
| 3 | IFNULL() | analogue of if-else |
| 4 | NULLIF() | analogue of if-else |
The last three in SQL are represented as functions, and CASEis a full-fledged operator, so we will consider it separately at the very end.
1.2 IF() function
A function IF()in SQL is somewhat similar to a ternary operator in Java. In SQL language, it takes 3 parameters and looks like this:
IF (condition, true, false)Three expressions must be passed to the function IF:
- a condition that is tested for truth;
- an expression that is returned when the condition is true;
- an expression that is returned when the condition is false.
Below I will give some examples to make it easier to understand how it works:
| Request | Result | |
|---|---|---|
| 1 | SELECT IF( 1>2 , 2, 3) | 3 |
| 2 | SELECT IF( 1<2 , 'yes', 'no') | 'yes' |
| 3 | SELECT IF( STRCMP('test','test1' ), 'no', 'yes') | 'no' |
1.3 Function IFNULL() and NULLIF()
There are also two modifications of the function IF().
The first modification is the function IFNULL(). It only takes two values:
IFNULL (expression 1, expression 2)If expression1 is not equal to NULL, then the function will return expression1 . If expression1 IS NULL, then the function will return expression2 . The essence is the substitution of the default value when the field is equal to NULL.
The second modification of the function IFis the function NULLIF(), it also takes two values:
NULLIF (expression 1, expression 2)It works in the opposite direction:
- if expression1 is equal to expression2 , then the function returns
NULL; - if expressions are not equal, then expression1 is returned .
GO TO FULL VERSION