## 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 .

GO TO FULL VERSION