---
isPrivate: true
title: DDSQL Statements (Preview)
description: Datadog, the leading service for cloud-scale monitoring.
breadcrumbs: Docs > DDSQL Reference > DDSQL Statements (Preview)
---

# DDSQL Statements (Preview)

## SELECT{% #select %}

`SELECT` retrieves rows from a table or view.

### Syntax{% #syntax %}

```text
SELECT [ ALL | DISTINCT ] select_expr, ...
[ FROM rel_source
  [ EVENT_SEARCH 'message_pattern' ]
  [ USE EVENT_INDEX 'index_name' ]
  [ [ join_type ] JOIN rel_source ...
    [ ON condition | USING (column, ... ) ] ] ... ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] expression, ... ]
[ HAVING condition, ... ]
[ ORDER BY expression, ... [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]
[ LIMIT [ ALL | expression ]
  [ OFFSET expression ] ]
```

#### Placeholder types{% #placeholder-types %}

{% dl %}

{% dt %}
`select_expr`
{% /dt %}

{% dd %}
Any expression that returns a value. It may be a constant, function call, aggregate, window, or the special expression `*`. This is the part of the query that specifies the output of the SELECT statement, and in relational algebra it is known as the projection.
{% /dd %}

{% dt %}
`message_pattern`
{% /dt %}

{% dd %}
A textual pattern for [full-text search](https://docs.datadoghq.com/logs/explorer/search_syntax/#full-text-search), where available.
{% /dd %}

{% dt %}
`index_name`
{% /dt %}

{% dd %}
An identifier for a [logs index](https://docs.datadoghq.com/logs/log_configuration/indexes/).
{% /dd %}

{% dt %}
`rel_source`
{% /dt %}

{% dd %}
A correlation (a table name or alias) or a parenthesized [DQL expression](https://docs.datadoghq.com/ddsql_editor/#use-sql-syntax-ddsql).
{% /dd %}

{% dt %}
`join_type`
{% /dt %}

{% dd %}
The type of SQL join, such as `INNER` or `LEFT`. `INNER` joins are fully supported. `OUTER` and `CROSS` joins may require a `WHERE` condition. `LEFT` and `RIGHT` joins are also supported if the condition is an *equijoin* expression: an equality comparison such as `<EXPRESSION_1> = <EXPRESSION_2>` where the expressions reference columns from different tables, and the output types of both expressions are the same. A `USING` expression `JOIN`ing on only one column also works.
{% /dd %}

{% dt %}
`condition`
{% /dt %}

{% dd %}
An expression that is evaluated and interpreted implicitly as having a boolean result.
{% /dd %}

{% dt %}
`expression`
{% /dt %}

{% dd %}
A value expression. See [Expressions and Operators](https://docs.datadoghq.com/ddsql_reference/ddsql_preview/expressions_and_operators) for details and examples.
{% /dd %}

{% /dl %}

### Evaluation{% #evaluation %}

SELECT retrieves rows from zero or more tables. The general processing of SELECT is as follows:

1. All elements in `FROM` are computed. If more than one element is specified, they are joined together using the specified join type.
1. If the `WHERE` clause is specified, rows that do not satisfy the condition are eliminated from the output.
1. If the `GROUP BY` clause is specified or there are aggregate function calls in the `selectExpr`, the output is combined into groups of rows that match on one or more values, and the aggregates are computed. If `HAVING` is present, rows that do not satisfy its condition are eliminated from the output.
1. The actual output rows are computed using the `selectExpr`.
1. `SELECT DISTINCT` eliminates duplicate rows from the result.
1. If the `ORDER BY` clause is specified, the returned rows are sorted in the specified order.
1. If the `LIMIT` or `OFFSET` clause is specified, rows not in the specified subset are eliminated.

The system may execute the query in any way that is guaranteed to produce the results specified by this order.

## Aliases{% #aliases %}

Aliases are substitute names for output expressions or `FROM` items. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times).

```sql
SELECT * FROM my_long_hosts_table_name as hosts
```

When an alias is provided in a `FROM` item, it completely hides the actual name of the table or function. In the above example, the remainder of the DQL expression must refer to `my_long_hosts_table_name` as `hosts`.

## Ordinals{% #ordinals %}

`GROUP BY` and `ORDER BY` clause expressions can be column names, arbitrary expressions formed from input columns, or the name or ordinal number of an output expression (a `SELECT` expression). Output expression ordinals are 1-indexed.

For example, the output of this query is ordered first by `ex3`, then `ex2`, and then `ex1`:

```sql
SELECT ex1, ex2, ex3 FROM table ORDER BY 3, 2, 1;
```

## UNION{% #union %}

`UNION` combines the results of two or more [DQL expressions](https://docs.datadoghq.com/ddsql_editor/#use-sql-syntax-ddsql) into a single output table.

### Syntax{% #syntax-1 %}

```text
DQL_expression UNION [ ALL ] DQL_expression ...
[ ORDER BY expressions [ ASC | DESC ] ]
[ LIMIT [ ALL | expression ]
  [ OFFSET expression] ]
```

#### Placeholder types{% #placeholder-types-1 %}

{% dl %}

{% dt %}
`DQL_expression`
{% /dt %}

{% dd %}
A query statement, such as a `SELECT` statement.
{% /dd %}

{% /dl %}

The `UNION` operator removes duplicate rows from the result. To retain duplicate rows, use `UNION ALL`:

```sql
SELECT host_key, CAST(service AS text) AS service, 'from resources' FROM host
UNION ALL
SELECT message, service AS text, 'from logs' FROM logs WHERE env='prod'
ORDER BY service LIMIT 200 OFFSET 10;
```

All subqueries in a `UNION` must have the same output schema. A query containing a `UNION` query can only have one `ORDER BY` and `LIMIT` expression, both of which must come at the end. Chained `UNION`s can only have one `ORDER BY` and `LIMIT` expression at the end.

## WITH{% #with %}

`WITH` provides a way to write auxiliary statements for use in a larger query.

`WITH` statements, which are also often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist for one query. Each auxiliary statement in a `WITH` clause can be any [DQL expression](https://docs.datadoghq.com/ddsql_editor/#use-sql-syntax-ddsql), and the `WITH` clause itself is attached to a primary statement that can also be any non-`WITH` DQL expression. Subsequent auxiliary statements may reference correlations aliased in previous auxiliary statements.

### Syntax{% #syntax-2 %}

```sql
WITH alias [ ( output, schema, column, names, ... ) ] AS ( DQL_expression ) [, ...] DQL_expression
```

#### Placeholder types{% #placeholder-types-2 %}

{% dl %}

{% dt %}
`DQL_expression`
{% /dt %}

{% dd %}
A query statement, such as a `SELECT` statement.
{% /dd %}

{% /dl %}

Data modification statements like `INSERT`, `UPDATE`, and `DELETE` are not supported in `WITH`.

Each aliased query may also specify its output schema and column names.

## CREATE{% #create %}

DDSQL allows users to create temporary tables, insert into them, and query and reference them. These tables are not persisted across sessions.

### Syntax{% #syntax-3 %}

```sql
CREATE TABLE name (
  column_name column_type
  [ PRIMARY KEY [ AUTOINCREMENT ] | NOT NULL | UNIQUE | DEFAULT expression ] ...
)
```

## INSERT{% #insert %}

DDSQL's `INSERT` statement follows the SQL standard. DDSQL only allows users to insert into temporary tables that are created with the `CREATE` statement, not downstream data sources.

### Syntax{% #syntax-4 %}

```sql
INSERT INTO table_name [ (specific, columns, ...) ] VALUES
  ( value1, value2, ... ),
  ( value1, value2, ... ),
  ...
```

## SHOW{% #show %}

{% alert level="danger" %}
While the `SHOW` statement is a part of the SQL standard, the runtime parameter names are experimental. Parameters may be renamed, retyped, or deprecated in the future.
{% /alert %}

When running queries, DDSQL references runtime parameters (environmental variables) that are not specified in the query statement itself, such as the default interval to use for metrics queries if no `BUCKET BY` is specified, or the start and end timestamp for a query.

The `SHOW` statement displays the values of these variables.

### Syntax{% #syntax-5 %}

```sql
SHOW (ALL | parameter)
```

`SHOW ALL` displays all available runtime parameters in the DDSQL system, and `SHOW <PARAMETER>` displays only the parameter specified.

## SET{% #set %}

To modify a runtime parameter, use the `SET` statement.

### Syntax{% #syntax-6 %}

```sql
SET variableName = expression
```