SQL Functions   «Prev  Next»
Lesson 2 Function overview
Objective Understand Issues related to the Use of SQL Functions

SQL Function Overview

While SQL (Structured Query Language) functions significantly enhance the capabilities of data manipulation and analysis within databases, their usage is not without challenges. The following are some of the difficulties associated with the use of SQL functions:
  1. Performance Overhead: SQL functions, especially aggregate and complex functions, can introduce significant performance overhead. When operating on large datasets, these functions can slow down query execution time due to the computation they need to perform.
  2. Null Handling: SQL functions handle NULL values in different ways. For instance, aggregate functions like SUM() and AVG() ignore NULL values in their calculations. However, if not considered, this behavior could lead to incorrect results or misinterpretations of data.
  3. Complexity with Nested Functions: SQL allows for functions to be nested within other functions, which can lead to complex and difficult-to-read queries. Understanding and debugging these queries can be challenging.
  4. Data Type Issues: SQL functions often expect data of specific types as input. If the data type of the input does not match the expected data type, it can lead to errors or unexpected results. For instance, attempting to use AVG() on a text data column would yield an error.
  5. Data Precision: Some functions, like division or mathematical operations involving floating-point numbers, can suffer from precision issues, resulting in slight inaccuracies in calculations.
  6. Grouping Issues: When using aggregate functions like SUM(), AVG(), MIN(), MAX(), etc., with the GROUP BY clause, understanding how data grouping works and correctly applying the clause can be a challenge for inexperienced users.
  7. Time Zone Considerations: Date and time functions can be particularly tricky due to variations in time zones. Developers must ensure that they are working in the correct time zone when using these functions, or the results could be inaccurate.
  8. Dependence on SQL Flavors: Different SQL-based DBMS (Database Management Systems) may implement SQL functions differently. A function in MySQL may behave differently or might not even exist in PostgreSQL, Oracle, or SQL Server.

While SQL functions are a powerful tool for data analysis and manipulation, they should be used with an understanding of the potential challenges. Appropriate data handling techniques, performance optimization strategies, thorough testing, and an understanding of the nuances of SQL functions are crucial to leveraging their capabilities effectively.
When you use functions, it is not likely that you will just be adding values together. What you will be doing, however, is using the built-in functions to perform more helpful work with your database table values. You might recall from the discussion of views that there are cases in which a view is updateable and cases in which it is read-only. If you create a view that includes a function, the view will not be updateable. Since the value returned from the view is actually a formula result, it is not possible to update the values that make up the result. This will force your view to be read-only if you use a function. Also, when you have a function return a value, there will, by default, be no column header for that value. Remember, you can name the column by indicating the column name in your query:

SELECT 'ColumnName'=Function...

Here is an example:
SELECT 'Name'=au_Fname + '' + au_Lname 
FROM Authors

This results in a derived column, Name, that will contain the results from adding the two columns together.

aggregate function count()

The aggregate function count() counts the number of rows in each group, and the asterisk tells the server to count everything in the group. Using the combination of a group by clause and the count() aggregate function, you are able to generate exactly the data needed to answer the business question without having to look at the raw data. When grouping data, you may need to filter out undesired data from your result set based on groups of data rather than based on the raw data. Since the group by clause runs after the where clause has been evaluated, you cannot add filter conditions to your where clause for this purpose. For example, here is an attempt to filter out any cases where an employee has opened fewer than five accounts:

mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> WHERE COUNT(*) > 4
-> GROUP BY open_emp_id;
ERROR 1111 (HY000): Invalid use of group function

You cannot refer to the aggregate function count(*) in your where clause, because the groups have not yet been generated at the time the where clause is evaluated. Instead, you must put your group filter conditions in the having clause. Here's what the query would look like using having:

mysql> SELECT open_emp_id, COUNT(*) how_many
-> FROM account
-> GROUP BY open_emp_id
-> HAVING COUNT(*) > 4;
+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
| 1 | 8 |
| 10 | 7 |
| 16 | 6 |
+-------------+----------+
3 rows in set (0.00 sec)