Every SQL function, explained with syntax, runnable examples, common mistakes, performance and interview questions.
Aggregate 6
COUNT()
Counts rows or non-NULL values in a group.
SUM()
Adds up the numeric values in a group.
AVG()
Returns the mean of the numeric values in a group.
MIN()
Returns the smallest value in a group.
MAX()
Returns the largest value in a group.
STRING_AGG()
Concatenates values across rows into one delimited string.
Window 9
ROW_NUMBER()
Assigns a unique sequential number to each row in a partition.
RANK()
Ranks rows within a partition, leaving gaps after ties.
DENSE_RANK()
Ranks rows within a partition with no gaps after ties.
LAG()
Returns a value from a previous row in the window.
LEAD()
Returns a value from a following row in the window.
NTILE()
Splits ordered rows into N roughly equal buckets.
FIRST_VALUE()
Returns the first value in the window frame.
LAST_VALUE()
Returns the last value in the window frame.
NTH_VALUE()
Returns the Nth value in the window frame.
String 14
CONCAT()
Joins two or more strings into one.
CONCAT_WS()
Joins strings with a separator, skipping NULLs.
SUBSTRING()
Extracts part of a string by position and length.
LENGTH()
Returns the length of a string.
UPPER()
Converts a string to uppercase.
LOWER()
Converts a string to lowercase.
TRIM()
Removes leading and trailing spaces or characters.
REPLACE()
Replaces every occurrence of a substring.
LEFT()
Returns the leftmost N characters.
RIGHT()
Returns the rightmost N characters.
POSITION()
Finds the index of a substring within a string.
REVERSE()
Reverses the characters in a string.
LPAD()
Left-pads a string to a target length.
RPAD()
Right-pads a string to a target length.
Date & Time 7
DATEADD()
Adds an interval to a date (DATE_ADD in MySQL).
DATEDIFF()
Returns the difference between two dates.
NOW()
Returns the current date and time.
CURRENT_DATE
Returns today's date with no time.
EXTRACT()
Pulls a component such as year or month from a date.
DATE_TRUNC()
Truncates a timestamp down to a unit.
DATE_FORMAT()
Formats a date or time as a string.
Numeric 9
ROUND()
Rounds a number to a given number of decimals.
CEIL()
Rounds a number up to the nearest integer.
FLOOR()
Rounds a number down to the nearest integer.
ABS()
Returns the absolute value of a number.
MOD()
Returns the remainder of a division.
POWER()
Raises a number to an exponent.
SQRT()
Returns the square root of a number.
TRUNCATE()
Cuts off decimals without rounding.
RANDOM()
Returns a random number (RAND in MySQL/SQL Server).
Conditional & NULL 6
COALESCE()
Returns the first non-NULL value from a list.
CASE
Returns a value based on conditional logic.
NULLIF()
Returns NULL if two values are equal, else the first.
IFNULL()
Replaces NULL with a fallback value (two arguments).
GREATEST()
Returns the largest of several values in a row.
LEAST()
Returns the smallest of several values in a row.
Conversion 2
No functions match your search.