Logical Functions
Logical functions are used to manipulate data based on certain conditions.
Table 59
Logical Functions
Function
Description
COALESCE (expression1, expression2 [, ... ] )
Returns the first non-null argument, starting from the left in the expression list.
See also COALESCE for additional details.
IF (predicate, expression1, expression2)
Returns expression1 if predicate is true; otherwise, returns expression2.
NULL ( )
Sets a column as NULL values.
IFNULL (exp, value)
If exp is NULL, value is returned. If exp is not null, exp is returned. The possible data type or types of value must be compatible with the data type of exp.
ISNULL (exp, value)
Replaces NULL with the value specified for value. Exp is the expression to check for NULL. Value is the value returned if exp is NULL. Exp is returned if it is not NULL. The data type of value must be compatible with the data type of exp.
NULLIF (exp1, exp2)
NULLIF returns exp1 if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a NULL value.
Examples
The COALESCE scalar function takes two or more arguments and returns the first non-null argument, starting from the left in the expression list.
select COALESCE(10, 'abc' + 'def')
Ten is treated as a SMALLINT and ResultType (SQL_SMALLINT, SQL_VARCHAR) is SQL_SMALLINT. Hence, the result type is SQL_SMALLINT.
The first parameter is 10, which can be converted to result type SQL_SMALLINT. Therefore, the return value of this example is 10.
============ 
The system scalar functions IF and NULL are SQL extensions.
IF allows you to enter different values depending on whether the condition is true or false. For example, if you want to display a column with logical values as “true” or “false” instead of a binary representation, you would use the following SQL statement:
SELECT IF(logicalcol=1, 'True', 'False')
============ 
The system scalar function NULL allows you to set a column as null values. The syntax is:
NULL()
For example, the following SQL statement inserts a row in the Room table with a NULL value for Capacity:
INSERT INTO Room VALUES (’Young Building’, 222, NULL(), ’Lab’)
============ 
The following example demonstrates how ISNULL returns a value.
CREATE TABLE t8 (c1 INT, c2 CHAR(10))
INSERT INTO t8 VALUES (100, 'string1')
SELECT c1, c2, ISNULL(c1, 1000), ISNULL(C2, 'a string') from t8
The SELECT returns 100 and string1 because both c1 and c2 contain a value, not a NULL.
INSERT INTO t8 VALUES (NULL, NULL)
SELECT c1, c2, ISNULL(c1, 1000), ISNULL(C2, 'a string') from t8
The SELECT returns 1000 and a string because both c1 and c2 contain a NULL.
============ 
The following statements demonstrate the IFNULL and NULLIF scalar functions. You use these functions when you want to do certain value substitution based on the presence or absence of NULLs and on equality.
CREATE TABLE Demo (col1 CHAR(3))
INSERT INTO Demo VALUES ('abc')
INSERT INTO Demo VALUES (NULL)
INSERT INTO Demo VALUES ('xyz')
Since the second row contains the NULL value, 'foo' is substituted in its place.
SELECT IFNULL(col1, 'foo') FROM Demo
This results in three rows fetched from one column:
"abc"
"foo"
"xyz"
3 rows fetched from 1 column.
The first row contains ‘abc,’ which matches the second argument of the following NULLIF call.
SELECT NULLIF(col1, 'abc') FROM Demo
A NULL is returned in its place:
<Null>
<Null>
"xyz"
3 rows fetched from 1 column.