# Exact Online ### SELECT Statements A SELECT statement can consist of the following basic clauses. * SELECT * INTO * FROM * JOIN * WHERE * GROUP BY * HAVING * UNION * ORDER BY * LIMIT ### SELECT Syntax The following syntax diagram outlines the syntax supported by the SQL engine of the provider: \ SELECT { \[ TOP \ | DISTINCT ] { \* \| { \ \[ \[ AS ] \ ] \| { \ | \ } .\* } \[ , ... ] } \[ INTO csv:// \[ filename= ] \ \[ ;delimiter=tab ] ] { FROM \ \[ \[ AS ] \ ] } \[ , ... ] \[ \[ INNER | { { LEFT | RIGHT | FULL } \[ OUTER ] } ] JOIN \ \[ ON \ ] \[ \[ AS ] \ ] ] \[ ... ] \[ WHERE \ ] \[ GROUP BY \ \[ , ... ] \[ HAVING \ ] \[ UNION \[ ALL ] \ ] \[ ORDER BY \ \[ ASC | DESC ] \[ NULLS FIRST | NULLS LAST ] ] \[ LIMIT \ \[ { OFFSET | , } \ ] ] } | SCOPE\_IDENTITY() \ ::= \| \ \| @ \ \| ? \| COUNT( \* | { \[ DISTINCT ] \ } ) \| { AVG | MAX | MIN | SUM | COUNT } ( \ ) \| NULLIF ( \ , \ ) \| COALESCE ( \ , ... ) \| CASE \ WHEN { \ | \ } THEN { \ | NULL } \[ ... ] \[ ELSE { \ | NULL } ] END \| \ \| \ \ ::= { \ { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } \[ \ ] } \[ { AND | OR } ... ] #### Examples 1. Return all columns:
| `SELECT * FROM Accounts` | | ------------------------ | 2. Rename a column:
| `SELECT [Name] AS MY_Name FROM Accounts` | | ---------------------------------------- | 3. Cast a column's data as a different data type:
| `SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Accounts` | | -------------------------------------------------------------------------- | 4. Search data:
| `SELECT * FROM Accounts WHERE City = 'Raleigh'` | | ----------------------------------------------- | 5. Return the number of items matching the query criteria:
| `SELECT COUNT(*) AS MyCount FROM Accounts` | | ------------------------------------------ | 6. Return the number of unique items matching the query criteria:
| `SELECT COUNT(DISTINCT Name) FROM Accounts` | | ------------------------------------------- | 7. Return the unique items matching the query criteria:
| `SELECT DISTINCT Name FROM Accounts` | | ------------------------------------ | 8. Summarize data:
| `SELECT Name, MAX(AnnualRevenue) FROM Accounts GROUP BY Name` | | ------------------------------------------------------------- | See Aggregate Functions below for details. 9. Retrieve data from multiple tables.
| `SELECT Accounts.Name, Contacts.FullName FROM Accounts, Contacts WHERE Accounts.ID=Contacts.Account` | | ---------------------------------------------------------------------------------------------------- | See JOIN Queries below for details. 10. Sort a result set in ascending order:
| `SELECT Id, Name FROM Accounts ORDER BY Name ASC` | | -------------------------------------------------- | 11. Restrict a result set to the specified number of rows:
| `SELECT Id, Name FROM Accounts LIMIT 10` | | ---------------------------------------- | 12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
| `SELECT * FROM Accounts WHERE City = @param` | | -------------------------------------------- | ### Aggregate Functions #### COUNT Returns the number of rows matching the query criteria.
| `SELECT COUNT(*) FROM Accounts WHERE City = 'Raleigh'` | | ------------------------------------------------------ | #### COUNT(DISTINCT) Returns the number of distinct, non-null field values matching the query criteria.
| `SELECT COUNT(DISTINCT Id) AS DistinctValues FROM Accounts WHERE City = 'Raleigh'` | | ---------------------------------------------------------------------------------- | #### AVG Returns the average of the column values.
| `SELECT Name, AVG(AnnualRevenue) FROM Accounts WHERE City = 'Raleigh'` `GROUP BY Name` | | --------------------------------------------------------------------------------------- | #### MIN Returns the minimum column value.
| `SELECT MIN(AnnualRevenue), Name FROM Accounts WHERE City = 'Raleigh'` `GROUP BY Name` | | -------------------------------------------------------------------------------------- | #### MAX Returns the maximum column value.
| `SELECT Name, MAX(AnnualRevenue) FROM Accounts WHERE City = 'Raleigh'` `GROUP BY Name` | | -------------------------------------------------------------------------------------- | #### SUM Returns the total sum of the column values.
| `SELECT SUM(AnnualRevenue) FROM Accounts WHERE City = 'Raleigh'` | | ---------------------------------------------------------------- | ### JOIN Queries The Provider for Exact Online supports standard SQL joins like the following examples. #### Inner Join An inner join selects only rows from both tables that match the join condition:
| `SELECT Accounts.Name, Contacts.FullName FROM Accounts, Contacts WHERE Accounts.ID=Contacts.Account` | | ---------------------------------------------------------------------------------------------------- | #### Left Join A left join selects all rows in the FROM table and only matching rows in the JOIN table:
| `SELECT Accounts.Name, Contacts.FullName FROM Accounts LEFT OUTER JOIN Contacts ON Accounts.ID=Contacts.Account` | | ---------------------------------------------------------------------------------------------------------------- | ### Date Literal Functions 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. #### L\_TODAY() The current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_TODAY()` | | ----------------------------------------------------- | #### L\_YESTERDAY() The previous day.
| `SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()` | | --------------------------------------------------------- | #### L\_TOMORROW() The following day.
| `SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()` | | -------------------------------------------------------- | #### L\_LAST\_WEEK() Every day in the preceding week.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()` | | --------------------------------------------------------- | #### L\_THIS\_WEEK() Every day in the current week.
| `SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()` | | --------------------------------------------------------- | #### L\_NEXT\_WEEK() Every day in the following week.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_WEEK()` | | --------------------------------------------------------- | Also available: * L\_LAST/L\_THIS/L\_NEXT MONTH * L\_LAST/L\_THIS/L\_NEXT QUARTER * L\_LAST/L\_THIS/L\_NEXT YEAR #### L\_LAST\_N\_DAYS(n) The previous n days, excluding the current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)` | | ------------------------------------------------------------ | #### L\_NEXT\_N\_DAYS(n) The following n days, including the current day.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_DAYS(3)` | | ------------------------------------------------------------ | Also available: * L\_LAST/L\_NEXT\_90\_DAYS #### L\_LAST\_N\_WEEKS(n) Every day in every week, starting n weeks before current week, and ending in the previous week.
| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_WEEKS(3)` | | ------------------------------------------------------------- | #### L\_NEXT\_N\_WEEKS(n) Every day in every week, starting the following week, and ending n weeks in the future.
| `SELECT * FROM MyTable WHERE MyDateField = L_NEXT_N_WEEKS(3)` | | ------------------------------------------------------------- | Also available: * L\_LAST/L\_NEXT\_N\_MONTHS(n) * L\_LAST/L\_NEXT\_N\_QUARTERS(n) * L\_LAST/L\_NEXT\_N\_YEARS(n)