Comparison functions return TRUE or FALSE.

Comparison functions are used as arguments with either numeric or string expressions. String constants must be enclosed in single or double quotes. The expressions can be literal or dynamically fetched by a query. Comparison functions can be used in most query clauses, but are most often used as filtering conditions in WHERE clauses.

Function: expr1 = expr2

This function checks for equality, it returns TRUE if the two expressions are equal, and FALSE if not.

SELECT 1=2, 'q'='q'
|
-->  false, true

Function: expr1 != expr2 | expr1 <> expr2

These functions check for inequality. They return TRUE if the two expressions are not equal, and FALSE if they are.

SELECT 1!=2, 'q'<>'q'
|
-->  true, false

Function: expr1 > expr2

This function checks that the expression on the left is greater than the one on the right. The function returns TRUE if the left side is greater than the right side, and FALSE if not.

SELECT 1 > 2, 2 > 1
|
-->  false, true

Function: expr1 < expr2

This function checks that the expression on the left is less than the expression on the right. The function returns TRUE if the left side is less than the right side, and FALSE if not.

SELECT 1 < 2, 2 < 1
|
-->  true, false

Function: expr1 >= expr2

This function checks that the expression on the left is greater than or equal to the one on the right. The function returns TRUE if the left side is greater than or equal to the right side, and FALSE if not.

SELECT 1 >= 1, 1 >= 2, 1 >= 0
|
-->  true, false, true

Function: expr1 <= expr2

This function checks that the expression on the left is less than or equal to the one on the right. The function returns TRUE if the left side is less than or equal to the right side, and FALSE if not.

SELECT 1 <= 1, 1 <= 2, 1 <= 0
|
-->  true, true, false

Function: expr IS NULL

This function checks if the expression evaluates to NULL. It returns TRUE if NULL, and FALSE if not.

SELECT 1 is NULL, 1 is not NULL
|
-->  false, true

Function: expr IN(expr1, expr2, …)

This function checks if the expression value is found in a list of values. If it is found, it returns TRUE, otherwise it returns FALSE. The list must contain constants or literals. In order to evaluate correctly, it must be the same data type as the expression.

SELECT 'adam' in ('ruth', 'bill', 'adam'), 'bob' in ('ruth', 'bill', 'adam'), 1 in (1,2,3,4,5,6,7,8,9)
|
-->  true, false, true

Function: if(condition, true_return, false_return)

This function returns either val_true or val_false, depending on whether the condition is true or false. The return values can be literals or field-derived values, but they must be the same data type. Field-derived values do not need to be included in the SELECT clause.

SELECT if(user is not NULL, user, 'Unknown user')

Function: IFNULL(expr, null_default)

This function offers a way to handle NULLs. If the expression is not NULL it is returned, if it is NULL then the value in null_default is returned. The expression and the default value must be the same type.

SELECT ifnull( null, true), ifnull(1,999)
|
-->  true, 1