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 ] 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 commands supported by the ocient jdbc cli program docid\ fwb2ppe2b5li l pisj25 command querying command 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 is helpful for breaking down complex queries into smaller parts syntax with \<cte name> \[ ( \<cte column> \[ , ] ) ] as ( \<sub query> ) \<select query> parameters parameter description \<cte name> a name for the common table expression populated with the result set from the sub query statement \<cte column> optional 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 with avg budget table (average budget) as ( select avg(budget) from movies ) select title, budget, revenue from movies, avg budget table where budget > avg budget table average budget; output title budget revenue cgi why 237000000 2787965087 titania 200000000 1845034188 merchandise vehicle 5 200000000 1066969703 the tentpole 220000000 1519557910 pirates of palm springs 140000000 655011224 spyman 16 200000000 1108561013 frigid 150000000 1274219009 fury 7 190000000 1506249360 superhero 23 250000000 1084939099 triassic world 150000000 1513528810 iron chef 3 200000000 1215439994 the last airman 150000000 318502923 select initiates a query statement or a subquery clause within other statements for information on using select as a subquery for filtering or ordering results, see the general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l and general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l sections for information on using select as a subquery for a common table expression, see the general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l section syntax 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 meets 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 \<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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l documentation examples using select this example uses select to return all the columns in the movies table 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 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 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 parameter description \<select clause> a query statement using the select sql statement for details, see the general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l section \<join clause> a join clause used to retrieve data from two or more tables for your query for details, see the general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l section 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 parameter description \<select query> a valid select query for details, see general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l \<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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l \<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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l 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 \<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 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 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 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 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) 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) 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 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 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 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 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 \<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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l section \<sub query> a subquery with a filter condition that follows the exists clause for details, see the general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l section \<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 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l 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 who are 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 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 ) r epetition of zero or more times + r epetition of one or more times ? r epetition of zero or one times {m} r epetition of exactly m times {m,n} r epetition between m and n times (inclusive) {m,} r epetition 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 \\\ \cx the character with the same five lower order bits as x with all zeros as higher order bits \e the character with the numeric value 27, esc \f form feed \n newline \r carriage return \t horizontal tab \uwxyz wxyz represents four hexadecimal digits, including two characters one with numeric value wx , and one with numeric value yz \uabcdefgh abcdefgh represents eight hexadecimal digits, including four characters numeric value ab , numeric value cd , numeric value ef , and numeric value gh \v vertical tab \xd d is any number of hexadecimal digits if the number of digits is odd, a 0 is added to the left of the sequence numeric characters with values represented by every group of two symbols from d \0 null character 0 \xy xy are octal digits that represent the character with the numeric value xy \xyz xyz are octal digits that r epresent t he character with the numeric value xyz if x is greater than or equal to 4 , the database returns two characters the numeric value 1 and the numeric value of the lower 8 bits of xyz \d matches any digit \s matches any whitespace \w matches any alphanumeric character or \d matches any non digit \s matches any non whitespace \w matches any non alphanumeric that is also not \a the beginning of a line equivalent to ^ \m , \m , or \y the boundary of a word equivalent to \b in normal regular expressions \y any position that is not the boundary of a word \z the end of a line equivalent to $ in normal regular expressions \\ the star character \\+ the plus character \\? the question mark character 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 array functions and operators docid\ ymza gmw5298nhgmbsuyn page filter behavior with null rows filtering with array functions can have different outcomes depending on whether the array row is null or if the values contained in the array are null if for some() or for all() evaluate 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 && ignore null values for details, see the array functions and operators docid\ ymza gmw5298nhgmbsuyn 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 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 docid\ a794p1hruev25euof9svc 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l section 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 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l syntax 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l 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 year(release date) sum(budget) 1997 200000000 2003 264000000 2009 237000000 2010 350000000 2012 670000000 2013 350000000 2015 414000000 order by sorts the result set in ascending or descending order based on one or more specified columns if you specify multiple columns, they are sorted hierarchically from left to right syntax 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 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l clause syntax \<select query> limit limit number parameters parameter description \<select query> a valid select query limit number the number of rows to return from the query, specified as a positive integer or any constant scalar expression that evaluates to a positive integer examples limit returned rows using a number this example uses limit to restrict the number of returned movie titles to only three 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 generate tables using sys dummy docid\ kvuew6zelggm2us tcb s 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l clause syntax \<select query> offset offset number parameters parameter description \<select query> a valid select query offset number the number of rows to skip when returning results from the query, specified as a positive integer or any constant scalar expression that evaluates to a positive integer examples skip rows from the result set using a number in this example, the order by statement orders the results chronologically as a result, the offset clause removes the oldest six movies from the result set select release date, title from movies order by release date offset 6; output release date title 2003 07 09 pirates of palm springs 2003 10 10 billy the killy 2003 12 01 swords & scabbards 2009 12 10 cgi why 2010 06 16 merchandise vehicle 5 2010 06 30 the last airman 2012 04 25 the tentpole 2012 07 16 superhero 23 2012 10 25 spyman 16 2013 04 18 iron chef 3 2013 11 27 frigid 2015 04 01 fury 7 2015 06 09 triassic world 2015 06 17 minnows skip rows from the result set using an expression the offset sql statement can also accept expressions this query uses the expression 3+4 with the sys dummy table to create a column of incrementing integers by skipping the first seven out of 10 rows for details, see generate tables using sys dummy docid\ kvuew6zelggm2us tcb s 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 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 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l syntax and example syntax \<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 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 \<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 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 \<select query> using \[ scheduling priority = priority value ] \[ max rows returned = max rows ] \[ max elapsed time = max time ] \[ max temp disk usage = max disk space ] \[ 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 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 disk space 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 r esults in memory on the sql nodes these results are not cached if there is insufficient memory available service class an optional clause at the end of a query that sets a specific service class to run that query for details, see users, groups, and service classes docid\ iwkirup8638xpra30vnl3 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 describe 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 general sql syntax docid\ cb6h0pxd4vqlizfr6jr0l 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 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 given 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 select from sys dummy10 trace; output plan parent id plan op id operator type node id core id op id time group sample value 77041d2b f7ca 49d5 a3f5 4b75b0782ed1 498690cf 4a9b 4c1f affa 417f31fddc2d rename operator 08ca7b05 1e4d 455f 9f46 2174ed048d33 4 8441 0 xg db vm operators operatortraceevents rows in 1 77041d2b f7ca 49d5 a3f5 4b75b0782ed1 498690cf 4a9b 4c1f affa 417f31fddc2d rename operator 08ca7b05 1e4d 455f 9f46 2174ed048d33 4 8441 0 xg db vm operators operatortraceevents rows out 1 77041d2b f7ca 49d5 a3f5 4b75b0782ed1 498690cf 4a9b 4c1f affa 417f31fddc2d rename operator 08ca7b05 1e4d 455f 9f46 2174ed048d33 4 8441 0 xg db vm operators operatortraceevents bloom filtered rows 0 string literals and escape sequences all string literals in sql statements must be enclosed in single quotes to use a single quote within a string, you can use another single quote as an escape, '' for other escape sequences, include an e character before the string literal, i e , before the opening single quote this directs the system to recognize escape sequences in the string literal this means all single \ characters in the string now escape themselves any subsequent character after the \ is also escaped if it matches an escape sequence (see the table) if you use escape sequences, be prepared that you might need to alter strings that include \ , such as directory paths supported escape sequences escape sequence description '' single quotation mark \\" double quotation mark \n newline character \r carriage return character \f form feed character (i e page break) \b backspace \\\ backslash \t tab examples these examples show how the system interprets strings with and without escape sequences string without an escape sequence this example selects the simple string literal '\my\directory\path' that does not use escape sequences 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 the escape sequences of regular expressions in this example, the character and binary functions docid\ t4v9mfehdtbx8w 6ipulp 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 sql reference docid\ ulddmgdefqwa9p 2dqlz data control language (dcl) statement reference docid 7jq6fizrghsmuumsa4qiq data definition language (ddl) statement reference docid\ wcqh0qqpm0gvf2x0gyzrq