Basics Table Query
Select all columns from the table identified by 'syn123'.
select * from syn123
Select only two columns identified by 'foo' and 'bar' from the table identified by 'syn'123'.
select foo, bar from syn123
If a column name contains spaces, punctuation, or SQL key words, then the name must be enclosed in double quotes.
select "has space" from syn123
Any double quote within a column name must be escaped with two double quotes.
select "The ""Cool"" name" from syn123
Aggregation Functions
Count the number of rows in table 'syn123'.
select count(*) from syn123
Get the maximum of all values for the column 'foo' in table 'syn123'.
select max( foo ) from syn123
Get the minimum of all values for the column 'foo' in table 'syn123'.
select min(foo) from syn123
Get the average of all values for the column 'foo' in table 'syn123'.
select avg(foo) from syn123
Get the sum of all values for the column 'foo' in table 'syn123'.
select sum(foo) from syn123
Returns the bit-wise AND of all bits in expr.
select bit_and(expr) from syn123
Returns the bit-wise OR of all bits in expr.
select bit_or(expr) from syn123
Returns the bit-wise XOR of all bits in expr.
select bit_xor(expr) from syn123
Returns the population standard deviation of expr (the square root of VAR_POP()). You can also use STD() or STDDEV(), which are equivalent.
select STDDEV_POP(expr) from syn123
Returns the sample standard deviation of expr (the square root of VAR_SAMP().
select STDDEV_SAMP(expr) from syn123
Returns the population standard variance of expr. It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator. You can also use VARIANCE(), which is equivalent.
select VAR_POP(expr) from syn123
Returns the sample variance of expr. That is, the denominator is the number of rows minus one.
select VAR_POP(expr) from syn123
For each group of the column 'foo', concatenate the distinct values from column 'bar' order by 'bar' descending with a separator character of '#'. The distinct keyword is optional. The 'order by' clause is optional. The separator is optional, with a default separator of ','.
select foo, group_concat(distinct bar order by bar desc separator '#') from syn123 group by foo
Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined. The function acts on a column or an expression that evaluates to a single value.
select foo, JSON_ARRAYAGG(bar) from syn123 group by foo
Set Selection
The DISTINCT keyword can be used to select all distinct value (the value set) from a column.
select distinct foo from syn123
The DISTINCT keyword applies to all selected columns, so if more than one column is listed, then the results will be a list of the distinct combinations of all selected columns.
select distinct foo, bar from syn123
The DISTINCT keyword can be used with set functions. In this example, we will get the count of the distinct values from the foo column.
select count(distinct foo) from syn123
Arithmetic
Perform basic arithmetic. Supported operators: + | - | / | *
select (1 + 2)/3 from syn123
Perform integer division.
select 5 DIV 2 from syn123
Modulo operation. Returns the remainder of integer division.
select 5 % 2 from syn123
Filtering
Select all rows where column foo has a value equal to one.
select * from syn123 where foo =1
Select all rows where column foo has a string value equal to 'a string', the right-hand-side but be within single quotes (').
select * from syn123 where foo = 'a string'
Select all rows where column foo has a value greater than one.
select * from syn123 where foo > 1
Select all rows where column foo have a value greater than -1.98e12
select * from syn123 where foo > 1.98e12
Select all rows where column foo has a value less than one.
select * from syn123 where foo < 1
Select all rows where column foo has a value that does not equal one.
select * from syn123 where foo <> 1
Select all rows where column foo has a value greater than or equal to one
select * from syn123 where foo >= 1
Select all rows where column foo has a value less than or equal to one.
select * from syn123 where foo <= 1
Select all rows where column foo has a value equal to one, two, or three.
select * from syn123 where foo in (1,2,3)
Select all rows where column foo has a value between one and two.
select * from syn123 where foo between 1 and 2
Select all rows where column foo has a null value.
select * from syn123 where foo is null
Select all rows where column foo has a value that is not null.
select * from syn123 where foo is not null
Select all rows where column foo has a value with a prefix of 'bar'. In this example the right-hand-side of the LIKE keyword is a regular expression where the '%' represents one or more characters or even zero characters.
select * from syn123 where foo like 'bar%'
Select all rows where column foo has a value with a prefix of 'bar'. In this example the right-hand-side of the LIKE keyword is a regular expression where the '_' represents one and only one character.
select * from syn123 where foo like 'bar_'
Select all rows where double column foo is not a number or plus or minus infinity
select * from syn123 where isNan(foo) or isInfinity(foo)
The default escape character for LIKE regular expression is the '' character. In this example we want to find all rows such that foo that contain 'bar_' so we will need to escape the '_' character.
select * from syn123 where foo like 'bar_'
To use a different escape character for LIKE regular expression we must define the escape character. In this example, the '|' will be used as an escape character instead of ''.
select * from syn123 where foo like 'bar|_' escape '|'
Select all rows where column foo has a value equal to one or column bar has a value equals to two.
select * from syn123 where foo = 1 or bar = 2
Select all rows where column foo has a value equal to one and column bar has a value equal to two.
select * from syn123 where foo=1 and bar =2
Predicates can be surrounded by the '(' and ')' to enforce precedence and nesting.
select * from syn123 where (foo=1 and bar =2) or foobar = 3
Perform a basic full text search on a table or (materialized) view. Full text search must be enabled first for the table or view (it is not available for virtual tables).
select * from syn123 where text_matches('some text')
Date and Time Functions
Filtering on columns of type DATE using a standard string format: 'YYYY-MM-DD HH:MM:SS'. This example selects all rows where column aDate has a value greater than '2017-05-19 22:31:01' (GMT).
select * from syn123 where aDate > UNIX_TIMESTAMP('2017-05-19 22:31:01')*1000
Filtering on columns of type DATE using an interval. This example will select all rows have been modified in last 30 days. Interval units can be YEAR | QUARTER | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND | MICROSECOND.
select * from syn123 where modified_on > unix_timestamp(NOW() - INTERVAL 30 DAY)*1000
For columns of type DATE, select the date value as a standard date string in the format: 'YYYY-MM-DD HH:MM:SS'.
select FROM_UNIXTIME(aDate/1000) from syn123
Function to extract a numeric value from a date-time string. Supported functions include: MICROSECOND | SECOND | MINUTE | HOUR | DAY | WEEK | QUARTER | YEAR | YEARWEEK | DAYOFMONTH | DAYOFWEEK | DAYOFYEAR | WEEKOFYEAR
select microsecond('12:00:00.123456') from syn123
Function to extract a string value from a date-time string. Supported functions include: DATE | DAYNAME | MONTHNAME
select dayname('2007-02-03') from syn123
String Functions
Concatenate the values of columns foo and bar into a single string.
select concat(foo, bar) from syn123
Replace all space characters with underscores for all values from the foo column.
select replace(foo, ' ', '_') from syn123
Convert all string values from the column foo to upper case.
select upper(foo) from syn123
Convert all string values from the column foo to lower case.
select lower(foo) from syn123
Trim leading and trailing white space characters from all string values of the column foo.
select trim(foo) from syn123
Numeric Functions
Rounds all numeric values in the column foo to 2 decimal places.
select round(foo,2) from syn123
Returns the cyclic redundancy check value of all values in column foo.
select crc32(foo) from syn123
JSON Functions
Evaluates a list of key-value pairs and returns a JSON object containing those pairs.
select JSON_OBJECT(foo, sum(bar)) from syn123 group by foo
Evaluates a list of values and returns a JSON array containing those values.
select JSON_ARRAY('a', 'b', 'c') from syn123
Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the first input is not a valid JSON document or any path argument is not a valid path expression.
select JSON_EXTRACT(foo, '$[1]') from syn123
Compares two JSON documents. Returns true (1) if the two document have any key-value pairs or array elements in common.
select JSON_OVERLAPS(foo, '[1,2]') from syn123
Returns the path to the given search string within a JSON document. The second argument can be 'one' (terminates after the first match) or 'all' (all matching paths). The third argument is the search string expression (Similar to a LIKE predicate where the '%' and '_' special characters has special meaning).
select JSON_SEARCH(foo, 'one', 'search_string%') from syn123
Quotes a string as a JSON value by wrapping it with double quote characters and escaping interior quote and other characters, then returning the result as a string. Returns NULL if the argument is NULL.
select JSON_QUOTE(foo), JSON_QUOTE('bar') from syn123
Unquotes JSON value and returns the result as a string. Returns NULL if the argument is NULL. An error occurs if the value starts and ends with double quotes but is not a valid JSON string literal.
select JSON_UNQUOTE(foo), JSON_UNQUOTE('"bar"') from syn123
Grouping
Select all rows grouping first by foo, then by bar.
select * from syn123 group by foo, bar
Grouping can be used in conjunction with aggregation function. In this example, values from the foo column are first grouped, then the average of each group is calculated. For this example, one row will be returned for each group.
select foo, avg(foo) from syn123 group by foo
Sorting
Select all columns from the table with the returned row order sorted by the values of the foo column in ascending order.
select * from syn123 order by foo asc
Select all columns from the table with the returned row order sorted by the values of the foo column in descending order.
select * from syn123 order by foo desc
Multiple columns can be include in the order by clause. In this example, the returned row order will be sorted first by foo in ascending order followed by bar in descending order.
select * from syn123 order by foo asc, bar desc
Pagination
Pagination is used to limit the number of results returned in a single request. In this example, we want the first ten rows that match our query.
select * from syn123 limit 10 offset 0
If the above query fetches the first page of ten results with indices from zero to nine, the second page of ten rows can be fetched using this query.
select * from syn123 limit 10 offset 10
The OFFSET element is optional. In this example, LIMIT is used to limit the results to the first 5 rows.
select * from syn123 limit 5
Pagination parameters should always be at the end of the query
select * from syn123 where foo =1 group by bar limit 100 offset 0
Reserved Columns
Every table has at least two reserved columns ROW_ID and ROW_VERSION. The values for these column are automatically managed and can not be directly modified. However, these columns can be used in queries like any other column. In this example, we are selecting all columns for a single row using its ROW_ID.
select * from syn123 where ROW_ID = 101
In this example we are listing all rows that have a current version number greater than 12. Note: while each row can have multiple versions, only the current version of each row will appear in the index used to support table queries. That means it is not possible to list old version of a row or select a row using an old version number.
select * from syn123 where ROW_VERSION > 12
Operations on LIST columns
To filter on a LIST column, use the HAS() clause with parentheses. HAS() holds a comma separated list of values. The values in the comma separated list have an OR relationship. This means that a row matches if it contains any of the values.
select * from syn123 where fooList has ('either this value', 'or this value')
To filter for lists that contain multiple values, you will need to chain together multiple HAS() clauses with AND. The ordering of the clauses does not change the outcome.
select * from syn123 where fooList has ('first required value') and fooList has ('second required value') and fooList has ('third required value')
To filter on a pattern (See the LIKE filter) you can use the HAS_LIKE() clause, similarly to the HAS() clause each value in the list have an OR relationship.
select * from syn123 where fooList has_like ('either this pattern%', 'or this other pattern%')
Similarly to the LIKE filter it is possible to use an alternative escape character
select * from syn123 where fooList has_like ('bar|_') escape '|'
To operate on individual values stored inside a LIST column, use the UNNEST() function. NOTE: This will duplicate rows for all other columns.
select unnest(fooList), count(*) from syn123 group by unnest(fooList)
Other Synapse Functions
Returns the Synapse userId of the current user. NOTE: Can be used in the Select clause and returns USERID columnTyp
select * from syn123 where userId = CURRENT_USER()
Select Functions
Returns NULL if foo = bar is true, otherwise returns foo.
select NULLIF(foo, bar) from syn123
COALESCE(value,...) Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
select COALESCE(foo,bar) from syn123
When the value for column foo equals 'a' then 'foo is a' will be returned. When the value for foo equals 'b' then 'foo is b' will be returned. If the value does not match any 'when' condition, then 'foo is something else' will be returned. The 'else' clause is optional.
select case foo when 'a' then 'foo is a' when 'b' then 'foo is b' else 'foo is something else' end from syn123
When the value from column foo is greater than the value of column bar, then 'foo is greater than bar' would be returned. When bar is greater than foo, then 'bar is greater than foo' will be returned. Any other condition will return 'default'. The 'else' clause is optional.
select case when foo > bar then 'foo is greater than bar' when bar > foo then 'bar is greater than foo' else 'default' end from syn123