HarperDB SQL Functions
This SQL keywords reference contains the SQL functions available in HarperDB.
Functions
Aggregate
| Keyword | Syntax | Description |
|---|---|---|
| AVG | AVG(expression) | Returns the average of a given numeric expression. |
| COUNT | SELECT COUNT(column_name) FROM schema.table WHERE condition | Returns the number records that match the given criteria. Nulls are not counted. |
| GROUP_CONCAT | GROUPCONCAT(_expression) | Returns a string with concatenated values that are comma separated and that are non-null from a group. Will return null when there are non-null values. |
| MAX | SELECT MAX(column_name) FROM schema.table WHERE condition | Returns largest value in a specified column. |
| MIN | SELECT MIN(column_name) FROM schema.table WHERE condition | Returns smallest value in a specified column. |
| SUM | SUM(column_name) | Returns the sum of the numeric values provided. |
| ARRAY* | ARRAY(expression) | Returns a list of data as a field. |
| DISTINCT_ARRAY* | DISTINCTARRAY(_expression) | When placed around a standard ARRAY() function, returns a distinct (deduplicated) results set. |
*For more information on ARRAY() and DISTINCT_ARRAY() see this blog.
Conversion
| Keyword | Syntax | Description |
|---|---|---|
| CAST | CAST(expression AS datatype(length)) | Converts a value to a specified datatype. |
| CONVERT | CONVERT(data_type(length), expression, style) | Converts a value from one datatype to a different, specified datatype. |
Date & Time
| Keyword | Syntax | Description |
|---|---|---|
| CURRENT_DATE | CURRENT_DATE() | Returns the current date in UTC in “YYYY-MM-DD” String format. |
| CURRENT_TIME | CURRENT_TIME() | Returns the current time in UTC in “HH:mm:ss.SSS” string format. |
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | Referencing this variable will evaluate as the current Unix Timestamp in milliseconds. For more information, go here. |
|
| DATE | DATE([date_string]) | Formats and returns the datestring argument in UTC in ‘YYYY-MM-DDTHH:mm:ss.SSSZZ’ string format. If a date_string is not provided, the function will return the current UTC date/time value in the return format defined above. For more information, go here. |
|
| DATE_ADD | DATE_ADD(_date, value, interval) | Adds the defined amount of time to the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted interval values: Either string value (key or shorthand) can be passed as the interval argument. For more information, go here. |
|
| DATEDIFF | DATEDIFF(_date_1, date_2[, interval]) | Returns the difference between the two date values passed based on the interval as a Number. If an interval is not provided, the function will return the difference value in milliseconds. For more information, go here. |
|
| DATEFORMAT | DATE_FORMAT(_date, format) | Formats and returns a date value in the String format provided. Find more details on accepted format values in the moment.js docs. For more information, go here. |
|
| DATESUB | DATE_SUB(_date, format) | Subtracts the defined amount of time from the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted datesub interval values- Either string value (key or shorthand) can be passed as the interval argument. For more information, go here. |
|
| DAY | DAY(_date) | Return the day of the month for the given date. |
|
| DAYOFWEEK | DAYOFWEEK(date) | Returns the numeric value of the weekday of the date given(“YYYY-MM-DD”).NOTE: 0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, and 6=Saturday. |
| EXTRACT | EXTRACT(date, date_part) | Extracts and returns the datepart requested as a String value. Accepted date_part values below show value returned for date = “2020-03-26T15:13:02.041+000” For more information, go here. |
|
| GETDATE | GETDATE() | Returns the current Unix Timestamp in milliseconds. |
| GET_SERVER_TIME | GET_SERVER_TIME() | Returns the current date/time value based on the server’s timezone in YYYY-MM-DDTHH:mm:ss.SSSZZ String format. |
| OFFSET_UTC | OFFSET_UTC(_date, offset) | Returns the UTC date time value with the offset provided included in the return String value formatted as YYYY-MM-DDTHH:mm:ss.SSSZZ. The offset argument will be added as minutes unless the value is less than 16 and greater than -16, in which case it will be treated as hours. |
| NOW | NOW() | Returns the current Unix Timestamp in milliseconds. |
|
| HOUR | HOUR(datetime) | Returns the hour part of a given date in range of 0 to 838. |
|
| MINUTE | MINUTE(datetime) | Returns the minute part of a time/datetime in range of 0 to 59. |
|
| MONTH | MONTH(date) | Returns month part for a specified date in range of 1 to 12. |
|
| SECOND | SECOND(datetime) | Returns the seconds part of a time/datetime in range of 0 to 59. |
| YEAR | YEAR(date) | Returns the year part for a specified date. |
|
Logical
| Keyword | Syntax | Description |
|---|---|---|
| IF | IF(condition, value_if_true, value_if_false) | Returns a value if the condition is true, or another value if the condition is false. |
| IIF | IIF(condition, value_if_true, value_if_false) | Returns a value if the condition is true, or another value if the condition is false. |
| IFNULL | IFNULL(expression, alt_value) | Returns a specified value if the expression is null. |
| NULLIF | NULLIF(expression_1, expression_2) | Returns null if expression_1 is equal to expression_2, if not equal, returns expression_1. |
Mathematical
| Keyword | Syntax | Description |
|---|---|---|
| ABS | ABS(expression) | Returns the absolute value of a given numeric expression. |
| CEIL | CEIL(number) | Returns integer ceiling, the smallest integer value that is bigger than or equal to a given number. |
| EXP | EXP(number) | Returns e to the power of a specified number. |
| FLOOR | FLOOR(number) | Returns the largest integer value that is smaller than, or equal to, a given number. |
| RANDOM | RANDOM(seed) | Returns a pseudo random number. |
| ROUND | ROUND(number,decimal_places) | Rounds a given number to a specified number of decimal places. |
| SQRT | SQRT(expression) | Returns the square root of an expression. |
String
| Keyword | Syntax | Description |
|---|---|---|
| CONCAT | CONCAT(string_1, string_2, ...., string_n) | Concatenates, or joins, two or more strings together, resulting in a single string. |
| CONCAT_WS | CONCATWS(_separator, string_1, string_2, ...., string_n) | Concatenates, or joins, two or more strings together with a separator, resulting in a single string. |
| INSTR | INSTR(string_1, string_2) | Returns the first position, as an integer, of string_2 within string_1. |
| LEN | LEN(string) | Returns the length of a string. |
| LOWER | LOWER(string) | Converts a string to lower-case. |
| REGEXP | SELECT column_name FROM schema.table WHERE column_name REGEXP pattern | Searches column for matching string against a given regular expression pattern, provided as a string, and returns all matches. If no matches are found, it returns null. |
| REGEXP_LIKE | SELECT column_name FROM schema.table WHERE REGEXPLIKE(_column_name, pattern) | Searches column for matching string against a given regular expression pattern, provided as a string, and returns all matches. If no matches are found, it returns null. |
| REPLACE | REPLACE(string, old_string, new_string) | Replaces all instances of old_string within new_string, with string. |
| SUBSTRING | SUBSTRING(string, string_position, length_of_substring) | Extracts a specified amount of characters from a string. |
| TRIM | TRIM([character(s) FROM] string) | Removes leading and trailing spaces, or specified character(s), from a string. |
| UPPER | UPPER(string) | Converts a string to upper-case. |
Operators
Logical Operators
| Keyword | Syntax | Description |
|---|---|---|
| BETWEEN | SELECT column_name(s) FROM schema.table WHERE column_name BETWEEN value_1 AND value_2 | (inclusive) Returns values(numbers, text, or dates) within a given range. |
| IN | SELECT column_name(s) FROM schema.table WHERE column_name IN(value(s)) | Used to specify multiple values in a WHERE clause. |
| LIKE | SELECT column_name(s) FROM schema.table WHERE column_n LIKE pattern | Searches for a specified pattern within a WHERE clause. |
Queries
General
| Keyword | Syntax | Description |
|---|---|---|
| DISTINCT | SELECT DISTINCT column_name(s) FROM schema.table | Returns only unique values, eliminating duplicate records. |
| FROM | FROM schema.table | Used to list the schema(s), table(s), and any joins required for a SQL statement. |
| GROUP BY | SELECT column_name(s) FROM schema.table WHERE condition GROUP BY column_name(s) ORDER BY column_name(s) | Groups rows that have the same values into summary rows. |
| HAVING | SELECT column_name(s) FROM schema.table WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s) | Filters data based on a group or aggregate function. |
| SELECT | SELECT column_name(s) FROM schema.table | Selects data from table. |
| WHERE | SELECT column_name(s) FROM schema.table WHERE condition | Extracts records based on a defined condition. |
Joins
| Keyword | Syntax | Description |
|---|---|---|
| CROSS JOIN | SELECT column_name(s) FROM schema.table_1 CROSS JOIN schema.table_2 | Returns a paired combination of each row from table_1 with row from table_2. Note: CROSS JOIN can return very large result sets and is generally considered bad practice. |
| FULL OUTER | SELECT column_name(s) FROM schema.table_1 FULL OUTER JOIN schema.table_2 ON table_1.column_name = table_2.column_name WHERE condition | Returns all records when there is a match in either table_1 (left table) or table_2 (right table). |
| [INNER] JOIN | SELECT column_name(s) FROM schema.table_1 INNER JOIN schema.table_2 ON table_1.column_name = table_2.column_name | Return only matching records from table_1 (left table) and table_2 (right table). The INNER keyword is optional and does not affect the result. |
| LEFT [OUTER] JOIN | SELECT column_name(s) FROM schema.table_1 LEFT OUTER JOIN schema.table_2 ON table_1.column_name = table_2.column_name | Return all records from table_1 (left table) and matching data from table_2 (right table). The OUTER keyword is optional and does not affect the result. |
| RIGHT [OUTER] JOIN | SELECT column_name(s) FROM schema.table_1 RIGHT OUTER JOIN schema.table_2 ON table_1.column_name = table_2.column_name | Return all records from table_2 (right table) and matching data from table_1 (left table). The OUTER keyword is optional and does not affect the result. |
Predicates
| Keyword | Syntax | Description |
|---|---|---|
| IS NOT NULL | SELECT column_name(s) FROM schema.table WHERE column_name IS NOT NULL | Tests for non-null values. |
| IS NULL | SELECT column_name(s) FROM schema.table WHERE column_name IS NULL | Tests for null values. |
Statements
| Keyword | Syntax | Description |
|---|---|---|
| DELETE | DELETE FROM schema.table WHERE condition | Deletes existing data from a table. |
| INSERT | INSERT INTO schema.table(column_name(s)) VALUES(value(s)) | Inserts new records into a table. |
| UPDATE | UPDATE schema.table SET column_1 = value_1, column_2 = value_2, ...., WHERE condition | Alters existing records in a table. |