Data Manipulation (DML)


INSERT INTO <table> VALUES (value, ...);

Use this statement for single row ad hoc inserts. (When inserting many rows, use the more efficient COPY command.)

INSERT INTO foo VALUES (NULL, 3.1415, 'xyz', '2015-05-11 211720`);


[ WITH <alias> AS <query>,... ]
SELECT [ALL|DISTINCT] <expr> [AS [<alias>]], ...
  FROM <table> [ <alias> ], ...
  [WHERE <expr>]
  [GROUP BY <expr>, ...]
  [HAVING <expr>]
  [ORDER BY <expr> [ ASC | DESC ] , ...]
  [LIMIT {<number>|ALL} [OFFSET <number> [ROWS]]]
  [ANY | ALL (subquery) ;

Usage Notes

  • ORDER BY sort order defaults to ASC.
  • ORDER BY allows you to use a positional reference to choose the sort column. For example, the command SELECT colA,colB FROM table1 ORDER BY 2 sorts the results on colB because it is in position 2.



Shows the generated IR code identifying whether it is executed on GPU or CPU. This is mostly used internally by MapD to monitor behavior.


Returns a Relational Algebra tree describing the high level plan to execute the statement.

Table Expression and Join Support

<table> , <table> WHERE <column> = <column>
<table> [ LEFT ] JOIN <table> ON <column> = <column>

Usage Notes

  • You can join tables on at most one column.
  • If join column names or aliases are not unique, they must be prefixed by their table name.
  • Data types of join columns must be SMALLINT, INTEGER, BIGINT, or TEXT/VARCHAR ENCODING DICT.
  • Data types of join columns must match exactly. For example a SMALLINT column cannot be joined to a BIGINT column.
  • For all but the first table list in the from-list, the data values in the join column must be unique. In data warehouse terms, list the “fact” table first, followed by any number of “dimension” tables.

Logical Operator Support

Operator Description
AND logical AND
NOT negates value
OR logical OR

Comparison Operator Support

Operator Description
= equals
<> not equals
> greater than
>= greater than or equal to
< less than
<= less than or equal to
BETWEEN x AND y is a value within a range
NOT BETWEEN x AND y is a value not within a range
IS NULL is a value null
IS NOT NULL is a value not null

Mathematical Function Support

Function Description
ABS(x) returns the absolute value of x
CEIL(x) returns the smallest integer not less than the argument
DEGREES(x) converts radians to degrees
EXP(x) returns the value of e to the power of x
FLOOR(x) returns the largest integer not greater than the argument
LN(x) returns the natural logarithm of x
LOG(x) returns the natural logarithm of x
LOG10(x) returns the base-10 logarithm of the specified float expression x
MOD(x, y) returns the remainder of x divided by y
PI() returns the value of pi
POWER(x, y) returns the value of x raised to the power of y
RADIANS(x) converts degrees to radians
ROUND(x, y) rounds x to y decimal places
SIGN(x) returns the sign of x as -1, 0, 1 if x is negative, zero, or positive
TRUNCATE(x, y) truncates x to y decimal places

Statistical Function Support

Function Description
CORR(x, y) Returns the coefficient of correlation of a set of number pairs.
COVAR_POP(x, y) Returns the population covariance of a set of number pairs.
COVAR_SAMP(x, y) Returns the sample covariance of a set of number pairs.
STDDEV_POP(x) Returns the population standard deviation of the value.
STDDEV_SAMP(x) Returns the sample standard deviation of the value.
VAR_POP(x) Returns the population variance of the value.
VAR_SAMP(x) Returns the sample variance of the value.

Trigonometric Function Support

Function Description
ACOS(x) returns the arc cosine of x
ASIN(x) returns the arc sine of x
ATAN(x) returns the arc tangent of x
ATAN2(x, y) returns the arc tangent of x and y
COS(x) returns the cosine of x
COT(x) returns the cotangent of x
SIN(x) returns the sine of x
TAN(x) returns the tangent of x

Geometric Function Support

Function Description
DISTANCE_IN_METERS(fromLon, fromLat, toLon, toLat) calculate distance in meters between two WGS-84 positions

String Function Support

Function Description
CHAR_LENGTH(str) returns the number of characters in a string
LENGTH(str) returns the length of a string in bytes

Pattern Matching Support

Name Example Description
str LIKE pattern 'ab' LIKE 'ab' returns true if the string matches the pattern
str NOT LIKE pattern 'ab' NOT LIKE 'cd' returns true if the string does not match the pattern
str ILIKE pattern 'AB' ILIKE 'ab' case-insensitive LIKE
str REGEXP POSIX pattern '^[a-z]+r$' lower case string ending with r
REGEXP_LIKE ( str , POSIX pattern ) '^[hc]at' cat or hat

Wildcard characters supported by LIKE and ILIKE:

% matches any number of characters, including zero characters

_ matches exactly one character

Date/Time Function Support

Function Description
DATE_TRUNC(date_part, timestamp) truncates the timestamp to the specified date_part
EXTRACT(date_part FROM timestamp) returns the specified date_part from provided timestamp
NOW() returns the current timestamp

Supported date_part types:


Accepted Date, Time, and Timestamp formats

Datatype Formats Examples
DATE YYYY-MM-DD 2013-10-31
DATE MM/DD/YYYY 10/31/2013
DATE DD-MON-YY 31-Oct-13
DATE DD/Mon/YYYY 31/Oct/2013
TIME HH:MM 23:49
TIME HH:MM:SS 23:49:01
TIMESTAMP DATE TIME 31-Oct-13 23:49:01
TIMESTAMP DATE:TIME 11/31/2013:234901
TIMESTAMP DATE TIME ZONE 31-Oct-13 11:30:25 -0800
TIMESTAMP DATE HH.MM.SS PM 31-Oct-13 11.30.25pm
TIMESTAMP DATE HH:MM:SS PM 31-Oct-13 11:30:25pm
TIMESTAMP   1383262225

Usage Notes

  • For two-digit years, years 69-99 are assumed to be previous century (e.g. 1969), and 0-68 are assumed to be current century (016).
  • For four-digit years, negative years (e.g. BC) are not supported.
  • Hours are expressed in 24-hour format.
  • When time components are separated by colons, you can write them as one or two digits.
  • Months are case insensitive. You can spell them out or abbreviate to three characters.
  • For timestamps, decimal seconds are ignored. Time zone offsets are written as +/-HHMM.
  • For timestamps, a numeric string is converted to +/- seconds since January 1, 1970.
  • On output, dates are formatted as YYYY-MM-DD. Times are formatted as HH:MM:SS.

Aggregate Function Support

Function Description
AVG(x) returns the average value of x
COUNT() returns the count of the number of rows returned
COUNT(DISTINCT x) returns the count of distinct values of x
APPROX_COUNT_DISTINCT(x) returns the approximate count of distinct values of x
MAX(x) returns the maximum value of x
MIN(x) returns the minimum value of x
SUM(x) returns the sum of the values of x

Usage Notes

  • COUNT(DISTINCT x ), especially when used in conjunction with GROUP BY, can require a very large amount of memory to keep track of all distinct values in large tables with large cardinality. To avoid this large overhead APPROX_COUNT_DICTINCT (x) gives a very close approximation (within 4%) of the distinct values for a column while keeping performance and memory usage reasonable. It is recommended on large table with large cardinalities to use APPROX_COUNT_DISTINCT when possible.

Conditional Expression Support

Expression Description
CASE WHEN condition THEN result Case operator
COALESCE(val1, val2, ..) returns the first non-null value in the list

Subquery Expression Support

Expression Example Description
IN expr IN (subquery or list of values) evaluates whether expr equals any value of the IN list
NOT IN expr NOT IN (subquery or list of values) evaluates whether expr does not equal any value of the IN list

Usage Notes

  • A subquery may be used anywhere an expression may be used, subject to any run-time constraints of that expression. For example, a subquery in a CASE statement must return exactly one row, but a subquery may return multiple values to an IN expression.
  • A subquery may be used anywhere a table is allowed (e.g. FROM subquery), making use of aliases to name any reference to the table and columns returned by the subquery.

Type Cast Support

Expression Example Description
CAST(expr AS type) CAST(1.25 AS FLOAT) converts an expression to another data type

Array Support

Expression Description
SELECT <ArrayCol>[n] ... Query array elements n of column ArrayCol
UNNEST(<ArrayCol>) ... Expand the array ArrayCol to a set of rows.
SELECT...WHERE ANY(subquery) ANY compares a scalar value with a single- column set of values, and returns TRUE when the result of an inner query contains at least one row. ANY must be preceded by comparison operators.
SELECT...WHERE ALL(subquery) ALL compares a scalar value to a single-column of values, and returns TRUE when the comparison specified is TRUE for all pairs (scalar_expression,x), where x is a value in the single-column set.
ARRAYINDEX(row_index) Returns a value from a specific location in an array.


Expression Description
LIKELY(X) The LIKELY(X) function provides a hint to the query planner that argument X is a boolean value that is usually true. The planner can prioritize filters on the value X earlier in the execution cycle and return significant results more efficiently.
UNLIKELY(X) The UNLIKELY(X) function provides a hint to the query planner that argument X is a boolean value that is usually not true. The planner can prioritize filters on the value X later in the execution cycle and return significant results more efficiently.

Usage Notes

SQL normally assumes that terms in the WHERE clause that cannot be used by indices have a strong probability of being true. If this assumption is incorrect, it could lead to a suboptimal query plan. You can use the LIKELY(X) and UNLIKELY(X) SQL functions to provide hints to the query planner about clause terms that are probably not true, which helps the query planner to select the best possible plan.