> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Query Language (DQL) Statement Reference

export const OcientDataIntelligencePlatform = "OcientAIQ™ Unified Data Platform";

export const Ocient = "Ocient®";

The {OcientDataIntelligencePlatform} supports querying using the SQL syntax that follows ANSI SQL standards.

## Ocient SQL Syntax

This code block shows the general syntax to perform SQL querying in {Ocient} and the order in which commands should go.

For specific descriptions and syntax for the commands, see the respective SQL statement sections on this page.

**Syntax**

```sql SQL theme={null}
[ WITH ... ]
SELECT ...
    [ EXCEPT(...) ]
[ FROM ...
    [ JOIN ... ] ]
[ WHERE ... ]
[ GROUP BY ...
    [ HAVING ... ] ]
[ ORDER BY ... ]
[ LIMIT ... ]
[ OFFSET ... ]
[ INTERSECT ... ]
[ EXCEPT ... ]
[ UNION ... ] ]
[ USING ... ]
[ TRACE ... ]
[ TAG ... ]
```

## Default Schema

Ocient identifies every table using a database and schema. For example, the fully qualified path to the `movies` table is `cinema.adventure.movies`, where `cinema` is the database and `adventure` is the schema.

When you do not fully qualify a table name, the Ocient System uses a default schema. When you first log into the system, the default schema is your fully qualified username. You can change the default schema using the [SET SCHEMA](/commands-supported-by-the-ocient-jdbc-cli-program#set-schema) command.

## Querying SQL Statement Reference

Ocient supports the following SQL statements.

### WITH

Assigns a name to a common table expression, allowing an auxiliary query to be used in the main query. This helps break complex queries into smaller parts.

**Syntax**

```sql SQL theme={null}
WITH
    <cte_name> [ ( <cte_column> [ ,... ] ) ]
    AS ( <sub_query> )
<select_query>
```

**Parameters**

| **Parameter**    | **Description**                                                                                                                                                                                                                                                                            |
| ---------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `<cte_name>`     | A name for the common table expression populated with the result set from the `sub_query` statement.                                                                                                                                                                                       |
| `<cte_column>`   | Optional. <br />This is a list of column names for common table expressions populated with the `sub_query` result set. If no column names are provided, the column names are the same as the table referenced in the `sub_query` statement.                                                |
| `<sub_query>`    | An auxiliary query that collects data for a common table expression. <br />`sub_query` can use regular query commands, including ORDER BY, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, WHERE, GROUP BY, and HAVING.<br />See the respective SQL statement reference sections for usage rules. |
| `<select_query>` | The main SELECT query. <br />Note that the `FROM` statement in the main `SELECT` query must also include the `cte_name` if the query uses its values.                                                                                                                                      |

**Example**

In this example, the subquery calculates the average budget for all rows in the `movies` table. The main query uses that average to find all movies that spent more.

```sql SQL theme={null}
WITH avg_budget_table (average_budget) AS (
    SELECT AVG(budget)
    FROM movies
)
SELECT title,
    budget,
    revenue
FROM movies,
    avg_budget_table
WHERE budget > avg_budget_table.average_budget;
```

*Output*

| title                   | budget    | revenue    |
| ----------------------- | --------- | ---------- |
| CGI Why                 | 237000000 | 2787965087 |
| Titania                 | 200000000 | 1845034188 |
| Merchandise Vehicle 5   | 200000000 | 1066969703 |
| The Tentpole            | 220000000 | 1519557910 |
| Pirates of Palm Springs | 140000000 | 655011224  |
| Spyman 16               | 200000000 | 1108561013 |
| Frigid                  | 150000000 | 1274219009 |
| Fury 7                  | 190000000 | 1506249360 |
| Superhero 23            | 250000000 | 1084939099 |
| Triassic World          | 150000000 | 1513528810 |
| Iron Chef 3             | 200000000 | 1215439994 |
| The Last Airman         | 150000000 | 318502923  |

### SELECT

Initiates a query statement or a subquery clause within other statements.

You can query the data of tables where you have the SELECT privilege.

For information on using SELECT as a subquery for filtering or ordering results, see the [WHERE](#where) and [HAVING](#having) sections.

For information on using SELECT as a subquery for a common table expression, see the [WITH](#with) section.

<Info>
  `SELECT *` queries that lack a `FROM` clause automatically reference the `sys.dummy1` table.

  For example, `SELECT *;` is the same as `SELECT * FROM sys.dummy1;`.
</Info>

**Syntax**

```sql SQL theme={null}
SELECT [ ALL | DISTINCT ]
    [ * [ EXCEPT ( column_name [ , ... ] ) ]
    | <select_list_entry> [ , ... ] ]
    [ <from_clause> ]
```

**Parameters**

| **Parameter** | Description                                                                                                                            |
| ------------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| `ALL`         | `SELECT` and `SELECT ALL` are both the same. <br />Returns all valid data rows from the database that meet the criteria of your query. |
| `DISTINCT`    | `SELECT DISTINCT` returns only unique rows that do not match other rows based on the criteria of your query.                           |
| `*`           | Returns all columns from the specified tables in the result set for the query.                                                         |
| `EXCEPT`      | When used with `*`, `EXCEPT` allows specific columns to be excluded from the query results.                                            |
| `column_name` | The name of one or more columns from the specified table that you want to exclude (using `EXCEPT`) from your query results.            |

#### **\<select\_list\_entry>**

The `select_list_entry` defines a column or expression to include in your query result set.

**Syntax**

```sql SQL theme={null}
<select_list_entry> ::=
    column_name | expression [ AS new_name ]
```

**Parameters**

| **Parameter** | **Description**                                                                                                                                                                                            |
| ------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `column_name` | The name of one or more columns from the specified table that you want to include in your query results.                                                                                                   |
| `expression`  | One or more expressions that you want to include in your query results. <br />Expressions can be any combination of literal values, column names, arithmetic expressions, parentheses, and function calls. |
| `new_name`    | An alias, treated as an identifier, for an alternative name for the column or the results of the expression.                                                                                               |

#### **\<****from\_clause****>**

For information on the `<from_clause>`, see the [FROM](#from) documentation.

**Examples**

#### Using SELECT \*

This example uses `SELECT *` to return all the columns in the `movies` table.

```sql SQL theme={null}
SELECT * FROM movies LIMIT 5;
```

*Output*

| movie\_id | title           | budget    | popularity | release\_date | revenue    | runtime | movie\_status | vote\_average | vote\_count |
| --------- | --------------- | --------- | ---------- | ------------- | ---------- | ------- | ------------- | ------------- | ----------- |
| 211672    | Minnows         | 74000000  | 875.581305 | 2015-06-17    | 1156730962 | 91      | Released      | 6.40          | 4571        |
| 24        | Billy the Killy | 30000000  | 79.754966  | 2003-10-10    | 180949000  | 111     | Released      | 7.70          | 4949        |
| 19995     | CGI Why         | 237000000 | 150.437577 | 2009-12-10    | 2787965087 | 162     | Released      | 7.20          | 11800       |
| 37724     | Spyman 16       | 200000000 | 93.004993  | 2012-10-25    | 1108561013 | 143     | Released      | 6.90          | 7604        |
| 24428     | The Tentpole    | 220000000 | 144.448633 | 2012-04-25    | 1519557910 | 143     | Released      | 7.40          | 11776       |

#### Using SELECT \* EXCEPT

This example uses `SELECT * EXCEPT` to exclude certain columns from the result set.

```sql SQL theme={null}
SELECT *
EXCEPT (movie_id, runtime, vote_average, vote_count)
FROM movies
LIMIT 5;
```

*Output*

| title                 | budget    | release\_date | revenue    |
| --------------------- | --------- | ------------- | ---------- |
| Swords & Scabbards    | 94000000  | 2003-12-01    | 1118888979 |
| Billy the Killy       | 30000000  | 2003-10-10    | 180949000  |
| Merchandise Vehicle 5 | 200000000 | 2010-06-16    | 1066969703 |
| CGI Why               | 237000000 | 2009-12-10    | 2787965087 |
| Space Odyssey 6000    | 10500000  | 1968-04-10    | 68700000   |

#### Using Lateral Column Aliases

Ocient SQL queries support lateral column aliases, meaning you can immediately reuse aliases for calculations in the same query as new inputs. Hence, you can simplify queries that normally require subqueries and common table expressions.

These examples use the `products` table with these columns:

* `product_id` —  Product identifier as an integer
* `product_name` — Product name as a string
* `price` — Price as a floating point number

Create this table using the `CREATE TABLE` SQL statement.

```sql SQL theme={null}
CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
    );
```

Insert four records into the `products` table.

```sql SQL theme={null}
INSERT INTO products (product_id, product_name, price) VALUES
    (1, 'Laptop', 1000.00),
    (2, 'Tablet', 500.00),
    (3, 'Smartphone', 800.00),
    (4, 'Monitor', 300.00);
```

Create a query to determine prices after discounts and taxes by using a common table expression subquery `DiscountedPrices`. Use the `WITH` keyword to create the subquery.

```sql SQL theme={null}
WITH DiscountedPrices AS (
    SELECT
        product_id,
        product_name,
        price,
        price * 0.90 AS discounted_price,
        price * 0.90 * 1.05 AS total_price_after_tax
    FROM
        products
)
SELECT
    product_id,
    product_name,
    price,
    discounted_price,
    total_price_after_tax
FROM
    DiscountedPrices;
```

Lateral aliases allow the same calculations to be packaged in a single query.

This simpler query is essentially the same as the longer common table expression example, but the logic is condensed because you can reference the `discounted_price` alias immediately to calculate the `total_price_after_tax` value in the same query.

```sql SQL theme={null}
SELECT
    product_id,
    product_name,
    price,
    price * 0.90 AS discounted_price,
    discounted_price * 1.05 AS total_price_after_tax
FROM
    products;
```

### FROM

Specifies the table or view to use in a `SELECT` statement.

**Syntax**

```sql SQL theme={null}
<select_clause> FROM
    { table_name | ( <sub_query> ) | ( <values_clause> ) } [ ,... ]
    [ <join_clause> [ ,... ] ]
```

**Parameters**

| **Parameter**     | Description                                                                                                                                                                                                                                                                             |
| ----------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_clause>` | A query statement using the `SELECT` SQL statement. For details, see the [SELECT](#select) section.                                                                                                                                                                                     |
| `table_name`      | The names of one or more tables or views that you want to query. <br />If you specify multiple tables in the `FROM` list, separated by commas, the effect is the same as if you explicitly perform a [CROSS JOIN](#cross-join) on all of those sources.                                 |
| `<sub_query>`     | One or more subqueries using the `SELECT` SQL statement. A subquery generates a table from which the `select_clause` query references data. <br />Each subquery must be enclosed in parentheses with an optional correlation name.<br />For details, see the [SELECT](#select) section. |
| `<values_clause>` | Use the VALUES keyword to define a table with data. For example, the `SELECT * FROM (VALUES (1, NULL), (2, 5))` SQL statement selects all the data from a table defined by the specified values as `(1, NULL)` for the first row with two columns, and `(2, 5)` for the second row.     |
| `<join_clause>`   | A `JOIN` clause used to retrieve data from two or more tables for your query. <br />For details, see the [JOIN](#join) section.                                                                                                                                                         |

### JOIN

Combines rows from multiple tables so they can be accessed by a query.

**Syntax**

```sql SQL theme={null}
<select_query>
    FROM <table_reference1> <join_operation> <table_reference2>
        ON table1_column <boolean_operator> table2_column
```

**Parameter**

| **Parameter**        | **Description**                                                                                                                                                                                                                                       |
| -------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query>`     | A valid `SELECT` query. <br />For details, see [SELECT](#select).                                                                                                                                                                                     |
| `<table_reference1>` | Either a table name or a full `SELECT` in parentheses<br />`table_reference1` takes precedence for returning rows for `LEFT OUTER JOIN`, `SEMI JOIN`, and `ANTI JOIN`. For specific rules, see [Types of Join Operations](#types-of-join-operations). |
| `<table_reference2>` | Either a table name or a full `SELECT` in parentheses function.  <br />`table_reference2` takes precedence for returning rows for `RIGHT OUTER JOIN`. For specific rules, see [Types of Join Operations](#types-of-join-operations).                  |
| `table1_column`      | A column from either `table_reference` <br />The `JOIN` operation uses this column to match rows with `table2_column` to combine data for the result set.                                                                                             |
| `<boolean_operator>` | Join conditions can use any Boolean expression, including `=`, `!=`, `<`, `>`, `=>`, and `<=`.                                                                                                                                                        |
| `table2_column`      | A column from the `table_reference` not used for `table1_column` <br />The `JOIN` operation uses this column to match rows based on the `table1_column` to combine data for the result set.                                                           |

#### Types of Join Operations ( `<join_operation>` ) \[#types-of-join-operations]

Ocient supports the following types of `JOIN` operations.

**Syntax**

```sql SQL theme={null}
<join_operation> ::=
    { INNER JOIN
    | LEFT [ OUTER ] JOIN
    | RIGHT [ OUTER ] JOIN
    | FULL [ OUTER ] JOIN
    | CROSS JOIN
    | SEMI JOIN
    | ANTI JOIN }
```

#### **JOIN Type Descriptions**

| **JOIN Type**      | **Description**                                                                                                                                                                                                                                  |
| ------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `INNER JOIN`       | Returns all rows that exist in both tables. This join type is the default type of join.                                                                                                                                                          |
| `LEFT OUTER JOIN`  | Returns all rows from the first table regardless of whether there are matching rows in the second table. <br />`LEFT JOIN` performs the same operation as `LEFT OUTER JOIN`.                                                                     |
| `RIGHT OUTER JOIN` | Returns all rows from the second table regardless of whether there are matching rows in the first table.<br /> `RIGHT JOIN` performs the same operation as `RIGHT OUTER JOIN`.                                                                   |
| `FULL OUTER JOIN`  | Returns all matched and unmatched rows.<br />`FULL JOIN` performs the same operation as `FULL OUTER JOIN`.                                                                                                                                       |
| `CROSS JOIN`       | Returns the Cartesian product of the joined tables. Each row in the first table is joined with all rows in the second table. The result set contains the number of rows in the first table multiplied by the number of rows in the second table. |
| `SEMI JOIN`        | Returns the rows from the first table that have at least one match in the second table. No columns from the second table are available in the query.                                                                                             |
| `ANTI JOIN`        | Returns the rows from the first table that have no matches in the second table. No columns from the second table are available in the query.                                                                                                     |

<Info>
  By default, `JOIN` statements that involve subqueries can operate laterally if necessary. This behavior allows subqueries to reference joined columns from the preceding items included in the `FROM` clause.

  For example, both parts of this join operation use subqueries that reference table `x`. The `LATERAL` keyword is optional; the join operation acts the same regardless of whether it is included.

  ```text Text theme={null}
  SELECT \* FROM
  	(
  	SELECT
  		1 AS cx1,
  		2 AS cx2) AS x
  INNER JOIN LATERAL (
  	SELECT
  		x.cx1 AS cy1,
  		x.cx2 AS cy2) AS y
  ON
  	x.cx1 = y.cy1;
  ```

  Lateral joins are primarily useful when a cross-referenced column is necessary for computing the rows to join. A common application is providing an argument value for a set-returning function.
</Info>

**Examples**

These examples join two tables:

* `games` — A table of video game titles and their genre\_ids. Note that some games have a NULL value assigned to their genre\_id.

| game\_name             | genre\_id |
| ---------------------- | --------- |
| Star Battle 6000       | 9         |
| Dwarf Simulator        | NULL      |
| 2002 Futbol            | 11        |
| Space Attack!          | 9         |
| Fantasy Fool           | 8         |
| Blasto the Squirrel    | 5         |
| Trucker Quest          | 7         |
| Block, Stack, and Cry! | 6         |
| Basketball Day         | 11        |
| Pizza Mutant           | 1         |
| Italian Plumber        | 5         |
| Zombies!               | 1         |
| Space Ninjas           | 12        |
| Moon Mayor             | 10        |
| Spreadsheet Hero       | NULL      |
| Zoom Tycoon            | 12        |
| Porch Poacher          | 6         |

* `genre` — A table of video game genres, which are identified by IDs.

| genre\_name  | id |
| ------------ | -- |
| Racing       | 7  |
| Puzzle       | 6  |
| Adventure    | 2  |
| Simulation   | 10 |
| Shooter      | 9  |
| Misc         | 4  |
| Action       | 1  |
| Sports       | 11 |
| Role-Playing | 8  |
| Platform     | 5  |
| Strategy     | 12 |
| Fighting     | 3  |

#### INNER JOIN

This example uses an `INNER JOIN` operation to capture only the rows that exist in both tables. Games with NULL values for their `genre_id` are eliminated from the result set.

```sql SQL theme={null}
SELECT game_name,
    genre_name
FROM video_games.game
    INNER JOIN video_games.genre ON game.genre_id = genre.id;
```

*Output*

| game\_name             | genre\_name  |
| ---------------------- | ------------ |
| Fantasy Fool           | Role-Playing |
| Blasto the Squirrel    | Platform     |
| Italian Plumber        | Platform     |
| Porch Poacher          | Puzzle       |
| Space Ninjas           | Strategy     |
| Basketball Day         | Sports       |
| 2002 Futbol            | Sports       |
| Moon Mayor             | Simulation   |
| Block, Stack, and Cry! | Puzzle       |
| Zombies!               | Action       |
| Pizza Mutant           | Action       |
| Space Attack!          | Shooter      |
| Trucker Quest          | Racing       |
| Zoom Tycoon            | Strategy     |

#### LEFT OUTER JOIN

This example uses a `LEFT OUTER JOIN` operation to capture all rows from the left table (game), even if they have no matching row in the right table (genre).

```sql SQL theme={null}
SELECT game_name,
    genre_name
FROM video_games.game
    LEFT OUTER JOIN video_games.genre ON game.genre_id = genre.id;
```

*Output*

| game\_name             | genre\_name  |
| ---------------------- | ------------ |
| Fantasy Fool           | Role-Playing |
| Space Ninjas           | Strategy     |
| Zoom Tycoon            | Strategy     |
| Zombies!               | Action       |
| Pizza Mutant           | Action       |
| Trucker Quest          | Racing       |
| Blasto the Squirrel    | Platform     |
| 2002 Futbol            | Sports       |
| Basketball Day         | Sports       |
| Italian Plumber        | Platform     |
| Block, Stack, and Cry! | Puzzle       |
| Star Battle 6000       | Shooter      |
| Dwarf Simulator        | NULL         |
| Moon Mayor             | Simulation   |
| Porch Poacher          | Puzzle       |
| Spreadsheet Hero       | NULL         |
| Space Attack!          | Shooter      |

#### RIGHT OUTER JOIN

This example uses a `RIGHT`` OUTER JOIN` operation to capture all rows from the right table (genre), even if they have no matching row in the left table (game).

```sql SQL theme={null}
SELECT game_name,
    genre_name
FROM video_games.game
    RIGHT OUTER JOIN video_games.genre ON game.genre_id = genre.id;
```

*Output*

| game\_name             | genre\_name  |
| ---------------------- | ------------ |
| NULL                   | Adventure    |
| Trucker Quest          | Racing       |
| Pizza Mutant           | Action       |
| Moon Mayor             | Simulation   |
| Fantasy Fool           | Role-Playing |
| Space Attack!          | Shooter      |
| NULL                   | Misc         |
| Star Battle 6000       | Shooter      |
| Space Ninjas           | Strategy     |
| Porch Poacher          | Puzzle       |
| Block, Stack, and Cry! | Puzzle       |
| Blasto the Squirrel    | Platform     |
| Italian Plumber        | Platform     |
| Zoom Tycoon            | Strategy     |
| NULL                   | Fighting     |
| Zombies!               | Action       |
| 2002 Futbol            | Sports       |
| Basketball Day         | Sports       |

#### FULL OUTER JOIN

This example uses a `FULL OUTER JOIN` operation to capture all rows from both tables, even if they do not match.

```sql SQL theme={null}
SELECT game_name,
    genre_name
FROM video_games.game
    FULL OUTER JOIN video_games.genre ON game.genre_id = genre.id;
```

*Output*

| game\_name             | genre\_name  |
| ---------------------- | ------------ |
| Basketball Day         | Sports       |
| 2002 Futbol            | Sports       |
| Zombies!               | Action       |
| Blasto the Squirrel    | Platform     |
| Moon Mayor             | Simulation   |
| Trucker Quest          | Racing       |
| NULL                   | Misc         |
| Star Battle 6000       | Shooter      |
| Space Attack!          | Shooter      |
| Spreadsheet Hero       | NULL         |
| NULL                   | Adventure    |
| NULL                   | Fighting     |
| Italian Plumber        | Platform     |
| Pizza Mutant           | Action       |
| Dwarf Simulator        | NULL         |
| Fantasy Fool           | Role-Playing |
| Block, Stack, and Cry! | Puzzle       |
| Space Ninjas           | Strategy     |
| Zoom Tycoon            | Strategy     |
| Porch Poacher          | Puzzle       |

#### CROSS JOIN

This example uses a `CROSS JOIN` operation to capture every possible combination of rows from both tables, regardless of whether they match. Note that, unlike other `JOIN` operations, `CROSS JOIN` does not require an `ON` statement.

```sql SQL theme={null}
SELECT game_name,
    genre_name
FROM video_games.genre
    CROSS JOIN video_games.game;
```

*Output*

<Info>
  As the result set for this CROSS JOIN example is more than 200 rows, the results are abbreviated.
</Info>

| genre\_name      | genre\_id    |
| ---------------- | ------------ |
| Spreadsheet Hero | Role-Playing |
| Spreadsheet Hero | Misc         |
| Spreadsheet Hero | Sports       |
| Spreadsheet Hero | Platform     |
| Spreadsheet Hero | Strategy     |
| Spreadsheet Hero | Shooter      |
| Spreadsheet Hero | Fighting     |
| Spreadsheet Hero | Action       |
| Spreadsheet Hero | Simulation   |
| Spreadsheet Hero | Racing       |
| Spreadsheet Hero | Puzzle       |
| Spreadsheet Hero | Adventure    |
| Space Attack!    | Role-Playing |
| Space Attack!    | Misc         |
| Space Attack!    | Sports       |
| Space Attack!    | Platform     |
| Space Attack!    | Strategy     |
| Space Attack!    | Shooter      |
| Space Attack!    | Fighting     |
| Space Attack!    | Action       |
| Space Attack!    | Simulation   |
| Space Attack!    | Racing       |
| Space Attack!    | Puzzle       |
| Space Attack!    | Adventure    |
| ...              | ...          |

#### SEMI JOIN

This example uses a `SEMI JOIN` operation to capture genre names that have at least one match in the games table. Rows are only included once, even if there are multiple matches.

```sql SQL theme={null}
SELECT genre_name,
    id
FROM video_games.genre
    SEMI JOIN video_games.game ON genre.id = game.genre_id;
```

*Output*

| genre\_name  | genre\_id |
| ------------ | --------- |
| Simulation   | 10        |
| Shooter      | 9         |
| Racing       | 7         |
| Strategy     | 12        |
| Puzzle       | 6         |
| Platform     | 5         |
| Action       | 1         |
| Sports       | 11        |
| Role-Playing | 8         |

#### ANTI JOIN

This example uses an `ANTI JOIN` operation to capture any rows in the game table that do not match any rows in the genre table.

```sql SQL theme={null}
SELECT game_name
FROM video_games.game
    ANTI JOIN video_games.genre ON game.genre_id = genre.id;
```

*Output*

| game\_name       |
| ---------------- |
| Spreadsheet Hero |
| Dwarf Simulator  |

### WHERE

Filters rows based on a specified condition.

**Syntax**

```sql SQL theme={null}
<select_query>
    WHERE { column_name <filter_condition> filter_value |
    [ NOT ] EXISTS ( <sub_query> ) }
```

**Parameters**

| **Parameter**    | **Description**                                                                                                                    |
| ---------------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query>` | A valid `SELECT` query.                                                                                                            |
| `column_name`    | A column used for grouping that is evaluated by the `filter_condition`.                                                            |
| `filter_value`   | A value used to evaluate the specified `column_name`. <br />For details, see the [\<filter\_condition>](#filtercondition) section. |
| `<sub_query>`    | A subquery with a filter condition that follows the `EXISTS` clause. <br />For details, see the [EXISTS](#exists) section.         |

#### \<filter\_condition>

A logical combination of predicates used to evaluate the referenced `column_name` based on the `filter_value`.

**Syntax**

```sql SQL theme={null}
<filter_condition> ::=
  { =
    | ==
    | <>
    | !=
    | [ NOT ] EQUALS
    | <
    | <=
    | >
    | >=
    | [ NOT ] IN
    | [ NOT ] LIKE
    | [ NOT ] SIMILAR TO
    | BETWEEN
    | FOR SOME
    | FOR ALL
    | IS [ NOT ] NULL
    | IS [ NOT ] DISTINCT FROM
  }
```

**Definitions**

| **Operator**                | **Description**                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | **Example**                                                              |
| --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------ |
| `=`, `==`, `EQUALS`         | Equal to.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | `Author = 'Alcott'`                                                      |
| `<>`, `!=`                  | Not equal to                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | `Dept <> 'Sales'`                                                        |
| `>`                         | Greater than                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | `Hire_Date > '2012-01-31'`                                               |
| `<`                         | Less than                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | `Bonus < 50000.00`                                                       |
| `>=`                        | Greater than or equal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | `Dependents >= 2`                                                        |
| `<=`                        | Less than or equal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | `Rate <= 0.05`                                                           |
| `[ NOT ] LIKE`              | Begins with a character pattern to match. <br />The pattern is enclosed in parentheses and can include the wildcard characters `%`, representing zero, one or multiple characters, and `_` representing a single character. <br />To have a literal `%` or `_` that does not act as a wildcard on the right-hand side of a `LIKE` or `NOT LIKE` expression, put a backslash before the character.                                                                                                                                                                                                                                           | `Full_Name LIKE 'Will%'`                                                 |
| `[ NOT ] SIMILAR TO`        | For usage information, see the [SIMILAR TO Operator](#similar-to-operator) section.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         | `'abc' SIMILAR TO 'abc' `                                                |
| `BETWEEN value1 AND value2` | Requires two values. <br />The filter evaluates to TRUE if the specified column is within the range between the two values. `BETWEEN` bounds are inclusive.  Values can be times, numbers, or strings.                                                                                                                                                                                                                                                                                                                                                                                                                                      | `Sales BETWEEN 1000 AND 5000`                                            |
| `FOR_SOME()`                | Evaluates an array to determine if at least one value meets the filter criteria. <br />In the example, the `FOR_SOME()` operator would return all rows of the column `col_int_array` where at least one value is greater than 10. <br />For details, see [Array Filters](#array-filters).                                                                                                                                                                                                                                                                                                                                                   | `FOR_SOME(col_int_array) > 10`<br /><br />`FOR_SOME(col_int_array) > 10` |
| `FOR_ALL()`                 | Evaluates all values of an array to determine if all meet the filter criteria. <br />In the example, the `FOR_ALL()` operator would return any rows of the column `col_int_array` where all values are greater than 10. <br />For details, see [Array Filters](#array-filters).                                                                                                                                                                                                                                                                                                                                                             | `FOR_ALL(col_int_array) > 10`<br /><br />`FOR_ALL(col_int_array) = 1`    |
| `[ NOT ] IN`                | Equal to one of multiple possible values                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | `DeptCode IN (101, 103, 209)`                                            |
| `IS [ NOT ] NULL`           | Compare to `NULL` (missing data)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | `Address IS NOT NULL`                                                    |
| `IS [ NOT ] DISTINCT FROM`  | Compares the equality of two expressions for a Boolean result, including comparisons that involve `NULL` values. This operator ensures reliable comparisons in scenarios where `NULL` values need to be treated as significant data points.<br />Normally, `NULL = NULL` evaluates to `false` because `NULL` represents an unknown value. However, the statement `a IS NOT DISTINCT FROM b` treats `NULL` as a comparable value and returns `true` when both values are `NULL` or when `a = b`. <br />Inversely, `a IS DISTINCT FROM b` returns `true` if the values are different or if one value is `NULL` while the other is not `NULL`. | `Debt IS NOT DISTINCT FROM Receivables`                                  |

#### EXISTS

An `EXISTS` clause used in a `WHERE` statement evaluates if a subquery returns any rows.

For each row that the database computes in the outer query, if the subquery returns at least one row, the `EXISTS` clause evaluates to `true`. In this outcome, the outer query returns its row.

If the subquery returns zero rows, the `EXISTS` clause evaluates to `false`, and the outer query excludes those rows.

The `NOT EXISTS` clause performs the opposite Boolean logic. In this case, the outer query returns rows only when the subquery has no matches.

**Examples**

These examples use two tables, one for company departments and the other for employees assigned to those departments.

Create the `departments` table for department data.

```sql SQL theme={null}
CREATE TABLE departments (
    id INT,
    name VARCHAR(50)
);
```

Create the `employees` table for employee data.

```sql SQL theme={null}
CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department_id INT
);
```

Insert department data for three departments, `HR`, `IT`, and `Marketing`, into the `departments` table.

```sql SQL theme={null}
INSERT INTO departments (id, name) VALUES
    (1, 'HR'),
    (2, 'IT'),
    (3, 'Marketing');
```

Insert employee data for four employees, `Alice`, `Bob`, `Charlie`, and `David`, into the `employees` table.

```sql SQL theme={null}
INSERT INTO employees (id, name, department_id) VALUES
    (1, 'Alice', 1),
    (2, 'Bob', 2),
    (3, 'Charlie', 2),
    (4, 'David', 4);
```

**Find Employees Belonging to an Existing Department**

This example uses an `EXISTS` clause to find any names from the `employees` table who are assigned to a department listed in the `departments` table.

```sql SQL theme={null}
SELECT name
FROM employees AS e
WHERE EXISTS (
    SELECT *
    FROM departments AS d
    WHERE e.department_id = d.id
);
```

The query returns all employees except `David`, who is assigned to a department that does not exist in the `departments` table.

*Output*

```none Text theme={null}
Bob
Charlie
Alice
```

**Find Departments That Have Employees**

This query returns departments that have at least one employee. The subquery uses `SELECT 1` to check whether any matching rows exist in the `employees` table. The output is the same if the query uses `SELECT *` instead.

```sql SQL theme={null}
SELECT name
FROM departments AS d
WHERE EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.id
);
```

The query results exclude the `Marketing` department because no employees belong to it.

*Output*

```none Text theme={null}
HR
IT
```

**Filter Departments Based on an Uncorrelated Table**

In this example, the outer query filters the `departments` table where `id != 3` (excluding the `Marketing` department). The subquery checks if there is at least one employee with a department identifier less than `4`.

The query is uncorrelated because the subquery does not reference the `departments` table.

```sql SQL theme={null}
SELECT *
FROM departments d
WHERE d.id != 3
AND EXISTS (
    SELECT *
    FROM employees e
    WHERE e.department_id < 4
);
```

*Output*

```none Text theme={null}
HR
IT
```

**Find Employees Without a Valid Department**

This example uses a `NOT EXISTS` clause. The example returns only employees who are assigned to a department identifier `department_id` not listed in the `departments` table.

```sql SQL theme={null}
SELECT name
FROM employees AS e
WHERE NOT EXISTS (
    SELECT *
    FROM departments AS d
    WHERE e.department_id = d.id
);
```

\*Output: \*`David`

#### SIMILAR TO Operator

`SIMILAR TO` is a keyword that extends the `LIKE` operator, adding more features for match filtering, including many metacharacters used in regular expressions.

`%` and `_` both act as wildcard operators, but other supported metacharacters match traditional regular expressions.

**Syntax**

```sql SQL theme={null}
WHERE string1 SIMILAR TO string2
```

This table describes the metacharacters supported by the `SIMILAR TO` keyword.

| **Metacharacter** | **Description**                                                                                                              |
| ----------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| `%`               | Repeated wildcard, matches any characters zero or more times. Equivalent usage to `LIKE`.                                    |
| `_`               | Wildcard, matches exactly one character. Equivalent usage to `LIKE`.                                                         |
| `\|`              | Alteration, meaning either of two alternatives (`a\|b` represents `a` or `b`).                                               |
| `*`               | Repetition of zero or more times.                                                                                            |
| `+`               | Repetition of one or more times.                                                                                             |
| `?`               | Repetition of zero or one times.                                                                                             |
| `{m}`             | Repetition of exactly `m` times.                                                                                             |
| `{m,n}`           | Repetition between `m` and `n` times (inclusive).                                                                            |
| `{m,}`            | Repetition of at least `m` times.                                                                                            |
| `()`              | Logical grouping.                                                                                                            |
| `[]`              | A character class, equivalent to character classes in regular expressions. Example: `[a-z]` is any lowercase English letter. |
| `^`               | Beginning of the line anchor and the negation in character classes.                                                          |
| `$`               | End of the line anchor.                                                                                                      |

`SIMILAR TO` also supports escaping these metacharacters with `\` and certain escape sequences supported by C family languages.

| **Metacharacter**   | **Description**                                                                 |
| ------------------- | ------------------------------------------------------------------------------- |
| `\a`                | The alert/bell character.                                                       |
| `\b`                | The backspace character.                                                        |
| `\B`                | A single `\` character. Equivalent to `\\`.                                     |
| `\f`                | Form feed.                                                                      |
| `\t`                | Horizontal tab.                                                                 |
| `\v`                | Vertical tab.                                                                   |
| `\xy`               | `xy` are octal digits that represent the character with the numeric value `xy`. |
| `\s`                | Matches any whitespace.                                                         |
| `\S`                | Matches any non-whitespace.                                                     |
| `\m`, `\M`, or `\y` | The boundary of a word. Equivalent to `\b` in normal regular expressions.       |
| `*`                 | The star character.                                                             |

#### Array Filters

A filter expression can use the functions `FOR_SOME()` and `FOR_ALL()` to apply a predicate against all values of an input array.

Array filter functions have the following rules:

* They can only evaluate array types.
* They can go on either the left or right side of a Boolean comparison expression, but not both sides at the same time.
* They must directly use a Boolean comparison operator.
* They cannot use the `SOME` and `ALL` SQL keywords.

**Filter Behavior with Empty Arrays**

Array filter functions have unique behavior when evaluating empty arrays.

* `FOR_ALL()` evaluates an empty array as `TRUE`.
* `FOR_SOME()` evaluates an empty array as `FALSE`.

If empty arrays must be evaluated for a different result, you can use the `ARRAY_LENGTH` function to specify a minimum array length. For example, this statement would evaluate an array as `TRUE` only if it is not empty and all values matched `%ocient%`:

```sql SQL theme={null}
ARRAY_LENGTH(array_col) > 0 AND FOR_ALL(array_col) LIKE '%ocient%
```

For details about array functions, see the [Array Functions and Operators](/array-functions-and-operators) page.

**Filter Behavior with NULL rows**

Filtering with array functions can yield different results depending on whether the array row is NULL or whether the values in the array are NULL.

If `FOR_SOME()` or `FOR_ALL()` evaluates a NULL row (the row itself is NULL, not that the array contains NULL values), then the result is always `FALSE`.

To check an array for the presence of NULL values, you must use the `IS NULL` operator. For example:

```sql SQL theme={null}
FOR_SOME(array_col) IS NULL
```

Array comparison operators, such as `@>`, `<@`, and `&&`, do not adhere to Boolean logic for NULL values. For details, see the [Array Functions and Operators](/array-functions-and-operators) page.

### GROUP BY

Groups rows with the same values into summary rows, based on a specified aggregate function.

**Syntax**

```sql SQL theme={null}
SELECT <select_list_clause> FROM table_name
    GROUP BY { column_name | expression | integer } [ , ... ]
    [ HAVING <filter_condition> ]
```

**Parameters**

| **Parameter**          | **Description**                                                                                                                                                                                                                                                                                                                              |
| ---------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_list_clause>` | A list of one or more columns for the `SELECT` query. <br />To use `GROUP BY` to summarize the query result set, at least one of the columns in `column_list` must include an aggregate function, such as `SUM()`, `COUNT()`, or `MAX()`. <br />For a list of supported functions, see the [Aggregate Functions](/aggregate-functions) page. |
| `table_name`           | The table to use for the query.                                                                                                                                                                                                                                                                                                              |
| `column_name`          | The name of one or more columns to use to group the result set. <br />If you specify multiple columns, the result set is grouped by each unique combination of values from the columns.                                                                                                                                                      |
| `expression`           | Any combination of literal values, column names, arithmetic expressions, parentheses, and function calls.                                                                                                                                                                                                                                    |
| `integer`              | An integer representing the position of the columns referenced in the `column_list`. The first position starts at `1`.                                                                                                                                                                                                                       |
| `<filter_condition>`   | A `HAVING` clause that filters the aggregated groups based on a specified condition. <br />For details, see the [\<filter\_condition>](#filtercondition) section.                                                                                                                                                                            |

**Example**

This example uses a movie database to calculate the total amount spent on movie production per year.

```sql SQL theme={null}
SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
ORDER BY 1 ASC;
```

*Output*

| year(release\_date) | sum(budget) |
| ------------------- | ----------- |
| 1968                | 10500000    |
| 1979                | 31500000    |
| 1981                | 18000000    |
| 1982                | 28000000    |
| 1992                | 14000000    |
| 1997                | 200000000   |
| 2003                | 264000000   |
| 2009                | 237000000   |
| 2010                | 350000000   |
| 2012                | 670000000   |
| 2013                | 350000000   |
| 2015                | 414000000   |

### HAVING

Filters aggregated rows based on a specified condition.

`HAVING` operates in a `GROUP BY` statement by setting a filter for the rows to be aggregated and grouped.

For information on using `GROUP BY` in a query statement, see [GROUP BY](#group-by).

**Syntax**

```sql SQL theme={null}
GROUP BY column_name HAVING <filter_condition>
```

**Parameters**

| **Parameter**        | **Description**                                                                                                                                   |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| `column_name`        | A column used for grouping that is evaluated by the `filter_condition`.                                                                           |
| `<filter_condition>` | A logical combination of Boolean predicates. <br />For information on supported logical predicates, see [\<filter\_condition>](#filtercondition). |

**Example**

This example calculates the total amount spent on movie production per year. The `HAVING` clause filters out any rows that do not have a sum of at least \$100 million.

```sql SQL theme={null}
SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
HAVING SUM(budget) > 100000000
ORDER BY 1 ASC;
```

*Output*

| year(release\_date) | sum(budget) |
| ------------------- | ----------- |
| 1997                | 200000000   |
| 2003                | 264000000   |
| 2009                | 237000000   |
| 2010                | 350000000   |
| 2012                | 670000000   |
| 2013                | 350000000   |
| 2015                | 414000000   |

### ORDER BY

Sorts the result set in ascending or descending order based on one or more specified columns.

If you specify multiple columns, they are sorted hierarchically from left to right.

**Syntax**

```sql SQL theme={null}
ORDER BY { column_position | column_name } [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ] [ , ... ]
```

**Parameters**

| **Parameter**               | **Data Type** | **Description**                                                                                                                                                                                                 |
| --------------------------- | ------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `column_position`           | Integer       | The position of a column to be used for the `ORDER BY` sorting. <br />Column positions start at `1`.                                                                                                            |
| `column_name`               | String        | The name of a column to be used for the `ORDER BY` sorting.                                                                                                                                                     |
| `ASC \| DESC`               | String        | Optional. <br />Specifies whether to sort the column in ascending (`ASC`) or descending (`DESC`) order. <br />If unspecified, defaults to `ASC`.                                                                |
| `NULLS FIRST \| NULLS LAST` | String        | Optional. <br />`NULLS FIRST` means NULL values go at the start of the result set. <br />`NULLS LAST` means NULL values go at the end of the result set. <br /><br />If unspecified, defaults to `NULLS FIRST`. |

**Example**

In this example, the `ORDER BY` statement orders the movies from newest to oldest.

```sql SQL theme={null}
SELECT release_date, title
FROM movies
ORDER BY release_date DESC;
```

*Output*

| release\_date | title                        |
| ------------- | ---------------------------- |
| 2015-06-17    | Minnows                      |
| 2015-06-09    | Triassic World               |
| 2015-04-01    | Fury 7                       |
| 2013-11-27    | Frigid                       |
| 2013-04-18    | Iron Chef 3                  |
| 2012-10-25    | Spyman 16                    |
| 2012-07-16    | Superhero 23                 |
| 2012-04-25    | The Tentpole                 |
| 2010-06-30    | The Last Airman              |
| 2010-06-16    | Merchandise Vehicle 5        |
| 2009-12-10    | CGI Why                      |
| 2003-12-01    | Swords & Scabbards           |
| 2003-10-10    | Billy the Killy              |
| 2003-07-09    | Pirates of Palm Springs      |
| 1997-11-18    | Titania                      |
| 1992-08-07    | Bang Bang Western            |
| 1982-06-25    | Blade Walker                 |
| 1981-06-12    | Raiders of the Jungle Temple |
| 1979-08-15    | Apocalypse None              |
| 1968-04-10    | Space Odyssey 6000           |

### LIMIT

Limits the number of returned rows from a query to a specified amount.

The returned rows are nondeterministic unless you use an [ORDER BY](#order-by) clause.

**Syntax**

```sql SQL theme={null}
<select_query> LIMIT limit_number
```

**Parameters**

| **Parameter**    | **Description**                                                                                                                                      |
| ---------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query>` | A valid `SELECT` query.                                                                                                                              |
| `limit_number`   | The number of rows to return from the query, specified as a positive integer or any constant scalar expression that evaluates to a positive integer. |

**Examples**

**Limit Returned Rows Using a Number**

This example uses `LIMIT` to restrict the number of returned movie titles to only three.

```sql SQL theme={null}
SELECT title FROM movies LIMIT 3;
```

*Output*

```sql SQL theme={null}
Minnows
CGI Why
Billy the Killy
```

**Limit Returned Rows Using an Expression**

The `LIMIT` SQL statement can also accept expressions. This query uses the `1+2` expression with the `sys.dummy` table to create a table of three incrementing integers. For details, see [Generate Tables Using sys.dummy](/generate-tables-using-sys-dummy).

```sql SQL theme={null}
SELECT c1 FROM sys.dummy10 LIMIT 1+2;
```

*Output*

```sql SQL theme={null}
1
2
3
```

### OFFSET

Skips a specified number of rows from the result set.

The returned rows are nondeterministic unless you use an [ORDER BY](#order-by) clause.

**Syntax**

```sql SQL theme={null}
<select_query> OFFSET offset_number
```

**Parameters**

| **Parameter**    | **Description**                                                                                                                                                           |
| ---------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query>` | A valid SELECT query.                                                                                                                                                     |
| `offset_number`  | The number of rows to skip when returning results from the query, specified as a positive integer or any constant scalar expression that evaluates to a positive integer. |

**Examples**

**Skip Rows From the Result Set Using a Number**

In this example, the `ORDER BY` statement orders the results chronologically. As a result, the `OFFSET` clause removes the oldest six movies from the result set.

```sql SQL theme={null}
SELECT release_date,
    title
FROM movies
ORDER BY release_date OFFSET 6;
```

*Output*

| release\_date | title                   |
| ------------- | ----------------------- |
| 2003-07-09    | Pirates of Palm Springs |
| 2003-10-10    | Billy the Killy         |
| 2003-12-01    | Swords & Scabbards      |
| 2009-12-10    | CGI Why                 |
| 2010-06-16    | Merchandise Vehicle 5   |
| 2010-06-30    | The Last Airman         |
| 2012-04-25    | The Tentpole            |
| 2012-07-16    | Superhero 23            |
| 2012-10-25    | Spyman 16               |
| 2013-04-18    | Iron Chef 3             |
| 2013-11-27    | Frigid                  |
| 2015-04-01    | Fury 7                  |
| 2015-06-09    | Triassic World          |
| 2015-06-17    | Minnows                 |

**Skip Rows From the Result Set Using an Expression**

The `OFFSET` SQL statement can also accept expressions. This query uses the expression `3+4` with the `sys.dummy` table to create a column of incrementing integers by skipping the first seven out of 10 rows. For details, see [Generate Tables Using sys.dummy](/generate-tables-using-sys-dummy).

```sql SQL theme={null}
SELECT c1 FROM sys.dummy10 OFFSET 3+4;
```

*Output*

```sql SQL theme={null}
8
9
10
```

### INTERSECT

Returns any rows that match between two separate `SELECT` queries. To use `INTERSECT`, the two queries must be compatible, meaning they must return the same number of columns and have similar data types.

By default, the SQL statement eliminates duplicate rows unless the query includes the optional `ALL` keyword.  Using the `DISTINCT` keyword is the same as this default behavior.

**Syntax**

```sql SQL theme={null}
<select_query1> INTERSECT [ ALL | DISTINCT ] <select_query2>
```

**Parameters**

| **Parameter**     | **Description**       |
| ----------------- | --------------------- |
| `<select_query1>` | A valid SELECT query. |
| `<select_query2>` | A valid SELECT query. |

**Example**

This example has two `SELECT` queries with an `INTERSECT` statement. The full query retrieves movies that had a budget of at least $200 million, but also earned more than $1 billion in revenues.

```sql SQL theme={null}
SELECT *
FROM movies
WHERE revenue > 1000000000
INTERSECT
SELECT *
FROM movies
WHERE budget > 20000000;
```

*Output*

| movie\_id | title                 | budget    | popularity | release\_date | revenue    | runtime | movie\_status | vote\_average | vote\_count |
| --------- | --------------------- | --------- | ---------- | ------------- | ---------- | ------- | ------------- | ------------- | ----------- |
| 597       | Titania               | 200000000 | 100.025899 | 1997-11-18    | 1845034188 | 194     | Released      | 7.5           | 7562        |
| 19995     | CGI Why               | 237000000 | 150.437577 | 2009-12-10    | 2787965087 | 162     | Released      | 7.2           | 11800       |
| 49026     | Superhero 23          | 250000000 | 112.31295  | 2012-07-16    | 1084939099 | 165     | Released      | 7.6           | 9106        |
| 10193     | Merchandise Vehicle 5 | 200000000 | 59.995418  | 2010-06-16    | 1066969703 | 103     | Released      | 7.6           | 4597        |
| 211672    | Minnows               | 74000000  | 875.581305 | 2015-06-17    | 1156730962 | 91      | Released      | 6.4           | 4571        |
| 122       | Swords & Scabbards    | 94000000  | 123.630332 | 2003-12-01    | 1118888979 | 201     | Released      | 8.1           | 8064        |
| 37724     | Spyman 16             | 200000000 | 93.004993  | 2012-10-25    | 1108561013 | 143     | Released      | 6.9           | 7604        |
| 109445    | Frigid                | 150000000 | 165.125366 | 2013-11-27    | 1274219009 | 102     | Released      | 7.3           | 5295        |
| 168259    | Furious 7             | 190000000 | 102.322217 | 2015-04-01    | 1506249360 | 137     | Released      | 7.3           | 4176        |
| 68721     | Iron Chef 3           | 200000000 | 77.68208   | 2013-04-18    | 1215439994 | 130     | Released      | 6.8           | 8806        |
| 135397    | Triassic World        | 150000000 | 418.708552 | 2015-06-09    | 1513528810 | 124     | Released      | 6.5           | 8662        |
| 24428     | The Tentpole          | 220000000 | 144.448633 | 2012-04-25    | 1519557910 | 143     | Released      | 7.4           | 11776       |

### EXCEPT

The `EXCEPT` keyword returns the result set of a first `SELECT` query minus any matching rows from a second `SELECT` query.

`EXCEPT` requires the two queries to be compatible, meaning they must return the same number of columns and have similar data types. By default, the result set eliminates duplicate rows unless the query includes the optional `ALL` keyword.  Using the `DISTINCT` keyword is the same as this default behavior.

The `EXCEPT` keyword can also be used to exclude specific columns from a `SELECT *` query. For information on that alternate usage, see the [SELECT](#select) syntax and example.

**Syntax**

```sql SQL theme={null}
<select_query1> EXCEPT [ ALL | DISTINCT ] <select_query2>
```

**Parameters**

| **Parameter**     | **Description**                                                                                                                                                                 |
| ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query1>` | A valid SELECT query that is compared to `select_query2`.<br />The results of an EXCEPT statement are the rows in `select_query1` that do not match any row in `select_query2`. |
| `<select_query2>` | A valid SELECT query that is compared to `select_query1`.                                                                                                                       |

**Example**

In this example, the first `SELECT` statement queries all rows in the `movies` table. The `EXCEPT` clause and the second `SELECT` statement eliminate from the results any rows with movies that had a budget greater than `20000000`.

```sql SQL theme={null}
SELECT *
FROM movies
EXCEPT
SELECT *
FROM movies
WHERE budget > 20000000;
```

*Output*

| title                        | budget   | popularity | release\_date | revenue   | runtime | movie\_status | vote\_average | vote\_count |
| ---------------------------- | -------- | ---------- | ------------- | --------- | ------- | ------------- | ------------- | ----------- |
| Bang Bang Western            | 14000000 | 37.380435  | 1992-08-07    | 159157447 | 131     | Released      | 7.7           | 1113        |
| Space Odyssey 6000           | 10500000 | 86.201184  | 1968-04-10    | 68700000  | 149     | Released      | 7.9           | 2998        |
| Raiders of the Jungle Temple | 18000000 | 68.159596  | 1981-06-12    | 389925971 | 115     | Released      | 7.7           | 3854        |

### UNION

Returns the combined result set of two or more `SELECT` queries.

By default, `UNION` eliminates duplicate rows from the result set unless you specify the `ALL` keyword.  Using the `DISTINCT` keyword is the same as this default behavior.

**Syntax**

```sql SQL theme={null}
<select_query1> UNION [ ALL | DISTINCT ] <select_query2> [ ... ]
```

**Parameters**

| **Parameter**   | **Data Type** | **Description**                                               |
| --------------- | ------------- | ------------------------------------------------------------- |
| `select_query1` | String        | A valid `SELECT` query that is combined with `select_query2`. |
| `select_query2` | String        | A valid `SELECT` query that is combined with `select_query1`. |

**Example**

This example uses `UNION` to merge two separate queries for identical columns into the same result set.

```sql SQL theme={null}
SELECT title,
    popularity,
    vote_average
FROM movie
WHERE popularity > 800
UNION
SELECT title,
    popularity,
    vote_average
FROM movies.movie
WHERE vote_average > 7.5;
```

*Output*

| title                        | popularity | vote\_average |
| ---------------------------- | ---------- | ------------- |
| Raiders of the Jungle Temple | 68.159596  | 7.7           |
| Minnows                      | 875.581305 | 6.4           |
| Apocalypse None              | 49.973462  | 8             |
| Superhero 23                 | 112.31295  | 7.6           |
| Billy the Killy              | 79.754966  | 7.7           |
| Bang Bang Western            | 37.380435  | 7.7           |
| Blade Walker                 | 94.056131  | 7.9           |
| Swords & Scabbards           | 123.630332 | 8.1           |
| Merchandise Vehicle 5        | 59.995418  | 7.6           |
| Space Odyssey 6000           | 86.201184  | 7.9           |

### USING

Overrides various system configurations for processing a specified query.

The `USING` keyword is required for only the first query override, not for subsequent ones.

For descriptions of the supported query configurations, see the parameter table below.

**Syntax**

```sql SQL theme={null}
<select_query>
USING
    [ SCHEDULING_PRIORITY = priority_value ]
    [ MAX_ROWS_RETURNED = max_rows ]
    [ MAX_ELAPSED_TIME = max_elapsed_time ]
    [ MAX_TEMP_DISK_USAGE = max_temp_disk_usage ]
    [ CACHE_MAX_TIME = cache_max_time ]
    [ CACHE_MAX_BYTES = cache_max_bytes ]
    [ SERVICE CLASS service_class ]
```

**Parameters**

| **Parameter**         | **Description**                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| --------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query>`      | A valid `SELECT` query.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| `priority_value`      | An optional clause that specifies the priority at which the query should run. <br />The priority value is a floating-point number, which indicates the priority compared to the other queries. The priority value does not specify any percentage of resources that the query takes or any specific degree of difference between queries.<br />If unspecified, defaults to `SCHEDULING_PRIORITY = 1.0`.                                                                                                                                                                                                                                                                                                   |
| `max_rows`            | An optional clause that limits the number of rows returned by a query. If a query exceeds this number of rows, the system kills it. <br />For example, `max_rows_returned = 10` kills any queries returning more than ten rows.                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| `max_elapsed_time`    | An optional clause that limits how long a query can run. If a query exceeds this time limit in seconds, the system kills it. <br />For example, `max_elapsed_time = 10` kills any queries taking longer than 10 seconds.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `max_temp_disk_usage` | An optional clause that limits the percentage of temporary disk used by a query. If a query exceeds this temporary disk limit, the system kills it. <br />For example, `max_temp_disk_usage = 10` kills any queries taking more than 10% of temporary disk space.                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `cache_max_time`      | An optional clause that affects whether a specified query uses results from the cache rather than executing the query. If there is a cached result with the same query text, executed less than `cache_max_time` seconds ago, the database uses that cached result. The default value is 0, and thus by default, the database does not return any cached results.<br />The cache considers all SQL Nodes, and if a potential cached result is only available on a different SQL Node, the database redirects the query to that node. If you specify the force attribute on the connection, which disables load balancing and redirection, the database considers only cached results on the current node. |
| `cache_max_bytes`     | An optional clause that controls whether the database stores the results of any specified query in the cache. The system stores the results of all queries executed using a service class specifying this attribute in the cache if the result size is smaller than this value. You can determine the size of a result set in bytes by querying the `bytes_returned` field of the `completed_queries` virtual table. The default value is 0, and thus by default, the database does not cache any results.<br />The database caches results in memory on the SQL Nodes. These results are not cached if there is insufficient memory available.                                                           |
| `service_class`       | An optional clause at the end of a query that sets a specific service class to run that query.<br />For details, see [CREATE SERVICE CLASS](/users-groups-and-service-classes#create-service-class).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |

**Example**

```sql SQL theme={null}
SELECT * FROM sys.dummy10
USING
    SCHEDULING_PRIORITY = 1.0
    MAX_ROWS_RETURNED = 10
    MAX_ELAPSED_TIME = 10
    MAX_TEMP_DISK_USAGE = 10;
```

*Output*

```sql SQL theme={null}
c1
-----------
1
2
3
4
5
6
7
8
9
10
```

### TRACE

An optional clause that executes the query, but discards the original result set. Instead, `TRACE` returns a result set of tracing data that describes the execution of the query.

Append the `TRACE` SQL statement to the end of a query. The statement can include optional parameters to control its frequency and level of detail.

To understand the results of this statement, see [TRACE Results](#trace-results).

<Info>
  Trace queries have the same effect to the workload as a query run without the clause.
</Info>

<Warning>
  Contact Ocient Support for guidance in using the TRACE SQL statement.
</Warning>

**Syntax**

```sql SQL theme={null}
<select_query> TRACE
    [ FREQUENCY frequency_int ]
    [ RESOLUTION resolution_int ]
```

**Parameters**

| **Parameter**    | **Description**                                                                                                                                                                                                                 |
| ---------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query>` | A valid `SELECT` query.                                                                                                                                                                                                         |
| `frequency_int`  | Controls how often the database samples trace events during query execution in milliseconds.<br />If unspecified, defaults to 500 milliseconds.                                                                                 |
| `resolution_int` | Determines the level of detail captured for a trace or event. <br />If the resolution of a trace is greater than the resolution of an event, the database records the event.<br />If unspecified, defaults to 100 milliseconds. |

#### TRACE Results

Each row of the trace output provides some information about what an operator instance has done in the time between the previous trace sample and the most current sample.

| **Column**       | **Description**                                                                                                                                              |
| ---------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `plan_parent_id` | The UUID of the parent of the operator instance in the query plan. If an operator has multiple parents, this field specifies the UUID of one of the parents. |
| `plan_op_id`     | The UUID of the operator in the query plan.                                                                                                                  |
| `operator_type`  | The type of operator that the database uses to sample trace events.                                                                                          |
| `node_id`        | The UUID of the node where this operator executes.                                                                                                           |
| `code_id`        | The identifier of the VM core where this operator instance executes. Core identifiers are unique for each node.                                              |
| `op_id`          | The identifier of the operator instance. Identifiers are unique for each node.                                                                               |
| `time`           | The time in milliseconds when the database collects the information for the trace event relative to the start of the query.                                  |
| `group`          | Group related trace events together.                                                                                                                         |
| `sample`         | The sample of trace event data. For possible values, see the following table.                                                                                |
| `value`          | The value of trace event data.                                                                                                                               |

**Sample and Value Columns**

The `sample` and `value` columns describe what occurred in a specified trace period.

| **Sample**            | **Description**                                                                 |
| --------------------- | ------------------------------------------------------------------------------- |
| `ROWS_IN`             | The number of rows an operator instance has read from its children operators.   |
| `ROWS_OUT`            | The number of rows the operator instance returns.                               |
| `BLOOM_FILTERED_ROWS` | The number of rows that this operator instance discards by using Bloom filters. |
| `SCHEDULE_CYCLE`      | The number of cycles in the schedule for this operator instance.                |
| `OOM_CYCLE`           | The number of out-of-memory cycles in the schedule for this operator.           |
| `INITIALIZE`          | The time when the database initializes the operator.                            |
| `FINALIZE`            | The time when the database finalizes the operator.                              |

**Example**

```sql SQL theme={null}
SELECT * FROM sys.dummy10 TRACE;
```

*Output*

| plan\_parent\_id                     | plan\_op\_id                         | operator\_type   | node\_id                             | core\_id | op\_id | time | group                                      | sample                | value |
| ------------------------------------ | ------------------------------------ | ---------------- | ------------------------------------ | -------- | ------ | ---- | ------------------------------------------ | --------------------- | ----- |
| 77041d2b-f7ca-49d5-a3f5-4b75b0782ed1 | 498690cf-4a9b-4c1f-affa-417f31fddc2d | RENAME\_OPERATOR | 08ca7b05-1e4d-455f-9f46-2174ed048d33 | 4        | 8441   | 0    | xg::db::vm::operators::operatorTraceEvents | ROWS\_IN              | 1     |
| 77041d2b-f7ca-49d5-a3f5-4b75b0782ed1 | 498690cf-4a9b-4c1f-affa-417f31fddc2d | RENAME\_OPERATOR | 08ca7b05-1e4d-455f-9f46-2174ed048d33 | 4        | 8441   | 0    | xg::db::vm::operators::operatorTraceEvents | ROWS\_OUT             | 1     |
| 77041d2b-f7ca-49d5-a3f5-4b75b0782ed1 | 498690cf-4a9b-4c1f-affa-417f31fddc2d | RENAME\_OPERATOR | 08ca7b05-1e4d-455f-9f46-2174ed048d33 | 4        | 8441   | 0    | xg::db::vm::operators::operatorTraceEvents | BLOOM\_FILTERED\_ROWS | 0     |

### TAG

An optional clause that adds one or more tags to the SQL query. You can also add tags to a common table expression in the `WITH` clause. After you define tags, you can find them in the `sys.queries` and `sys.completed_queries` system catalog tables.

**Syntax**

```sql SQL theme={null}
<select_query> <tag> [ ... ]

<tag> ::= TAG <tag_identifier>
```

**Parameters**

| **Parameter**    | **Description**                                                                                                                     |
| ---------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query>` | A valid `SELECT` query.                                                                                                             |
| `tag_identifier` | Identifier of the tag for the query. Enclose the identifier in double quotes if it contains any special characters, such as spaces. |

**Examples**

**SQL Query with One Tag**

Select the count of a table with one row and tag the query with the name `count1`.

```sql SQL theme={null}
SELECT COUNT(*) FROM sys.dummy1 TAG count1;
```

**SQL Query with Multiple Tags**

Select the device model and tag this query with two names `device_model` and `phones`.

```sql SQL theme={null}
SELECT device_model
FROM system.adtech_flat
LIMIT 1
TAG device_model
TAG phones;
```

**Common Table Expression with One Tag**

Define a common table expression with the `average_budget` tag. You can also add another tag for the whole query `overall_budget`.

```sql SQL theme={null}
WITH avg_budget_table (average_budget) AS (
    SELECT AVG(budget)
    FROM movies
    TAG average_budget
)
SELECT title,
    budget,
    revenue
FROM movies,
    avg_budget_table
WHERE budget > avg_budget_table.average_budget
TAG overall_budget;
```

### String Literals and Escape Sequences

All string literals in SQL statements must be enclosed in single quotes. To use a single quote within a string, you can use another single quote as an escape, `''`\*. \*

For other escape sequences, include an `e` character before the string literal, i.e., before the opening single quote. This directs the system to recognize escape sequences in the string literal. This means:

* All single `\` characters in the string now escape themselves.
* Any subsequent character after the `\` is also escaped if it matches an escape sequence (see the table).

If you use escape sequences, be prepared that you might need to alter strings that include `\`, such as directory paths.

**Supported Escape Sequences**

| **Escape Sequence** | **Description**                        |
| ------------------- | -------------------------------------- |
| `''`                | Single quotation mark                  |
| `\"`                | Double quotation mark                  |
| `\n`                | Newline character                      |
| `\r`                | Carriage return character              |
| `\f`                | Form feed character (i.e., page break) |
| `\b`                | Backspace                              |
| `\\`                | Backslash                              |
| `\t`                | Tab                                    |

**Examples**

These examples show how the system interprets strings with and without escape sequences.

**String Without an Escape Sequence**

This example selects the simple string literal `'\my\directory\path'` that does not use escape sequences.

```sql SQL theme={null}
SELECT '\my\directory\path';
```

\*Output: \*`\my\directory\path`

**String With an Escape Sequence**

This example selects the simple string literal `'\my\directory\path'` and uses an escape sequence. The result omits the backslashes.

```sql SQL theme={null}
SELECT e'\my\directory\path';
```

\*Output: \*`mydirectorypath`

**String With an Escape Sequence to Retain the Backslashes**

This example uses the `'\\my\\directory\\path'` string with an escape sequence to include backslashes in the result.

```sql SQL theme={null}
SELECT e'\\my\\directory\\path';
```

\*Output: \*`\my\directory\path`

**Escape Sequences with Regular Expressions**

<Info>
  Be careful using escape sequences with strings that also use regular expressions. Escape sequences in SQL syntax override those in regular expressions.
</Info>

In this example, the [REGEXP\_SUBSTR](/character-and-binary-functions#regexp_substr) function uses the regular expression `\w+` to match any word characters.

```sql SQL theme={null}
SELECT REGEXP_SUBSTR(
    'abcdefghijklmnopqrstuvwxyz',
    '\w+'
    );
```

\*Output: \*`abcdefghijklmnopqrstuvwxyz`

The function behaves differently if the regular expression is an escape sequence because it overrides the `\` character. The regular expression searches only for the character `w`.

```sql SQL theme={null}
SELECT REGEXP_SUBSTR(
    'abcdefghijklmnopqrstuvwxyz',
    e'\w+'
    );
```

\*Output: \*`w`

## Related Links

[SQL Reference](/sql-reference)

[Data Control Language (DCL) Statement Reference](/data-control-language-dcl-statement-reference)

[Data Definition Language (DDL) Statement Reference](/data-definition-language-ddl-statement-reference)