Database Tutorial

Course Tutorial Site

Site Admin

SQL Functions

with 4 comments

This web page describes what a SQL function are, and shows you how to use them. SQL functions are pass-by-value functions. That means you put copies of material into the function as call parameters, the function consumes the original values, and returns a processed output value. In the purest programming sense SQL functions are factories that produce what are known as expressions.

SQL functions come in two varieties, those delivered as part of the database management system engine and those created through procedural languages supported by the same engine. These functions perform many utilities inside SQL statements, like changing and/or formatting one data type into another, performing complex mathematical resolutions (dates), or supplying flow control to a non-procedural language.

This page shows you that process by showing you how to handle null values. Null values are special in SQL. They use what is known as three-value logic, which differs from two-value logic where what isn’t true is false and vice versa. It also shows you how to cast data types from one type to another with SQL functions.

Three-valued logic ↓

Three-value logic is only a conundrum until somebody explains it. There’s a nice explaination that I’ve borrowed from the Oracle Database 11g PL/SQL Workbook. Click on the Three-valued Logic title if you’re interested in understanding it.

“Three-valued logic means basically that if you find something is true when you look for truth, it is true. By the same token, when you check whether something is false and it is, then it is false. The opposite case isn’t proved. That means when something isn’t true, you can’t assume it is false, and vice versa.

The third case is that if something isn’t true, it can be false or null. Likewise, if something isn’t false, it can be true or null. Something is null when a Boolean variable is defined but not declared or when an expression compares something against another variable that is null.”

Null Values ↓

Null values can occur in two principle places in DML statements. They can be in filtering clauses, like the WHERE clause. They can also be returned as potential column values from a query. This section describes how to work with null values in both places.

Null Value Filters ↓

You filter data through the WHERE clause. The WHERE clause provides the ability to compare a column value against another column or literal value by using the = (equal) comparison operator. If the two values are equal the statement resolves to true and when they’re not false.

You can also use the >, >=, <, and <= inequality operators to make relative comparisons of inequality. These also resolve to true or false. On the other hand, It doesn’t support comparing a column value against a null. That’s because a null isn’t equal to anything. Therefore, a comparison with a mathematical operator returns NULL not necessary true or false.

This statement demonstrates a no row return result that you may think supports comparing a value to a null value. Note that the FROM dual clause for Oracle isn’t required in MySQL or SQL Server.

SELECT 'True' FROM dual WHERE 'Something' = NULL;

However, you can see the problem if we test the contrary position that it is false. This statement uses the <> (not equal) operator to test that something isn’t equal to null. It returns no rows too.

SELECT 'True' FROM dual WHERE 'Something' <> NULL;

You might try a combination of the prior two statements to return the 'True' string. It also returns no rows. The question lies in the idea of whether or not something is true in a three-valued logic model. The easy answer is that the comparative statement returns a NULL, which makes the entire statement false.

SELECT 'True' FROM dual WHERE 'Something' = NULL AND 'Something' <> NULL;

The standard math symbols for equality and inequality don’t work. You check whether a column value is null by using the IS or IS NOT operator. It checks whether the reference to see if it points to a data stream or a null value (typically a null terminator).

You use the following syntax to check if a column value contains a null value:

WHERE some_column IS NULL

You use the following syntax to check if a column value doesn’t contain a null value:

WHERE some_column IS NOT NULL

Null Value Functions ↓

There isn’t a convenient way to substitute a value for a null value in a result set, except by calling the implementation dependent function.

Null SQL Functions
OracleMySQLSQL Server

Oracle

The NVL(evaluation_input,substitution_value) function is Oracle’s implementation of a SQL function that allows you to check for and replace the value of a column or literal value. It has two formal parameters. The first is the column or value to evaluate, and the second is the substitution value that you use when a call parameter is null. Note that both parameters must be of the same data type, or the first parameter’s data type must implicitly convert to the second parameter’s data type.

While this works with columns returned from queries, the example uses literals to simplify the size of sample programs. The first use calls the function with a valid string, and a substitution string that is unneeded.

SELECT NVL('Something','Nothing') FROM dual;

This query returns the first call parameter value, Something, because it isn’t a null value. The second use case calls the function with a null, which makes the second parameter needed. This returns the value of the second call parameter.

SELECT NVL(NULL,'Nothing') FROM dual;

The NULL call parameter value is case insensitive and could easily have been typed in lowercase characters. It would produce the same result.

MySQL

The IFNULL(evaluation_input,substitution_value) function is MySQL’s implementation of a SQL function that allows you to check for and replace the value of a column or literal value. It has two formal parameters. The first is the column or value to evaluate, and the second is the substitution value that you use when a call parameter is null. Note that both parameters must be of the same data type, or the first parameter’s data type must implicitly convert to the second parameter’s data type.

While this works with columns returned from queries, the example uses literals to simplify the size of sample programs. The first use calls the function with a valid string, and a substitution string that is unneeded.

SELECT IFNULL('Something','Nothing');

This query returns the first call parameter value, Something, because it isn’t a null value. The second use case calls the function with a null, which makes the second parameter needed. This returns the value of the second call parameter.

SELECT IFNULL(NULL,'Nothing');

The NULL call parameter value is case insensitive and could easily have been typed in lowercase characters. It would produce the same result.

SQL Server

The first critical thing you should understand about Microsoft™ SQL Server is that it dislikes null columns. In fact, the behavior of SQL Server 2008 varies based on internal DBA parameter settings. Some SQL Server DBAs would tell you never design a table with a null allowed column, which is plainly absurd from a practical design standpoint. You have to recognize that DBAs want to control things in a reasonable and consistent way, which is easiet with null values if you disallow them.

If you have ANSI_NULLS set to ON you have a three-valued logic model. On the contrary, when ANSI_NULLS is set to OFF you have a two-valued logic model. In a three-valued logic model, if it’s not true doesn’t mean false but if’s not false doesn’t mean it’s true because both outcomes are unknown. Whereas, it can only be true or false in a two-valued logic model.

The ISNULL(evaluation_input,substitution_value) function is Oracle’s implementation of a SQL function that allows you to check for and replace the value of a column or literal value. It has two formal parameters. The first is the column or value to evaluate, and the second is the substitution value that you use when a call parameter is null. Note that both parameters must be of the same data type, or the first parameter’s data type must implicitly convert to the second parameter’s data type.

While this works with columns returned from queries, the example uses literals to simplify the size of sample programs. The first use calls the function with a valid string, and a substitution string that is unneeded.

SELECT ISNULL('Something','Nothing');

This query returns the first call parameter value, Something, because it isn’t a null value. The second use case calls the function with a null, which makes the second parameter needed. This returns the value of the second call parameter.

SELECT ISNULL(NULL,'Nothing');

The NULL call parameter value is case insensitive and could easily have been typed in lowercase characters. It would produce the same result.

There isn’t a convenient way to substitute a value for a null value in a result set, except by calling the implementation dependent function.

Written by michaelmclaughlin

October 23rd, 2009 at 8:46 am

Posted in

4 Responses to 'SQL Functions'

Subscribe to comments with RSS or TrackBack to 'SQL Functions'.

  1. The beginning of the page makes more sense if one reads the second paragraph first. Once I read that, I could understand what was going on in the first paragraph. The first paragraph talks about functions, but the concept of functions is not delineated until the second paragraph. The description of a concept’s use is useless until after the concept has been defined. That is how I read things, at least. Not sure I’m really qualified to comment…

    Sam Johansen

    4 Feb 10 at 1:22 pm

  2. I concur. It’s switched now.

    michaelmclaughlin

    4 Feb 10 at 8:42 pm

  3. “There isn’t a convenient way to substitute a value for a null value in a result set, except by calling the implementation dependent function.”

    Is COALESCE not an implementation independent function and is there a reason we would not want to use it over the functions you mentioned (NVL, ISNULL, etc.)?

    Ryan D

    17 May 10 at 2:21 pm

  4. The most portable is:

    CASE
  WHEN some_value IS NULL
  THEN another VALUE
  ELSE some_value
END

    The COALESCE function is used to find the first null value in a list of values.

    michaelmclaughlin

    17 May 10 at 2:30 pm

Leave a Reply

Array