Specific use of logical functions in mysql

Logical functions in MySQL allow you to judge and select data based on conditions. The following are detailed introductions and examples of some commonly used logical functions:

IF(expr1, expr2, expr3)

ifexpr1is true (non-zero and non-NULL),IF()Function returnsexpr2, otherwise returnexpr3。

SELECT IF(1 0, 'true', 'false'); -- result: 'true'

CASE

CASEThere are two formats of functions: simpleCASEand searchCASEFunction. They both allow selection in conditional statements.

Simple CASE function

Used when there is an expression that needs to be compared with a series of values.

SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' ELSE 'other'END; 
-- result: 'two'

Search for CASE functions

Used when judgments are required based on multiple conditions.

SELECT CASE WHEN 1 0 THEN 'true' WHEN 2 < 1 THEN 'false' ELSE 'unknown'END; 
-- result: 'true'

COALESCE(expr1, expr2, ...)

Returns the first non-NULL value in the parameter list.

SELECT COALESCE(NULL, NULL, 'first non-null', 'second non-null'); 
-- result: 'first non-null'

NULLIF(expr1, expr2)

ifexpr1Equal toexpr2, return NULL, otherwise returnexpr1。

SELECT NULLIF(1, 1); -- result: NULL 
SELECT NULLIF(1, 2); -- result: 1

IFNULL(expr1, expr2)

ifexpr1Not NULL, returnexpr1, otherwise returnexpr2。

SELECT IFNULL(NULL, 'fallback'); -- result: 'fallback' 
SELECT IFNULL('not null', 'fallback'); -- result: 'not null'

These logic functions are very useful in SQL queries, especially when you need to process or transform the data based on certain conditions. They can be used directly in SELECT statements or in combination with other functions and operations to meet more complex data processing needs.

This is the article about the specific use of logical functions in mysql. For more related content of mysql logic functions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!