Data Query Language (DQL) Statement Reference

The OcientAIQ™ Unified Data Platform 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

[ WITH [ RECURSIVE ] ... ]
SELECT ...
    [ EXCEPT(...) ]
[ FROM ...
    [ JOIN ... ] ]
[ WHERE ... ]
[ GROUP BY ...
    [ HAVING ... ] ]
[ QUALIFY ... ]
[ WINDOW ... ]
[ LIMIT ... PER (...) ]
[ OFFSET ... PER (...) ]
[ ORDER BY ... ]
[ LIMIT ... | FETCH ... ]
[ OFFSET ... ]
[ INTERSECT ... ]
[ EXCEPT ... ]
[ UNION ... ] ]
[ USING ... ]
[ TRACE ... ]
[ TAG ... ]

Grouped LIMIT ... PER and OFFSET ... PER must appear before ORDER BY. Scalar LIMIT and OFFSET (without PER) appear after ORDER BY. See the LIMIT and OFFSET sections for details on both forms.

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

Querying SQL Statement Reference

Ocient supports the following SQL statements.

WITH

Assigns a name to a common table expression (CTE) so that the main query can reference an auxiliary query. This helps break complex queries into smaller parts. Syntax

SQL

WITH [ RECURSIVE ]
    <cte_name> [ ( <cte_column> [ ,... ] ) ]
    AS ( <sub_query> )
    [ , <cte_name> [ ( <cte_column> [ ,... ] ) ]
      AS ( <sub_query> ) [ ,... ] ]
<select_query>

Parameters

Parameter	Description
`RECURSIVE`	Optional. Declares the CTE as recursive, which lets the CTE reference itself. For details, see WITH RECURSIVE.
`<cte_name>`	A name for the common table expression populated with the result set from the `sub_query` statement. When you define multiple CTEs in a single `WITH` clause, each CTE can reference any CTE defined before it.
`<cte_column>`	Optional. 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. `sub_query` can use regular query commands, including ORDER BY, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, WHERE, GROUP BY, and HAVING. See the respective SQL statement reference sections for usage rules.
`<select_query>`	The main SELECT query. 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

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

WITH RECURSIVE

The WITH RECURSIVE clause defines a recursive common table expression (CTE) that references itself. Recursive CTEs enable iterative computations, such as traversing hierarchical data, generating sequences, and walking graph structures. A recursive CTE consists of two parts joined by UNION ALL or UNION:

Anchor member — A non-recursive base query that produces the initial result set.
Recursive member — A query that references the CTE by name and produces additional rows in each iteration.

The recursion terminates when the recursive member returns no rows. The RECURSIVE keyword is optional. If you omit it but the CTE references itself, the database automatically detects the self-reference and treats the CTE as recursive. Syntax

SQL

WITH [ RECURSIVE ] <cte_name> [ ( <cte_column> [ ,... ] ) ] AS (
    <anchor_query>
    { UNION ALL | UNION }
    <recursive_query>
)
<main_query>

Parameters

Parameter	Description
`RECURSIVE`	Optional. Declares the CTE as recursive. If you omit this keyword, the system automatically detects self-referencing CTEs.
`<cte_name>`	A name for the recursive CTE. The recursive member references this name to access rows from the previous iteration.
`<cte_column>`	Optional. A list of column names for the CTE result set. If you provide this list, the column count must match the number of columns that the anchor query returns.
`<anchor_query>`	A non-recursive query that produces the initial (base case) rows.
`<recursive_query>`	A query that references `<cte_name>`. In each iteration, the recursive member reads only the new rows produced by the previous iteration (not the full accumulated result). The recursion stops when this query returns no rows.
`<main_query>`	The primary query that reads from the completed recursive CTE result. This parameter can be a `SELECT` SQL statement or a DML statement. For supported DML statements and examples, see Use Recursive CTEs with DML Statements.

Choose Between UNION ALL and UNION Keywords

UNION ALL — Accumulates all rows from every iteration without deduplication. Use this keyword for most recursive queries.
UNION — Removes duplicate rows at each iteration by comparing new rows against all previously accumulated rows. Use this keyword to guarantee termination on cyclic data, for example, a graph traversal where a node can appear in multiple paths. This keyword is the UNION set operator, which is unrelated to the DISTINCT keyword that the recursive member cannot contain.

Configuration The sql.maxRecursiveQueryIterations system parameter controls the maximum number of iterations a recursive CTE can perform, which prevents runaway recursion. The system enables recursive queries by default.

Value	Behavior
`0`	The system disables recursive queries. Any recursive CTE returns an error.
`> 0`	The maximum number of iterations. Exceeding this limit returns an error. The default is `1000`.
`-1`	Unlimited iterations. The system applies no iteration cap, so a recursive query over cyclic data that uses the `UNION ALL` keyword does not terminate on its own.

You can set this parameter with the ALTER SYSTEM ALTER CONFIG SET statement. The change applies system-wide and propagates asynchronously, so there might be a brief delay before the new value takes effect on all SQL Nodes.

SQL

ALTER SYSTEM ALTER CONFIG
    SET 'sql.maxRecursiveQueryIterations' = '100';

Structural Rules The recursive member of a CTE must follow these rules:

The recursive member must use the UNION ALL or UNION keywords to combine with the anchor. The member cannot use the EXCEPT or INTERSECT set operator keywords.
The recursive member cannot contain the GROUP BY clause, the HAVING clause, or aggregate functions.
The recursive member cannot contain the DISTINCT keyword, the LIMIT clause, or the OFFSET clause.
The recursive member cannot contain window functions.
The recursive reference cannot appear on the NULL-producing side of an outer join (LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN).
The recursive reference cannot appear inside NOT IN, NOT EXISTS, or other negation predicates.
The recursive member cannot reference the CTE more than once, which rules out self-joins and nonlinear recursion.
Two CTEs cannot reference each other. The system does not support mutual recursion.

EXPLAIN SQL Statement Support Executing the EXPLAIN SQL statement on a recursive query returns the data flow script that the database generates to execute the recursion. You can execute the EXPLAIN statement on individual statements within that script to inspect their execution plans. Examples Generate a Sequence of Integers This recursive CTE generates a sequence of integers from 1 to 10.

SQL

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM counter
    WHERE n < 10
)
SELECT n FROM counter;

Output

n
1
2
3
4
5
6
7
8
9
10

Traverse an Employee Hierarchy This example traverses an employee hierarchy starting from the top-level manager (WHERE manager_id is NULL) and walks down to all reports.

SQL

WITH RECURSIVE org_tree(staff_id, name, manager_id, depth) AS (
    SELECT staff_id, first_name, manager_id, 0
    FROM company.staffs
    WHERE manager_id IS NULL
    UNION ALL
    SELECT s.staff_id, s.first_name, s.manager_id,
        org_tree.depth + 1
    FROM company.staffs s
    INNER JOIN org_tree
        ON org_tree.staff_id = s.manager_id
)
SELECT staff_id, name, depth
FROM org_tree
ORDER BY depth, name;

Output

staff_id	name	depth
1	Diana	0
2	Ethan	1
3	Farah	1
4	Gabriel	2
5	Hana	2
6	Ivan	2

Traverse a Graph with Cycles This example uses the UNION keyword to traverse a graph with cycles and guarantee termination. Because the UNION keyword removes duplicate rows, the recursion stops when the database does not recover any new nodes.

SQL

WITH RECURSIVE reachable(node_id) AS (
    SELECT 1
    UNION
    SELECT e.target_id
    FROM edges e
    INNER JOIN reachable r
        ON r.node_id = e.source_id
)
SELECT node_id FROM reachable
ORDER BY node_id;

Output

node_id
1
2
3
4

Use Recursive CTEs with DML Statements

You can use recursive CTEs with INSERT INTO ... SELECT, CREATE TABLE AS SELECT (CTAS), and DELETE SQL statements. In each case, the WITH RECURSIVE clause follows the table name of the DML statement, for example, after INSERT INTO table_name. Examples Insert Rows Using a Recursive CTE This example inserts the result of a recursive CTE into an existing table. The target table must already exist with a compatible schema.

SQL

INSERT INTO number_sequences
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100
)
SELECT n FROM seq;

This statement inserts 100 rows (integers 1 through 100) into the number_sequences table.

SQL

SELECT COUNT(*) AS row_count FROM number_sequences;

Output

row_count
100

Create a Table Using a Recursive CTE This example creates a new table from the result of a recursive CTE. For the CREATE TABLE AS SELECT SQL statement, add the AS keyword between the table name and the WITH RECURSIVE clause.

SQL

CREATE TABLE fibonacci AS
WITH RECURSIVE fib(n, a, b) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n + 1, b, a + b FROM fib WHERE n < 20
)
SELECT n, a AS fib_value FROM fib;

Querying the created table returns the first 20 Fibonacci numbers.

SQL

SELECT * FROM fibonacci;

Output

n	fib_value
1	0
2	1
3	1
4	2
5	3
6	5
7	8
8	13
9	21
10	34
11	55
12	89
13	144
14	233
15	377
16	610
17	987
18	1597
19	2584
20	4181

Delete Rows Using a Recursive CTE This example uses a recursive CTE to delete a range of rows from the number_sequences table. The CTE generates the rows to delete, and the DELETE SQL statement removes any row whose n value matches the CTE result.

SQL

DELETE FROM number_sequences
WITH RECURSIVE seq(n) AS (
    SELECT 91
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100
)
WHERE n IN (SELECT n FROM seq);

This statement deletes 10 rows (integers 91 through 100), leaving 90 rows in the number_sequences table.

SQL

SELECT COUNT(*) AS row_count FROM number_sequences;

Output

row_count
90

Use Recursive CTEs in Views

You can define a view that contains a recursive CTE. The CREATE VIEW SQL statement succeeds even when the sql.maxRecursiveQueryIterations configuration parameter is 0 (disabled), because the system detects the recursion when you query the view, not when you create it.

SQL

CREATE VIEW org_hierarchy AS
WITH RECURSIVE org_tree(staff_id, name, depth) AS (
    SELECT staff_id, first_name, 0
    FROM company.staffs
    WHERE manager_id IS NULL
    UNION ALL
    SELECT s.staff_id, s.first_name, org_tree.depth + 1
    FROM company.staffs s
    INNER JOIN org_tree
        ON org_tree.staff_id = s.manager_id
)
SELECT * FROM org_tree;

Querying the view returns the employee hierarchy starting from the top-level manager.

SQL

SELECT * FROM org_hierarchy ORDER BY depth, name;

Output

staff_id	name	depth
1	Diana	0
2	Ethan	1
3	Farah	1
4	Gabriel	2
5	Hana	2
6	Ivan	2

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 and HAVING sections. For information on using SELECT as a subquery for a common table expression, see the WITH section.

SELECT * queries that lack a FROM clause automatically reference the sys.dummy1 table.For example, SELECT *; is the same as SELECT * FROM sys.dummy1;.

Syntax

SQL

SELECT [ ALL | DISTINCT ]
    [ * [ EXCEPT ( column_name [ , ... ] ) ]
    | <select_list_entry> [ , ... ] ]
    [ <from_clause> ]

Parameters

Parameter	Description
`ALL`	`SELECT` and `SELECT ALL` are both the same. 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

<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. 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 documentation. Examples

Using SELECT *

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

SQL

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

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

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
    );

Insert four records into the products table.

SQL

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

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

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

<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 section.
`table_name`	The names of one or more tables or views that you want to query. 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 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. Each subquery must be enclosed in parentheses with an optional correlation name. For details, see the 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. For details, see the JOIN section.

JOIN

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

SQL

<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. For details, see SELECT.
`<table_reference1>`	Either a table name or a full `SELECT` in parentheses `table_reference1` takes precedence for returning rows for `LEFT OUTER JOIN`, `SEMI JOIN`, and `ANTI JOIN`. For specific rules, see Types of Join Operations.
`<table_reference2>`	Either a table name or a full `SELECT` in parentheses function. `table_reference2` takes precedence for returning rows for `RIGHT OUTER JOIN`. For specific rules, see Types of Join Operations.
`table1_column`	A column from either `table_reference` 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` 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>` )

Ocient supports the following types of JOIN operations. Syntax

SQL

<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. `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. `RIGHT JOIN` performs the same operation as `RIGHT OUTER JOIN`.
`FULL OUTER JOIN`	Returns all matched and unmatched rows. `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.

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

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.

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

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

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

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

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

SELECT game_name,
    genre_name
FROM video_games.genre
    CROSS JOIN video_games.game;

Output

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

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

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

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

<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`. For details, see the <filter_condition> section.
`<sub_query>`	A subquery with a filter condition that follows the `EXISTS` clause. For details, see the EXISTS section.

<filter_condition>

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

SQL

<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. The pattern is enclosed in parentheses and can include the wildcard characters `%`, representing zero, one or multiple characters, and `_` representing a single character. 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 section.	`'abc' SIMILAR TO 'abc'`
`BETWEEN value1 AND value2`	Requires two values. 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. 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. For details, see Array Filters.	`FOR_SOME(col_int_array) > 10` `FOR_SOME(col_int_array) > 10`
`FOR_ALL()`	Evaluates all values of an array to determine if all meet the filter criteria. In the example, the `FOR_ALL()` operator would return any rows of the column `col_int_array` where all values are greater than 10. For details, see Array Filters.	`FOR_ALL(col_int_array) > 10` `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. 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`. 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

CREATE TABLE departments (
    id INT,
    name VARCHAR(50)
);

Create the employees table for employee data.

SQL

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

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

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

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

Text

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

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

Text

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

SELECT *
FROM departments d
WHERE d.id != 3
AND EXISTS (
    SELECT *
    FROM employees e
    WHERE e.department_id < 4
);

Output

Text

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

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

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

ARRAY_LENGTH(array_col) > 0 AND FOR_ALL(array_col) LIKE '%ocient%

For details about array functions, see the 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

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

GROUP BY

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

SQL

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. 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()`. For a list of supported functions, see the 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. 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. For details, see the <filter_condition> section.

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

SQL

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

SQL

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. For information on supported logical predicates, see <filter_condition>.

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

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

QUALIFY

Filters rows based on the results of window functions. The QUALIFY clause is analogous to HAVING for aggregate functions, except that QUALIFY filters rows after the system computes the window functions. The system evaluates the QUALIFY clause after the window functions but before the DISTINCT keyword and ORDER BY clause. The logical execution order is:

FROM
WHERE
GROUP BY
HAVING
SELECT (where the Ocient System computes the window functions)
QUALIFY
DISTINCT
ORDER BY
LIMIT and OFFSET

Syntax

SQL

<select_query>
QUALIFY <expression>

Parameters

Parameter	Description
`<select_query>`	A valid `SELECT` query that includes one or more window functions.
`<expression>`	A Boolean expression that references window function results. You can reference window function aliases or inline window expressions.

Examples Top-N per Group This example returns the top three highest-paid employees per department.

SQL

SELECT
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
QUALIFY rn <= 3
ORDER BY department, salary DESC;

Output

employee_id	department	salary	rn
105	Engineering	145000	1
102	Engineering	130000	2
110	Engineering	120000	3
201	Marketing	115000	1
204	Marketing	105000	2
207	Marketing	98000	3
301	Sales	125000	1
305	Sales	112000	2
308	Sales	108000	3

Filter on a Running Aggregate This example returns only rows where the running sum reaches at least 100.

SQL

SELECT
    id,
    val,
    SUM(val) OVER (ORDER BY id) AS running_sum
FROM transactions
QUALIFY running_sum >= 100
ORDER BY id;

Output

id	val	running_sum
4	40	100
5	25	125
6	60	185

Add Additional Filtering Using the WHERE Clause Return rows where the value val is greater than 20. The WHERE clause filters rows before the system computes the window functions. The QUALIFY clause filters rows after the computation.

SQL

SELECT
    id,
    grp,
    val,
    ROW_NUMBER() OVER (PARTITION BY grp ORDER BY val DESC) AS rn
FROM my_table
WHERE val > 20
QUALIFY rn <= 1
ORDER BY grp;

Output

id	grp	val	rn
3	A	85	1
7	B	72	1
12	C	90	1

Rank Items Using the DENSE_RANK Function This example ranks each row within each group and returns only the first two ranks.

SQL

SELECT
    grp,
    category,
    DENSE_RANK() OVER (PARTITION BY grp ORDER BY category) AS rnk
FROM my_table
QUALIFY rnk <= 2;

Output

grp	category	rnk
A	alpha	1
A	alpha	1
A	beta	2
A	beta	2
B	alpha	1
B	alpha	1
B	gamma	2
B	gamma	2
C	beta	1
C	delta	2
C	delta	2

The QUALIFY clause cannot contain window functions inside the recursive member of a recursive common table expression (CTE).

WINDOW

Defines one or more named window specifications that you can reference in window function OVER clauses within the same SELECT SQL statement. Named windows reduce repetition when multiple window functions share the same partitioning, ordering, or framing. Place the WINDOW clause after the QUALIFY clause, if present, and before the ORDER BY clause. Syntax

SQL

<select_query>
WINDOW <window_name> AS ( [ <base_window_name> ]
    [ PARTITION BY <part_list> ]
    [ ORDER BY <order_list> [ ASC | DESC ] ]
    [ <frame_clause> ] )
    [ , <window_name> AS ( ... ) ... ]

Parameters

Parameter	Description
`<window_name>`	A name for the window specification. Use this name in `OVER` clauses to reference the window. Names are case-insensitive.
`<base_window_name>`	Optional. The name of another window that you define in the same `WINDOW` clause. The current window inherits the properties of the base window and can add non-conflicting clauses.
`<part_list>`	Optional. One or more columns by which to partition the rows.
`<order_list>`	Optional. One or more columns by which to order rows within each partition.
`<frame_clause>`	Optional. A `ROWS` or `RANGE` frame specification. For details, see ROWS or RANGE.

Reference a Named Window You can reference a named window in an OVER clause in two ways. Examples Bare Named Window Reference

SQL

SELECT SUM(salary) OVER w
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY hire_date);

Parenthesized Named Window Reference

SQL

SELECT SUM(salary) OVER (w ORDER BY hire_date)
FROM employees
WINDOW w AS (PARTITION BY department);

The system returns an error if you specify a clause that the base window already defines. For example, you cannot add the ORDER BY clause when the base window already has this clause. Window Chaining Named windows can reference other named windows, which lets you build up specifications incrementally.

SQL

SELECT SUM(salary) OVER w3
FROM employees
WINDOW w1 AS (PARTITION BY department),
       w2 AS (w1 ORDER BY hire_date),
       w3 AS (w2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

You can reference a named window that you define later in the same WINDOW clause.

You cannot create a cyclic reference between named windows.

Window Scope and Supported Query Contexts A named window applies only within the SELECT SQL statement that defines it. Named windows are not visible inside subqueries, and a subquery cannot expose its named windows to the outer query.

You cannot reference a named window outside the SELECT statement that defines it.

Within that scope, named windows work in any context where a SELECT is valid:

SELECT SQL statements
CREATE VIEW and CREATE OR REPLACE VIEW statements
CREATE TABLE ... AS SELECT (CTAS) statement
INSERT INTO ... SELECT statement
Subqueries and CTEs

Examples Use a Named Window with Multiple Functions This example applies one named window to multiple functions to calculate row numbers and running aggregates within each department.

SQL

SELECT
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER w AS row_num,
    SUM(salary) OVER w AS running_sum,
    AVG(salary) OVER w AS running_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC)
ORDER BY department, salary DESC;

Output

department	employee_name	salary	row_num	running_sum	running_avg
Engineering	Alice	145000	1	145000	145000.0
Engineering	Bob	130000	2	275000	137500.0
Engineering	Carol	120000	3	395000	131666.67
Engineering	Frank	115000	4	510000	127500.0
Engineering	Grace	108000	5	618000	123600.0
…	…	…	…	…	…
Marketing	Dana	115000	1	115000	115000.0
Marketing	Eve	105000	2	220000	110000.0
Marketing	Sam	98000	3	318000	106000.0
…	…	…	…	…	…
Sales	Zach	125000	1	125000	125000.0
Sales	Amy	112000	2	237000	118500.0
Sales	Ben	108000	3	345000	115000.0

Chain Multiple Named Windows This example chains three named windows to build a complex window specification incrementally. The w_base window partitions rows by department, the w_ordered window adds ordering by hire date, and the w_framed window adds a frame that runs from the first row to the current row. The query uses the w_framed window to calculate a running total of salary within each department, ordered by hire date.

SQL

SELECT
    department,
    employee_name,
    salary,
    SUM(salary) OVER w_framed AS dept_running_total
FROM employees
WINDOW w_base AS (PARTITION BY department),
       w_ordered AS (w_base ORDER BY hire_date),
       w_framed AS (w_ordered ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY department, hire_date;

Output

department	employee_name	salary	dept_running_total
Engineering	Carol	120000	120000
Engineering	Frank	115000	235000
Engineering	Bob	130000	365000
Engineering	Grace	108000	473000
Engineering	Nancy	105000	578000
…	…	…	…
Marketing	Eve	105000	105000
Marketing	Dana	115000	220000
Marketing	Sam	98000	318000
…	…	…	…
Sales	Zach	125000	125000
Sales	Amy	112000	237000
Sales	Ben	108000	345000

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

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. 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. Specifies whether to sort the column in ascending (`ASC`) or descending (`DESC`) order. If unspecified, defaults to `ASC`.
`NULLS FIRST \| NULLS LAST`	String	Optional. `NULLS FIRST` means NULL values go at the start of the result set. `NULLS LAST` means NULL values go at the end of the result set. If unspecified, defaults to `NULLS FIRST`.

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

SQL

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 clause. Syntaxes Limit Rows

SQL

<select_query> LIMIT limit_number

Limit Rows per Group

SQL

<select_query> LIMIT limit_number PER ( <group_columns> [ ORDER BY <order_list> ] )

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.
`<group_columns>`	Optional. One or more columns that define groups. When you specify this parameter with the `PER` clause, the limit applies independently to each distinct combination of the grouping columns.
`<order_list>`	Optional. One or more columns that define the within-group ordering. This parameter determines which rows the database selects within each group.

Examples Return the First Three Rows Using the LIMIT Clause This example uses LIMIT to restrict the number of returned movie titles to only three.

SQL

SELECT title FROM movies LIMIT 3;

Output

SQL

Minnows
CGI Why
Billy the Killy

Return a Calculated Number of Rows Using the LIMIT Clause 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.

SQL

SELECT c1 FROM sys.dummy10 LIMIT 1+2;

Output

SQL

1
2
3

Return the Top N Rows per Group Using the LIMIT Clause This example returns the top three salaries in each department. The PER clause extends the LIMIT clause to operate per group rather than globally.

SQL

SELECT id, department, salary
FROM employees
LIMIT 3 PER (department ORDER BY salary DESC);

Output

id	department	salary
201	Marketing	115000
204	Marketing	105000
207	Marketing	98000
105	Engineering	145000
102	Engineering	130000
110	Engineering	120000
301	Sales	125000
305	Sales	112000
308	Sales	108000

The query returns the top three highest-paid employees per department. The ORDER BY salary DESC clause inside the PER clause specifies within-group ordering. The inter-group ordering is nondeterministic because there is no outer ORDER BY clause. Return an Arbitrary Set of Rows per Group Using the LIMIT Clause When you do not specify the ORDER BY clause inside the PER clause, the query returns an arbitrary set of N rows per group.

SQL

SELECT id, department, salary
FROM employees
LIMIT 5 PER (department);

Output

id	department	salary
308	Sales	108000
301	Sales	125000
305	Sales	112000
114	Engineering	100000
109	Engineering	82000
108	Engineering	85000
104	Engineering	98000
111	Engineering	95000
209	Marketing	92000
212	Marketing	82000
213	Marketing	79000
214	Marketing	76000
201	Marketing	115000

The result contains five rows per department (or fewer if the department has fewer than five rows total). Both the rows selected and their order are nondeterministic. Return Rows per Group Using Multiple Grouping Columns You can specify multiple columns for grouping.

SQL

SELECT id, region, department, salary
FROM employees
LIMIT 2 PER (region, department ORDER BY salary DESC);

Output

id	region	department	salary
301	West	Sales	125000
305	West	Sales	112000
209	West	Marketing	92000
210	West	Marketing	88000
201	East	Marketing	115000
204	East	Marketing	105000
114	West	Engineering	100000
104	West	Engineering	98000
308	East	Sales	108000
105	East	Engineering	145000
102	East	Engineering	130000

Order the Final Result Using an Outer ORDER BY Clause This example returns the top three salaries per department using the LIMIT ... PER clause, then sorts the combined result by department and descending salary using an outer ORDER BY clause. The ORDER BY clause inside the PER clause controls which rows the database selects within each group. An outer ORDER BY clause controls the final result ordering.

SQL

SELECT id, department, salary
FROM employees
LIMIT 3 PER (department ORDER BY salary DESC)
ORDER BY department, salary DESC;

Output

id	department	salary
105	Engineering	145000
102	Engineering	130000
110	Engineering	120000
201	Marketing	115000
204	Marketing	105000
207	Marketing	98000
301	Sales	125000
305	Sales	112000
308	Sales	108000

FETCH

Limits the number of returned rows from a query. This is an alternative to the LIMIT clause. The query returns rows in nondeterministic order unless you use an ORDER BY clause.

You can use FETCH FIRST and FETCH NEXT interchangeably. The system accepts both ROW and ROWS regardless of the value of limit_number.

Syntax

SQL

<select_query> FETCH { FIRST | NEXT } [ limit_number ] { ROW | ROWS } [ ONLY ]

Parameters

Parameter	Description
`<select_query>`	A valid `SELECT` query.
`limit_number`	Optional. The number of rows to return from the query, specified as a positive integer. The default value is `1`.

Examples Fetch First Three Rows This example uses FETCH FIRST to restrict the number of returned movie titles to only three.

SQL

SELECT title FROM movies FETCH FIRST 3 ROWS ONLY;

Output

SQL

Minnows
CGI Why
Billy the Killy

Fetch First Row (Default) If you omit limit_number, FETCH FIRST ROW ONLY returns a single row.

SQL

SELECT title FROM movies FETCH FIRST ROW ONLY;

Output

SQL

CGI Why

OFFSET

Skips a specified number of rows from the result set. The returned rows are nondeterministic unless you use an ORDER BY clause. Syntaxes Skip Rows

SQL

<select_query> OFFSET offset_number

Skip Rows per Group

SQL

<select_query> OFFSET offset_number PER ( <group_columns> [ ORDER BY <order_list> ] )

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.
`<group_columns>`	Optional. One or more columns that define groups. When you specify this parameter with the `PER` clause, the offset applies independently to each distinct combination of the grouping columns.
`<order_list>`	Optional. One or more columns that define the within-group ordering. This parameter determines which rows the system skips within each group.

Examples Skip the Six Oldest Rows Using the OFFSET Clause 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

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 a Calculated Number of Rows Using the OFFSET Clause The OFFSET clause 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.

SQL

SELECT c1 FROM sys.dummy10 OFFSET 3+4;

Output

SQL

8
9
10

Paginate Within Each Group Using the OFFSET and LIMIT Clauses This example skips the five highest salaries in each department, then returns the next five, producing the second page of a five-row-per-page pagination within each department. The PER clause extends the OFFSET clause to skip rows per group rather than globally. Combine the OFFSET ... PER clause with the LIMIT ... PER clause to paginate within each group.

SQL

SELECT id, department, salary
FROM employees
OFFSET 5 PER (department ORDER BY salary DESC)
LIMIT 5 PER (department ORDER BY salary DESC);

Output

id	department	salary
113	Engineering	105000
114	Engineering	100000
104	Engineering	98000
115	Engineering	97000
111	Engineering	95000
211	Marketing	85000
212	Marketing	82000
213	Marketing	79000
214	Marketing	76000

This query returns rows six through ten for each department, ordered by salary in descending order. This result is the second page of results, where each page contains five rows per department. Marketing has only nine employees, so only four rows appear for that department. Sales has only three employees, so OFFSET 5 skips past all of them and returns no Sales rows.

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

<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

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 syntax and example. Syntax

SQL

<select_query1> EXCEPT [ ALL | DISTINCT ] <select_query2>

Parameters

Parameter	Description
`<select_query1>`	A valid SELECT query that is compared to `select_query2`. 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

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

<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

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

<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. 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. 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. 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. 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. 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. 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. 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 execute that query. The user must have access to the service class through a group assignment or a `USE` privilege grant. For details, see CREATE SERVICE CLASS and Service Class Privileges.

Example

SQL

SELECT * FROM sys.dummy10
USING
    SCHEDULING_PRIORITY = 1.0
    MAX_ROWS_RETURNED = 10
    MAX_ELAPSED_TIME = 10
    MAX_TEMP_DISK_USAGE = 10;

Output

SQL

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 queries have the same effect on the workload as a query executed without the clause.

Contact Ocient Support for guidance in using the TRACE SQL statement.

Syntax

SQL

<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. If unspecified, defaults to 500 milliseconds.
`resolution_int`	Determines the level of detail captured for a trace or event. If the resolution of a trace is greater than the resolution of an event, the database records the event. 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

SELECT * FROM sys.dummy10 TRACE;

Output

plan_parent_id	plan_op_id	operator_type	node_id	core_id	op_id	group	sample	value
77041d2b-f7ca-49d5-a3f5-4b75b0782ed1	498690cf-4a9b-4c1f-affa-417f31fddc2d	RENAME_OPERATOR	08ca7b05-1e4d-455f-9f46-2174ed048d33	4	8441	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	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	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

<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

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

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

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
`\o`, `\oo`, `\ooo`	An octal byte value, where `o` is an integer from 0 to 7. If the value exceeds one byte (octal 377), the system uses two bytes.
`\xh`, `\xhh`	A hexadecimal byte value, where `h` is a character from 0–9 or a–f.
`\uxxxx`	A 16-bit hexadecimal Unicode character value, where `x` is a character from 0–9 or a–f.
`\Uxxxxxxxx`	A 32-bit hexadecimal Unicode character value, where `x` is a character from 0–9 or a–f.

The \o, \oo, \ooo and \xh, \xhh escape sequences specify raw UTF-8 bytes, not Unicode code points. To represent multi-byte UTF-8 characters using these sequences, you must provide each byte separately (e.g., e'\xc3\xa9' for é). For Unicode code points, use \uxxxx or \Uxxxxxxxx instead. String literals containing invalid UTF-8 byte sequences result in a syntax error.

The \' escape sequence is not supported in e-strings. Use '' to include a single quote within an e-string.

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

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

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

SELECT e'\\my\\directory\\path';

*Output: *\my\directory\path Escape Sequences with Regular Expressions

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

In this example, the REGEXP_SUBSTR function uses the regular expression \w+ to match any word characters.

SQL

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

SELECT REGEXP_SUBSTR(
    'abcdefghijklmnopqrstuvwxyz',
    e'\w+'
    );

*Output: *w Octal Escape Sequence This example uses an octal escape sequence to produce the letter A (octal 101).

SQL

SELECT e'\101';

*Output: *A Hexadecimal Escape Sequence This example uses hexadecimal escape sequences to produce the character é (UTF-8 bytes \xc3\xa9).

SQL

SELECT e'\xc3\xa9';

*Output: *é Unicode Escape Sequence This example uses a 16-bit Unicode escape sequence to produce the character é (U+00E9).

SQL

SELECT e'\u00e9';

*Output: *é 32-bit Unicode Escape Sequence This example uses a 32-bit Unicode escape sequence to produce an emoji (U+1F600).

SQL

SELECT e'\U0001f600';

*Output: *😀 SQL Reference Data Control Language (DCL) Statement Reference Data Definition Language (DDL) Statement Reference Join Operations SQL Syntax Conventions Identifiers

​Ocient SQL Syntax

​Default Schema

​Querying SQL Statement Reference

​WITH

​WITH RECURSIVE

​Use Recursive CTEs with DML Statements

​Use Recursive CTEs in Views

​SELECT

​<select_list_entry>

​<from_clause>

​Using SELECT *

​Using SELECT * EXCEPT

​Using Lateral Column Aliases

​FROM

​JOIN

​Types of Join Operations ( <join_operation> )

​JOIN Type Descriptions

​INNER JOIN

​LEFT OUTER JOIN

​RIGHT OUTER JOIN

​FULL OUTER JOIN

​CROSS JOIN

​SEMI JOIN

​ANTI JOIN

​WHERE

​<filter_condition>

​EXISTS

​SIMILAR TO Operator

​Array Filters

​GROUP BY

​HAVING

​QUALIFY

​WINDOW

​ORDER BY

​LIMIT

​FETCH

​OFFSET

​INTERSECT

​EXCEPT

​UNION

​USING

​TRACE

​TRACE Results

​TAG

​String Literals and Escape Sequences

​Related Links

Ocient SQL Syntax

Default Schema

Querying SQL Statement Reference

WITH

WITH RECURSIVE

Use Recursive CTEs with DML Statements

Use Recursive CTEs in Views

SELECT

<select_list_entry>

<from_clause>

Using SELECT *

Using SELECT * EXCEPT

Using Lateral Column Aliases

FROM

JOIN

Types of Join Operations ( `<join_operation>` )

JOIN Type Descriptions

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

SEMI JOIN

ANTI JOIN

WHERE

<filter_condition>

EXISTS

SIMILAR TO Operator

Array Filters

GROUP BY

HAVING

QUALIFY

WINDOW

ORDER BY

LIMIT

FETCH

OFFSET

INTERSECT

EXCEPT

UNION

USING

TRACE

TRACE Results

TAG

String Literals and Escape Sequences

Related Links