A SELECT statement can consist of the following basic clauses.
SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
The following syntax diagram outlines the syntax supported by the SQL engine of the provider:
Return all columns:
Rename a column:
Cast a column's data as a different data type:
Search data:
Return the number of items matching the query criteria:
Return the number of unique items matching the query criteria:
Return the unique items matching the query criteria:
Summarize data:
See Aggregate Functions below for details.
Retrieve data from multiple tables.
See JOIN Queries below for details.
Sort a result set in ascending order:
Restrict a result set to the specified number of rows:
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
Returns the number of rows matching the query criteria.
Returns the number of distinct, non-null field values matching the query criteria.
Returns the average of the column values.
Returns the minimum column value.
Returns the maximum column value.
Returns the total sum of the column values.
The Provider for Azure Cosmos DB supports joins of a nested array with its parent document and joins of multiple collections.
The provider expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested Azure Cosmos DB documents. This type of query is supported through the Azure Cosmos DB API.
For example, consider the following query from Azure Cosmos DB's restaurants collection:
You can join multiple collections just like you would join tables in a relational database. Set SupportEnhancedSQL to True to execute these types of joins. The following examples use the restaurants and zips collections available in the Azure Cosmos DB documentation.
The query below returns the restaurant records that exist, if any, for each ZIP code:
The query below returns records from both tables that match the join condition:
The following date literal functions can be used to filter date fields using relative intervals. Note that while the <, >, and = operators are supported for these functions, <= and >= are not.
The current day.
The previous day.
The following day.
Every day in the preceding week.
Every day in the current week.
Every day in the following week.
Also available:
L_LAST/L_THIS/L_NEXT MONTH
L_LAST/L_THIS/L_NEXT QUARTER
L_LAST/L_THIS/L_NEXT YEAR
The previous n days, excluding the current day.
The following n days, including the current day.
Also available:
L_LAST/L_NEXT_90_DAYS
Every day in every week, starting n weeks before current week, and ending in the previous week.
Every day in every week, starting the following week, and ending n weeks in the future.
Also available:
L_LAST/L_NEXT_N_MONTHS(n)
L_LAST/L_NEXT_N_QUARTERS(n)
L_LAST/L_NEXT_N_YEARS(n)
Returns the absolute (positive) value of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent.
numeric_expr: A numeric expression.
Returns the smallest integer value greater than, or equal to, the specified numeric expression.
numeric_expr: A numeric expression.
Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression.
numeric_expr: A numeric expression.
Returns the corresponding angle in degrees for an angle specified in radians.
numeric_expr: A numeric expression.
Returns the largest integer less than or equal to the specified numeric expression.
numeric_expr: A numeric expression.
Returns the exponential value of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the base-10 logarithm of the specified numeric expression.
numeric_expr: A numeric expression.
Returns radians when a numeric expression, in degrees, is entered.
numeric_expr: A numeric expression.
Returns a randomly generated numeric value from [0,1).
Returns a numeric value, rounded to the closest integer value.
numeric_expr: A numeric expression.
Returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the trigonometric sine of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns the square root of the specified numeric value.
numeric_expr: A numeric expression.
Returns the square of the specified numeric value.
numeric_expr: A numeric expression.
Returns the tangent of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns a numeric value, truncated to the closest integer value.
numeric_expr: A numeric expression.
Returns the principal value of the arc tangent of y/x, expressed in radians.
y_expr: The y numeric expression.
x_expr: The x numeric expression.
Returns the natural logarithm of the specified numeric expression.
numeric_expr: A numeric expression.
base: Optional numeric argument that sets the base for the logarithm.
Returns the constant value of PI.
Returns the value of the specified expression to the specified power.
numeric_expr: A numeric expression.
power_expr: Is the power to which to raise numeric_expr.
Returns a Boolean value indicating if the type of the specified expression is an array.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a Boolean.
expr: Any valid expression.
Returns a Boolean indicating if the property has been assigned a value.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is null.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a number.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a JSON object.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a primitive (string, Boolean, numeric, or null).
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a string.
expr: Any valid expression.
Returns a string that is the result of concatenating two or more string values.
str1: The first string to concatenate.
str2: The second string to concatenate.
str3: The third string to concatenate.
Returns a Boolean indicating whether the first string expression contains the second.
str1: The string to search in.
str2: The string to search for.
Returns a Boolean indicating whether the first string expression ends with the second.
str1: The string to search in.
str2: The string to search for.
Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found.
str1: The string to search in.
str2: The string to search for.
Returns the left part of a string with the specified number of characters.
str: A valid string expression.
num_expr: The number of characters to return.
Returns the number of characters of the specified string expression.
str: Any valid string expression.
Returns a string expression after converting uppercase character data to lowercase.
str: Any valid string expression.
Returns a string expression after it removes leading blanks.
str: Any valid string expression.
Replaces all occurrences of a specified string value with another string value.
original_value: The string to search in.
from_value: The string to search for.
to_value: The string to replace instances of from_value.
Repeats a string value a specified number of times.
str: The string expression to repeat.
repeat_num: The number of times to repeat the str expression.
Returns the reverse order of a string value.
str: Any valid string expression.
Returns the right part of a string with the specified number of characters.
str: Any valid string expression.
num_expr: The starting index.
Returns a string expression after it removes trailing blanks.
str: Any valid string expression.
Returns a Boolean indicating whether the first string expression starts with the second.
str1: The string to search in.
str2: The string to search for.
Returns part of a string expression starting at the specified character zero-based position and continues to the specified length, or to the end of the string.
str: Any valid string expression.
start_index: The starting index.
length: The length of the string to return.
Returns a string representation of scalar expression.
expr: Any valid expression.
Returns a string expression after it removes leading and trailing blanks.
str: Any valid string expression.
Returns a string expression after converting lowercase character data to uppercase.
str: Any valid string expression.
Returns an array that is the result of concatenating two or more array values.
array_exp1: Any valid array expression.
array_exp2: Any valid array expression.
array_exp3: Any valid array expression.
Returns a Boolean indicating whether the array contains the specified value. You can check for a partial or full match of an object by using a boolean expression within the command.
array_exp1: Any array expression.
expr: The expression to search for.
bool_expr: If it's set to 'true'and if the specified search value is an object, the command checks for a partial match (the search object is a subset of one of the objects). If it's set to 'false', the command checks for a full match of all objects within the array. The default value if not specified is false.
Returns the number of elements of the specified array expression.
array_exp: Any valid array expression.
Returns part of an array expression.
array_exp: Any valid array expression.
start_index: Zero-based numeric index at which to begin the array. Negative values may be used to specify the starting index relative to the last element of the array i.e. -1 references the last element in the array.
max_size: Maximum number of elements in the resulting array.
Returns the distance between the two GeoJSON Point, Polygon, or LineString expressions.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument is within the GeoJSON (Point, Polygon, or LineString) in the second argument.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument intersects the GeoJSON (Point, Polygon, or LineString) in the second argument.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean value indicating whether the specified GeoJSON Point, Polygon, or LineString expression is valid.
spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid, and if invalid, additionally the reason as a string value.
spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns the absolute (positive) value of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose cosine is the specified numeric expression; also called arccosine.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose sine is the specified numeric expression. This is also called arcsine.
numeric_expr: A numeric expression.
Returns the angle, in radians, whose tangent is the specified numeric expression. This is also called arctangent.
numeric_expr: A numeric expression.
Returns the smallest integer value greater than, or equal to, the specified numeric expression.
numeric_expr: A numeric expression.
Returns the trigonometric cosine of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns the trigonometric cotangent of the specified angle, in radians, in the specified numeric expression.
numeric_expr: A numeric expression.
Returns the corresponding angle in degrees for an angle specified in radians.
numeric_expr: A numeric expression.
Returns the largest integer less than or equal to the specified numeric expression.
numeric_expr: A numeric expression.
Returns the exponential value of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the base-10 logarithm of the specified numeric expression.
numeric_expr: A numeric expression.
Returns radians when a numeric expression, in degrees, is entered.
numeric_expr: A numeric expression.
Returns a randomly generated numeric value from [0,1).
Returns a numeric value, rounded to the closest integer value.
numeric_expr: A numeric expression.
Returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression.
numeric_expr: A numeric expression.
Returns the trigonometric sine of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns the square root of the specified numeric value.
numeric_expr: A numeric expression.
Returns the square of the specified numeric value.
numeric_expr: A numeric expression.
Returns the tangent of the specified angle, in radians, in the specified expression.
numeric_expr: A numeric expression.
Returns a numeric value, truncated to the closest integer value.
numeric_expr: A numeric expression.
Returns the principal value of the arc tangent of y/x, expressed in radians.
y_expr: The y numeric expression.
x_expr: The x numeric expression.
Returns the natural logarithm of the specified numeric expression.
numeric_expr: A numeric expression.
base: Optional numeric argument that sets the base for the logarithm.
Returns the constant value of PI.
Returns the value of the specified expression to the specified power.
numeric_expr: A numeric expression.
power_expr: Is the power to which to raise numeric_expr.
Returns a Boolean value indicating if the type of the specified expression is an array.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a Boolean.
expr: Any valid expression.
Returns a Boolean indicating if the property has been assigned a value.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is null.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a number.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a JSON object.
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a primitive (string, Boolean, numeric, or null).
expr: Any valid expression.
Returns a Boolean value indicating if the type of the specified expression is a string.
expr: Any valid expression.
Returns a string that is the result of concatenating two or more string values.
str1: The first string to concatenate.
str2: The second string to concatenate.
str3: The third string to concatenate.
Returns a Boolean indicating whether the first string expression contains the second.
str1: The string to search in.
str2: The string to search for.
Returns a Boolean indicating whether the first string expression ends with the second.
str1: The string to search in.
str2: The string to search for.
Returns the starting position of the first occurrence of the second string expression within the first specified string expression, or -1 if the string is not found.
str1: The string to search in.
str2: The string to search for.
Returns the left part of a string with the specified number of characters.
str: A valid string expression.
num_expr: The number of characters to return.
Returns the number of characters of the specified string expression.
str: Any valid string expression.
Returns a string expression after converting uppercase character data to lowercase.
str: Any valid string expression.
Returns a string expression after it removes leading blanks.
str: Any valid string expression.
Replaces all occurrences of a specified string value with another string value.
original_value: The string to search in.
from_value: The string to search for.
to_value: The string to replace instances of from_value.
Repeats a string value a specified number of times.
str: The string expression to repeat.
repeat_num: The number of times to repeat the str expression.
Returns the reverse order of a string value.
str: Any valid string expression.
Returns the right part of a string with the specified number of characters.
str: Any valid string expression.
num_expr: The starting index.
Returns a string expression after it removes trailing blanks.
str: Any valid string expression.
Returns a Boolean indicating whether the first string expression starts with the second.
str1: The string to search in.
str2: The string to search for.
Returns part of a string expression starting at the specified character zero-based position and continues to the specified length, or to the end of the string.
str: Any valid string expression.
start_index: The starting index.
length: The length of the string to return.
Returns a string representation of scalar expression.
expr: Any valid expression.
Returns a string expression after it removes leading and trailing blanks.
str: Any valid string expression.
Returns a string expression after converting lowercase character data to uppercase.
str: Any valid string expression.
Returns an array that is the result of concatenating two or more array values.
array_exp1: Any valid array expression.
array_exp2: Any valid array expression.
array_exp3: Any valid array expression.
Returns a Boolean indicating whether the array contains the specified value. You can check for a partial or full match of an object by using a boolean expression within the command.
array_exp1: Any array expression.
expr: The expression to search for.
bool_expr: If it's set to 'true'and if the specified search value is an object, the command checks for a partial match (the search object is a subset of one of the objects). If it's set to 'false', the command checks for a full match of all objects within the array. The default value if not specified is false.
Returns the number of elements of the specified array expression.
array_exp: Any valid array expression.
Returns part of an array expression.
array_exp: Any valid array expression.
start_index: Zero-based numeric index at which to begin the array. Negative values may be used to specify the starting index relative to the last element of the array i.e. -1 references the last element in the array.
max_size: Maximum number of elements in the resulting array.
Returns the distance between the two GeoJSON Point, Polygon, or LineString expressions.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument is within the GeoJSON (Point, Polygon, or LineString) in the second argument.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean expression indicating whether the GeoJSON object (Point, Polygon, or LineString) specified in the first argument intersects the GeoJSON (Point, Polygon, or LineString) in the second argument.
spatial_expr1: Is any valid GeoJSON Point, Polygon, or LineString object expression.
spatial_expr2: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a Boolean value indicating whether the specified GeoJSON Point, Polygon, or LineString expression is valid.
spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.
Returns a JSON value containing a Boolean value if the specified GeoJSON Point, Polygon, or LineString expression is valid, and if invalid, additionally the reason as a string value.
spatial_expr: Is any valid GeoJSON Point, Polygon, or LineString object expression.
SELECT {
[ TOP <numeric_literal> | DISTINCT ]
{
*
| {
<expression> [ [ AS ] <column_reference> ]
| { <table_name> | <correlation_name> } .*
} [ , ... ]
}
[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]
{
FROM <table_reference> [ [ AS ] <identifier> ]
} [ , ... ]
[ [
INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }
] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]
] [ ... ]
[ WHERE <search_condition> ]
[ GROUP BY <column_reference> [ , ... ]
[ HAVING <search_condition> ]
[ UNION [ ALL ] <select_statement> ]
[
ORDER BY
<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
]
[
LIMIT <expression>
[
{ OFFSET | , }
<expression>
]
]
} | SCOPE_IDENTITY()
<expression> ::=
| <column_reference>
| @ <parameter>
| ?
| COUNT( * | { [ DISTINCT ] <expression> } )
| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )
| NULLIF ( <expression> , <expression> )
| COALESCE ( <expression> , ... )
| CASE <expression>
WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
[ ELSE { <expression> | NULL } ]
END
| <literal>
| <sql_function>
<search_condition> ::=
{
<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]
} [ { AND | OR } ... ]
SELECT * FROM [CData].[Entities].Customers
SELECT [CompanyName] AS MY_CompanyName FROM [CData].[Entities].Customers
SELECT CAST(Balance AS VARCHAR) AS Str_Balance FROM [CData].[Entities].Customers
SELECT * FROM [CData].[Entities].Customers WHERE Country = 'US'
SELECT COUNT(*) AS MyCount FROM [CData].[Entities].Customers
SELECT COUNT(DISTINCT CompanyName) FROM [CData].[Entities].Customers
SELECT DISTINCT CompanyName FROM [CData].[Entities].Customers
SELECT CompanyName, MAX(Balance) FROM [CData].[Entities].Customers GROUP BY CompanyName
SELECT restaurants.name, zips.city FROM restaurants INNER JOIN zips ON restaurants.address_zipcode = zips.C_id
SELECT City, CompanyName FROM [CData].[Entities].Customers ORDER BY CompanyName ASC
SELECT City, CompanyName FROM [CData].[Entities].Customers LIMIT 10
SELECT * FROM [CData].[Entities].Customers WHERE Country = @param
SELECT COUNT(*) FROM [CData].[Entities].Customers WHERE Country = 'US'
SELECT COUNT(DISTINCT City) AS DistinctValues FROM [CData].[Entities].Customers WHERE Country = 'US'
SELECT CompanyName, AVG(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US' GROUP BY CompanyName
SELECT MIN(Balance), CompanyName FROM [CData].[Entities].Customers WHERE Country = 'US' GROUP BY CompanyName
SELECT CompanyName, MAX(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US' GROUP BY CompanyName
SELECT SUM(Balance) FROM [CData].[Entities].Customers WHERE Country = 'US'
SELECT [restaurants].[restaurant_id], [restaurants].name, [restaurants.grades].*FROM [restaurants.grades]JOIN [restaurants]WHERE [restaurants].name = 'Morris Park Bake Shop'
SELECT z.city, r.name, r.borough, r.cuisine, r.[address.zipcode]FROM zips zLEFT JOIN restaurants rON r.[address.zipcode] = z._id
SELECT z.city, r.name, r.borough, r.cuisine, r.[address.zipcode]FROM restaurants rINNER JOIN zips zON r.[address.zipcode] = z._id
SELECT * FROM MyTable WHERE MyDateField = L_TODAY()
SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()
SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)
SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)
SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)