# 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`

¶

Shows generated Intermediate Representation (IR) code, identifying whether it is executed on GPU or CPU. This is primarily used internally by MapD to monitor behavior.

```
EXPLAIN <STMT>;
```

For example, when you use the EXPLAIN command on a basic statement, the utility returns 90 lines of IR code that is not meant to be human readable. At the top of the listing, though, a heading indicates whether it is `IR for the CPU`

or `IR for the GPU`

, which can be useful to know in some situations.

`EXPLAIN CALCITE`

¶

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

```
EXPLAIN CALCITE <STMT>;
```

The table below lists the relational algebra classes used to describe the execution plan for a SQL statement.

Method | Description | |
---|---|

LogicalAggregate | LogicalAggregate is a relational operator that eliminates duplicates and computes totals. |

LogicalCalc | A relational expression that computes project expressions and also filters. |

LogicalChi | Relational operator that converts a stream to a relation. |

LogicalCorrelate | A relational operator that performs nested-loop joins. |

LogicalDelta | Relational operator that converts a relation to a stream. |

LogicalExchange | Relational expression that imposes a particular distribution on its input without otherwise changing its content. |

LogicalFilter | Relational expression that iterates over its input and returns elements for which a condition evaluates to true. |

LogicalIntersect | Relational expression that returns the intersection of the rows of its inputs. |

LogicalJoin | Relational expression that combines two relational expressions according to some condition. |

LogicalMatch | Relational expression that represents a MATCH_RECOGNIZE node. |

LogicalMinus | Relational expression that returns the rows of its first input minus any matching rows from its other inputs. Corresponds to the SQL EXCEPT operator. |

LogicalProject | Relational expression that computes a set of ‘select expressions’ from its input relational expression. |

LogicalSort | Relational expression that imposes a particular sort order on its input without otherwise changing its content. |

LogicalTableFunctionScan | Relational expression that calls a table-valued function. |

LogicalTableModify | Relational expression that modifies a table. It is similar to TableScan, but represents a request to modify a table rather than read from it. |

LogicalTableScan | A LogicalTableScan reads all the rows from a RelOptTable. |

LogicalUnion | Relational expression that returns the union of the rows of its inputs, optionally eliminating duplicates. |

LogicalValues | Relational expression whose value is a sequence of zero or more literal row values. |

LogicalWindow | A relational expression representing a set of window aggregates. |

For example a SELECT statement is described as a table scan and projection.

```
mapdql> explain calcite (select * from movies);
Explanation
LogicalProject(movieId=[$0], title=[$1], genres=[$2])
LogicalTableScan(table=[[CATALOG, mapd, MOVIES]])
```

If you add a sort order, the table projection is folded under a LogicalSort procedure.

```
mapdql> explain calcite (select * from movies order by title);
Explanation
LogicalSort(sort0=[$1], dir0=[ASC])
LogicalProject(movieId=[$0], title=[$1], genres=[$2])
LogicalTableScan(table=[[CATALOG, mapd, MOVIES]])
```

When the SQL statement is simple, the EXPLAIN CALCITE version is actually less “human readable.” The value of EXPLAIN CALCITE becomes clear as you work with more complex SQL statements, like the one that follows. This query performs a scan on the BOOK table before scanning the BOOK_ORDER table.

```
mapdql> explain calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
FROM book b, book_customer bc, book_order bo, shipper s
WHERE bo.cust_id = bc.cust_id AND b.book_id = bo.book_id AND bo.shipper_id = s.shipper_id
AND s.name = 'UPS';
Explanation
LogicalProject(firstname=[$5], lastname=[$6], title=[$2], orderdate=[$11], name=[$14])
LogicalFilter(condition=[AND(=($9, $4), =($0, $8), =($10, $13), =($14, 'UPS'))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, mapd, BOOK]])
LogicalTableScan(table=[[CATALOG, mapd, BOOK_CUSTOMER]])
LogicalTableScan(table=[[CATALOG, mapd, BOOK_ORDER]])
LogicalTableScan(table=[[CATALOG, mapd, SHIPPER]])
```

Revising the original SQL command results in a more natural selection order and a more performant query.

```
mapdql> explain calcite SELECT bc.firstname, bc.lastname, b.title, bo.orderdate, s.name
FROM book_order bo, book_customer bc, book b, shipper s
WHERE bo.cust_id = bc.cust_id AND bo.book_id = b.book_id AND bo.shipper_id = s.shipper_id
AND s.name = 'UPS';
Explanation
LogicalProject(firstname=[$10], lastname=[$11], title=[$7], orderdate=[$3], name=[$14])
LogicalFilter(condition=[AND(=($1, $9), =($5, $0), =($2, $13), =($14, 'UPS'))])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalTableScan(table=[[CATALOG, mapd, BOOK_ORDER]])
LogicalTableScan(table=[[CATALOG, mapd, BOOK_CUSTOMER]])
LogicalTableScan(table=[[CATALOG, mapd, BOOK]])
LogicalTableScan(table=[[CATALOG, mapd, SHIPPER]])
```

## Table Expression and Join Support¶

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

**Usage Notes**

- 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:

```
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¶

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 | HHMMSS | 234901 |

TIME | HH:MM:SS | 23:49:01 |

TIMESTAMP | DATE TIME | 31-Oct-13 23:49:01 |

TIMESTAMP | DATETTIME | 31-Oct-13T23: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. |

`LIKELY/UNLIKELY`

¶

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.