General SQL Syntax

the {{ocienthyperscaledatawarehouse}} 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 \[ 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 docid\ zyojxip3zkuvr9skpiyxo 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 with \<cte name> \[ ( \<cte column> \[ , ] ) ] as ( \<sub query> ) \<select query> parameters true 152,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 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 true 220,220,222 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 docid\ nw9vavkey2v75moxm muo and docid\ nw9vavkey2v75moxm muo sections for information on using select as a subquery for a common table expression, see the docid\ nw9vavkey2v75moxm muo section syntax select \[ all | distinct ] \[ \[ except ( column name \[ , ] ) ] \| \<select list entry> \[ , ] ] \[ \<from clause> ] parameters true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type \<select list entry> the select list entry defines a column or expression to include in your query result set syntax \<select list entry> = column name | expression \[ as new name ] parameters true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type < from clause > for information on the \<from clause> , see the docid\ nw9vavkey2v75moxm muo documentation examples using select this example uses select to return all the columns in the movies table select from movies limit 5; output true 100,100,100,100,100,100,100,100,100,100left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type using select except this example uses select except to exclude certain columns from the result set select except (movie id, runtime, vote average, vote count) from movies limit 5; output true 165,165,165,167left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 create table products ( product id int, product name varchar(100), price decimal(10, 2) ); insert four records into the products table 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 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 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 \<select clause> from { table name | ( \<sub query> ) } \[ , ] \[ \<join clause> \[ , ] ] parameters true 175,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type join combines rows from multiple tables so they can be accessed by a query syntax \<select query> from \<table reference1> \<join operation> \<table reference2> on table1 column \<boolean operator> table2 column parameter true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type types of join operations ( \<join operation> ) ocient supports the following types of join operations syntax \<join operation> = { inner join \| left \[ outer ] join \| right \[ outer ] join \| full \[ outer ] join \| cross join \| semi join \| anti join } join type descriptions 181,100 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 select from &#x9;( &#x9;select &#x9; 1 as cx1, &#x9; 2 as cx2) as x inner join lateral ( &#x9;select &#x9; x cx1 as cy1, &#x9; x cx2 as cy2) as y on &#x9;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 true 331,331left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type genre — a table of video game genres, which are identified by ids true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 select game name, genre name from video games game inner join video games genre on game genre id = genre id; output true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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) select game name, genre name from video games game left outer join video games genre on game genre id = genre id; output true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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) select game name, genre name from video games game right outer join video games genre on game genre id = genre id; output true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type full outer join this example uses a full outer join operation to capture all rows from both tables, even if they do not match select game name, genre name from video games game full outer join video games genre on game genre id = genre id; output true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 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 true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 select genre name, id from video games genre semi join video games game on genre id = game genre id; output true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 select game name from video games game anti join video games genre on game genre id = genre id; output true 662 unhandled content type unhandled content type unhandled content type where filters rows based on a specified condition syntax \<select query> where { column name \<filter condition> filter value | \[ not ] exists ( \<sub query> ) } parameters true 145,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type \<filter condition> a logical combination of predicates used to evaluate the referenced column name based on the filter value syntax \<filter condition> = { = \| == \| <> \| != \| \[ not ] equals \| < \| <= \| > \| >= \| \[ not ] in \| \[ not ] like \| \[ not ] similar to \| between \| for some \| for all \| is \[ not ] null \| is \[ not ] distinct from } definitions true 100,338,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 create table departments ( id int, name varchar(50) ); create the employees table for employee data 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 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 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 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 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 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 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 select from departments d where d id != 3 and exists ( select from employees e where e department id < 4 ); output 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 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 where string1 similar to string2 this table describes the metacharacters supported by the similar to keyword 134,497 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type similar to also supports escaping these metacharacters with \ and certain escape sequences supported by c family languages 135,425 trueleft unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type the database ignores any \ metacharacter that does not precede a metacharacter (including \ ) or an escape sequence in the metacharacter table invalid expressions (unpreceded quantifiers, unclosed parentheses, etc ) result in query errors the database does not recognize the character as a metacharacter, unlike the behavior in traditional regular expressions the database applies escape metacharacters that apply to string literals to match a string containing a single '\\' , you might need to use '\\\\\\\\' examples 'abc' similar to 'abc' true 'abc' similar to 'a' false 'abc' similar to '%(b|d)%' true 'abc' similar to '(b|c)%' false ' abc ' similar to '%\mabc\m%' true 'xabcy' similar to '%\mabc\m%' false 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% array length(array col) > 0 and for all(array col) like '%ocient% for details about array functions, see the docid\ xuk0z8dmxpgmogszpdw6w 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 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 docid\ xuk0z8dmxpgmogszpdw6w page group by groups rows with the same values into summary rows, based on a specified aggregate function syntax select \<select list clause> from table name group by { column name | expression | integer } \[ , ] \[ having \<filter condition> ] parameters true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example this example uses a movie database to calculate the total amount spent on movie production per year select year(release date), sum(budget) from movies group by 1 order by 1 asc; output true 331,331left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ nw9vavkey2v75moxm muo syntax group by column name having \<filter condition> parameters true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 select year(release date), sum(budget) from movies group by 1 having sum(budget) > 100000000 order by 1 asc; output true 331,331left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 order by { column position | column name } \[ asc | desc ] \[ nulls first | nulls last ] \[ , ] parameters true 220,220,222 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example in this example, the order by statement orders the movies from newest to oldest select release date, title from movies order by release date desc; output true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type limit limits the number of returned rows from a query to a specified amount the returned rows are nondeterministic unless you use an docid\ nw9vavkey2v75moxm muo clause syntax \<select query> limit limit number parameters true 166,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type examples limit returned rows using a number this example uses limit to restrict the number of returned movie titles to only three select title from movies limit 3; output 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 docid\ etf0ovy63hqzxjjt yvop select c1 from sys dummy10 limit 1+2; output 1 2 3 offset skips a specified number of rows from the result set the returned rows are nondeterministic unless you use an docid\ nw9vavkey2v75moxm muo clause syntax \<select query> offset offset number parameters true 160,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 select release date, title from movies order by release date offset 6; output true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 docid\ etf0ovy63hqzxjjt yvop select c1 from sys dummy10 offset 3+4; output 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 \<select query1> intersect \[ all | distinct ] \<select query2> parameters true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 select from movies where revenue > 1000000000 intersect select from movies where budget > 20000000; output 100,251,100,100,100,100,100,100,100,100 true left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 docid\ nw9vavkey2v75moxm muo syntax and example syntax \<select query1> except \[ all | distinct ] \<select query2> parameters true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 select from movies except select from movies where budget > 20000000; output true 100,100,100,100,100,100,100,100,100left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 \<select query1> union \[ all | distinct ] \<select query2> \[ ] parameters true 148,100,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example this example uses union to merge two separate queries for identical columns into the same result set 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 true 220,220,222left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type left unhandled content type 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 \<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 331,331 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example select from sys dummy10 using scheduling priority = 1 0 max rows returned = 10 max elapsed time = 10 max temp disk usage = 10; output 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 docid\ nw9vavkey2v75moxm muo trace queries have the same effect to the workload as a query run without the clause contact ocient support for guidance in using the trace sql statement syntax \<select query> trace \[ frequency frequency int ] \[ resolution resolution int ] parameters true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 331,331 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type sample and value columns the sample and value columns describe what occurred in a specified trace period 331,331 8 true unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example select from sys dummy10 trace; output true 100,100,100,100,100,100,100,100,100,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 \<select query> \<tag> \[ ] \<tag> = tag \<tag identifier> parameters true 331,331 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type examples sql query with one tag select the count of a table with one row and tag the query with the name count1 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 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 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 true 165,100 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 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 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 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 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 docid\ ja8cont33tonx ktruedj function uses the regular expression \w+ to match any word characters 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 select regexp substr( 'abcdefghijklmnopqrstuvwxyz', e'\w+' ); output w related links docid\ zvfytm4ip4rwxsa17tvk9 docid\ f55ngxtki0f7kkmyatvug docid\ jf l ie2lupbjgrjw4lqx