Page & Subpage’s Intent
The intent of this page is to provide a high-level overview on SQL functions and their types. The subpages dedicated to each type of function and the functions within each type are not intended to replace RDBMS documentation. My hope is to provide real-world examples on where and how each function can be used. The use-cases will largely rely on my personal experiences and creativity. In most instances, each example will contain functions adhering to the ANSI standard. In instances where non-ANSI functions are used, this will be the Oracle-specific function. Continue reading for a high-level overview on SQL functions and their types or navigate to pages dedicated to specific types of functions using the links below.
SINGLE-ROW FUNCTIONS
AGGREGATE FUNCTIONS
WINDOW/ANALYTIC FUNCTIONS
What is a function?
A function is a means to manipulate data and return a result. The functions available to the user vary across relational database management systems and even within (version/release-based differences).
Types of Functions
In general, functions fall into one of three categories: single-row, aggregate, or window/analytic.
Single-row Functions
As can likely be guessed, single-row functions operate on a single row. That is, each row’s value is passed to the function with the function returning a single result. For example, the LENGTH function (Oracle Database) accepts a row’s value and returns its length in terms of characters.
Aggregate Functions
In contrast to single-row functions, aggregate functions operate on groups of rows to return a single value (i.e., row) for each group. For example, the aggregate function, SUM, computes the sum of each group’s individual row values and returns a single value for the group.
Window/Analytic Functions
Similar to aggregate functions, window or analytic functions compute an aggregate based on a group of rows; however, these functions do not collapse/reduce a group of rows into a single row. Instead, window functions return multiple rows for each group. For example, when used as a window function, RANK computes the rank of each row’s value when compared to all other values returned by a query (i.e., the result set). Additional instructions or a specification describing how to compute the rank can be provided by the user, such as how to group and order the individual rows within each group.