Data Manipulation (DML)¶

INSERT¶

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

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

CREATE TABLE foo (a INT, b FLOAT, c TEXT, d TIMESTAMP);
INSERT INTO foo VALUES (NULL, 3.1415, 'xyz', '2015-05-11 211720`);

SELECT¶

[ 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.

EXPLAIN¶

EXPLAIN <STMT>;

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

EXPLAIN CALCITE <STMT>;

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¶

Comparison Operator Support¶

Mathematical Function Support¶

Statistical Function Support¶

Trigonometric Function Support¶

Geometric Function Support¶

String Function Support¶

Pattern Matching Support¶

Wildcard characters supported by LIKE and ILIKE:

% matches any number of characters, including zero characters

_ matches exactly one character

Date/Time Function Support¶

Supported date_part types:

DATE_TRUNC [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND,
            MILLENNIUM, CENTURY, DECADE, WEEK, QUARTERDAY]
EXTRACT    [YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND,
            DOW, ISODOW, DOY, EPOCH, QUARTERDAY, WEEK]

Accepted Date, Time, and Timestamp formats¶

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¶

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¶

Subquery Expression Support¶

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¶

Array Support¶

LIKELY/UNLIKELY¶

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.