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 MongoDB adapter:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [ 
      INNER 
    ] 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 | IN | NOT IN | AND | OR } [ <expression> ]
    } [ { AND | OR } ... ]

Examples

Return all columns:

SELECT * FROM [CData].[Sample].Customers

Rename a column:

SELECT "CompanyName" AS MY_CompanyName FROM [CData].[Sample].Customers

Cast a column's data as a different data type:

SELECT CAST(Balance AS VARCHAR) AS Str_Balance FROM [CData].[Sample].Customers

Search data:

SELECT * FROM [CData].[Sample].Customers WHERE Country = 'US'

The MongoDB APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, IN, NOT IN, AND, OR.
```
SELECT * FROM [CData].[Sample].Customers WHERE Country = 'US';
```

Return the number of items matching the query criteria:

SELECT COUNT(*) AS MyCount FROM [CData].[Sample].Customers

Return the number of unique items matching the query criteria:

SELECT COUNT(DISTINCT CompanyName) FROM [CData].[Sample].Customers

Return the unique items matching the query criteria:

SELECT DISTINCT CompanyName FROM [CData].[Sample].Customers

Sort a result set in ascending order:

SELECT City, CompanyName FROM [CData].[Sample].Customers  ORDER BY CompanyName ASC

Aggregate Functions

For SELECT examples using aggregate functions, see Aggregate Functions.

JOIN Queries

See JOIN Queries for SELECT query examples using JOINs.

Date Literal Functions