# Kintone Platform

### SELECT Statements <a href="#default" id="default"></a>

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:\


| <p><code>SELECT</code> <code>{</code></p><p>  <code>[ TOP</code> <code>&#x3C;numeric_literal> | DISTINCT</code> <code>]</code></p><p>  <code>{</code></p><p>    <code>*</code></p><p>    <code>| {</code></p><p>        <code>&#x3C;expression> [ [ AS</code> <code>] &#x3C;column_reference> ]</code></p><p>        <code>| { &#x3C;table_name> | &#x3C;correlation_name> } .*</code></p><p>      <code>} [ , ... ]</code></p><p>  <code>}</code></p><p>  <code>[ INTO</code> <code>csv:// [ filename= ] &#x3C;file_path> [ ;delimiter=tab ] ]</code></p><p>  <code>{</code></p><p>    <code>FROM</code> <code>&#x3C;table_reference> [ [ AS</code> <code>] &#x3C;identifier> ]</code></p><p>  <code>} [ , ... ]</code></p><p>  <code>[ [</code> </p><p>      <code>INNER</code> <code>| { { LEFT</code> <code>| RIGHT</code> <code>| FULL</code> <code>} [ OUTER</code> <code>] }</code></p><p>    <code>] JOIN</code> <code>&#x3C;table_reference> [ ON</code> <code>&#x3C;search_condition> ] [ [ AS</code> <code>] &#x3C;identifier> ]</code></p><p>  <code>] [ ... ]</code></p><p>  <code>[ WHERE</code> <code>&#x3C;search_condition> ]</code></p><p>  <code>[ GROUP</code> <code>BY</code> <code>&#x3C;column_reference> [ , ... ]</code></p><p>  <code>[ HAVING</code> <code>&#x3C;search_condition> ]</code></p><p>  <code>[ UNION</code> <code>[ ALL</code> <code>] &#x3C;select_statement> ]</code></p><p>  <code>[</code></p><p>    <code>ORDER</code> <code>BY</code></p><p>    <code>&#x3C;column_reference> [ ASC</code> <code>| DESC</code> <code>] [ NULLS FIRST</code> <code>| NULLS LAST</code> <code>]</code></p><p>  <code>]</code></p><p>  <code>[</code></p><p>    <code>LIMIT &#x3C;expression></code></p><p>    <code>[</code></p><p>      <code>{ OFFSET | , }</code></p><p>      <code>&#x3C;expression></code></p><p>    <code>]</code></p><p>  <code>]</code></p><p><code>} | SCOPE_IDENTITY()</code></p><p> </p><p><code>&#x3C;expression> ::=</code></p><p>  <code>| &#x3C;column_reference></code></p><p>  <code>| @ &#x3C;parameter></code></p><p>  <code>| ?</code></p><p>  <code>| COUNT( * | { [ DISTINCT</code> <code>] &#x3C;expression> } )</code></p><p>  <code>| { AVG</code> <code>| MAX</code> <code>| MIN</code> <code>| SUM</code> <code>| COUNT</code> <code>} ( &#x3C;expression> )</code></p><p>  <code>| NULLIF</code> <code>( &#x3C;expression> , &#x3C;expression> )</code></p><p>  <code>| COALESCE</code> <code>( &#x3C;expression> , ... )</code></p><p>  <code>| CASE</code> <code>&#x3C;expression></code></p><p>      <code>WHEN</code> <code>{ &#x3C;expression> | &#x3C;search_condition> } THEN</code> <code>{ &#x3C;expression> | NULL</code> <code>} [ ... ]</code></p><p>    <code>[ ELSE</code> <code>{ &#x3C;expression> | NULL</code> <code>} ]</code></p><p>    <code>END</code></p><p>  <code>| &#x3C;literal></code></p><p>  <code>| &#x3C;sql_function></code></p><p> </p><p><code>&#x3C;search_condition> ::=</code></p><p>  <code>{</code></p><p>    <code>&#x3C;expression> { = | > | &#x3C; | >= | &#x3C;= | &#x3C;> | != | LIKE</code> <code>| NOT</code> <code>LIKE</code> <code>| IN</code> <code>| NOT</code> <code>IN</code> <code>| IS</code> <code>NULL</code> <code>| IS</code> <code>NOT</code> <code>NULL</code> <code>| AND</code> <code>| OR</code> <code>| CONTAINS</code> <code>| BETWEEN</code> <code>} [ &#x3C;expression> ]</code></p><p>  <code>} [ { AND</code> <code>| OR</code> <code>} ... ]</code></p> |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

#### Examples

1.  Return all columns:\


    | `SELECT * FROM Comments` |
    | ------------------------ |
2.  Rename a column:\


    | `SELECT [Text] AS MY_Text FROM Comments` |
    | ---------------------------------------- |
3.  Cast a column's data as a different data type:\


    | `SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Comments` |
    | -------------------------------------------------------------------------- |
4.  Search data:\


    | `SELECT * FROM Comments WHERE AppId = '1354841'` |
    | ------------------------------------------------ |
5.  Return the number of items matching the query criteria:\


    | `SELECT COUNT(*) AS MyCount FROM Comments` |
    | ------------------------------------------ |
6.  Return the number of unique items matching the query criteria:\


    | `SELECT COUNT(DISTINCT Text) FROM Comments` |
    | ------------------------------------------- |
7.  Return the unique items matching the query criteria:\


    | `SELECT DISTINCT Text FROM Comments` |
    | ------------------------------------ |
8.  Summarize data:\


    | `SELECT Text, MAX(AnnualRevenue) FROM Comments GROUP BY Text` |
    | ------------------------------------------------------------- |

    See Aggregate Functions below for details.
9.  Retrieve data from multiple tables.\


    | `SELECT Apps.Name, Comments.Text FROM Apps INNER JOIN Comments ON Apps.AppId = Comments.AppId WHERE Comments.Appid=5 AND Comments.RecordId=1` |
    | --------------------------------------------------------------------------------------------------------------------------------------------- |

    See JOIN Queries below for details.
10. Sort a result set in ascending order:\


    | `SELECT CreatorName, Text FROM Comments  ORDER BY Text ASC` |
    | ----------------------------------------------------------- |
11. Restrict a result set to the specified number of rows:\


    | `SELECT CreatorName, Text FROM Comments 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 Comments WHERE AppId = @param` |
    | --------------------------------------------- |

### Aggregate Functions <a href="#default" id="default"></a>

#### COUNT <a href="#count" id="count"></a>

Returns the number of rows matching the query criteria.\


| `SELECT COUNT(*) FROM Comments WHERE AppId = '1354841'` |
| ------------------------------------------------------- |

#### COUNT(DISTINCT) <a href="#countdistinct" id="countdistinct"></a>

Returns the number of distinct, non-null field values matching the query criteria.\


| `SELECT COUNT(DISTINCT CreatorName) AS DistinctValues FROM Comments WHERE AppId = '1354841'` |
| -------------------------------------------------------------------------------------------- |

#### AVG <a href="#avg" id="avg"></a>

Returns the average of the column values.\


| `SELECT Text, AVG(AnnualRevenue) FROM Comments WHERE AppId = '1354841'`  `GROUP BY Text` |
| ---------------------------------------------------------------------------------------- |

#### MIN <a href="#min" id="min"></a>

Returns the minimum column value.\


| `SELECT MIN(AnnualRevenue), Text FROM Comments WHERE AppId = '1354841'` `GROUP BY Text` |
| --------------------------------------------------------------------------------------- |

#### MAX <a href="#max" id="max"></a>

Returns the maximum column value.\


| `SELECT Text, MAX(AnnualRevenue) FROM Comments WHERE AppId = '1354841'` `GROUP BY Text` |
| --------------------------------------------------------------------------------------- |

#### SUM <a href="#sum" id="sum"></a>

Returns the total sum of the column values.\


| `SELECT SUM(AnnualRevenue) FROM Comments WHERE AppId = '1354841'` |
| ----------------------------------------------------------------- |

### JOIN Queries <a href="#default" id="default"></a>

The Provider for Kintone 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 Apps.Name, Comments.Text FROM Apps INNER JOIN Comments ON Apps.AppId = Comments.AppId WHERE Comments.Appid=5 AND Comments.RecordId=1` |
| --------------------------------------------------------------------------------------------------------------------------------------------- |

#### Left Join

A left join selects all rows in the FROM table and only matching rows in the JOIN table:\


| `SELECT Apps.Name, Comments.Text FROM Apps LEFT JOIN Comments ON Apps.AppId = Comments.AppId WHERE Comments.Appid=5 AND Comments.RecordId=1` |
| -------------------------------------------------------------------------------------------------------------------------------------------- |

### Date Literal Functions <a href="#default" id="default"></a>

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() <a href="#ltoday" id="ltoday"></a>

The current day.\


| `SELECT * FROM MyTable WHERE MyDateField = L_TODAY()` |
| ----------------------------------------------------- |

#### L\_YESTERDAY() <a href="#lyesterday" id="lyesterday"></a>

The previous day.\


| `SELECT * FROM MyTable WHERE MyDateField = L_YESTERDAY()` |
| --------------------------------------------------------- |

#### L\_TOMORROW() <a href="#ltomorrow" id="ltomorrow"></a>

The following day.\


| `SELECT * FROM MyTable WHERE MyDateField = L_TOMORROW()` |
| -------------------------------------------------------- |

#### L\_LAST\_WEEK() <a href="#llastweek" id="llastweek"></a>

Every day in the preceding week.\


| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_WEEK()` |
| --------------------------------------------------------- |

#### L\_THIS\_WEEK() <a href="#lthisweek" id="lthisweek"></a>

Every day in the current week.\


| `SELECT * FROM MyTable WHERE MyDateField = L_THIS_WEEK()` |
| --------------------------------------------------------- |

#### L\_NEXT\_WEEK() <a href="#lnextweek" id="lnextweek"></a>

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) <a href="#llastndaysn" id="llastndaysn"></a>

The previous n days, excluding the current day.\


| `SELECT * FROM MyTable WHERE MyDateField = L_LAST_N_DAYS(3)` |
| ------------------------------------------------------------ |

#### L\_NEXT\_N\_DAYS(n) <a href="#lnextndaysn" id="lnextndaysn"></a>

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) <a href="#llastnweeksn" id="llastnweeksn"></a>

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) <a href="#lnextnweeksn" id="lnextnweeksn"></a>

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)
