1.1 List of conditional functions
The SQL language has a list of functions that are somewhat similar to the operator if-else
in switch
Java.
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 CASE
is 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 IF
is 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 .