Below is a quick reference list for the MySQL Functions descriptions and usage examples that have already been posted on this site.
You can search this blog using the function name to find many use cases for each of the built-in functions, as most of them are identical to those in Apache Hive, SQL Server, and Oracle SQL*Plus.
TIMEDIFF Function in MySQL
In order to analyze trends or systemic patterns throughout time, organizations might use time series analysis. Business users can explore the reasons why such patterns happen at certain times by using data visualizations to identify seasonal trends. TIMEDIFF is a MySQL function that calculates the difference between two “time” expressions. In data analysis or reporting, this…
NULLIF in MySQL
When the given two expressions are equal, the NULLIF() function compares them and returns NULL. If not, the first expression is returned. Examples: SELECT NULLIF(34, 0); Returns: 34 SELECT NULLIF(34, “SQL”); Returns: 34 SELECT NULLIF(34, 34); Returns: NULL SELECT NULLIF(NULL, 34); Returns: NULL (since it is the first expression)
SQL Server ISNULL equivalent in MySQL
One of the often used functions in SQL Server is ISNULL, which aids in replacing the null values of a column with an alternative value. The syntax of the ISNULL is provided below: SELECT ISNULL(expression, alternative_value); IFNULL is an equivalent function in MySQL. The definition: If the expression is NULL, the IFNULL() function returns a…
IFNULL in MySQL
If the expression is NULL, the IFNULL() function returns a specified alternative value. This function returns the expression if it is NOT NULL. This function is typically used to return a different value in the event that a column’s value contains any NULLs. Syntax: IFNULL(expression, alt_value) Example: SELECT IFNULL(NULL, “bigdatansql.com”); Result: bigdatansql.com
SUBTIME in MySQL
The SUBTIME() function subtracts a given time from a specified time or DateTime expression and then returns the resulting time/DateTime. Syntax: SUBTIME(datetime, time_interval) Note that both parameters are required. The time interval is the one that gets subtracted from datetime and can be either a positive or a negative value. Examples: SELECT SUBTIME(“2023-04-23 10:18:18.000021”, “5.000001”);…
ENUM vs SET in MySQL
Have you ever used an application or filled out a web form with the radio buttons and checkboxes depicted below? Source: Google Image. Typically, the values for radio buttons are fixed, and you can only select one from the available options. In the database, the column is restricted to that set of items so that…
TIME_FORMAT in MySQL
The time_format() is a MySQL date/time function. The purpose of it is to format the time using a defined format_mask. Format_mask refers to the timely application of the format. The choices are listed below. %f Microseconds (000000 to 999999) %H Hour (00 to 23 generally, but can be higher) %h Hour (00 to 12) %I…
Last Insert ID in MySQL
LAST_INSERT_ID() function return the AUTO_INCREMENT id of the last row that has been inserted or updated in a table. Here is an example. DROP TABLE IF EXISTS tbOrdersHeader; CREATE TABLE tbOrdersHeader( OrderID INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, OrderDate DATETIME NOT NULL); DROP TABLE IF EXISTS tbOrdersDetail; CREATE TABLE tbOrdersDetail( OrderDetailID INT unsigned NOT…
Big Data and SQL 