# SQL Reference {#chapter-8-sql-statements}

This chapter covers how to use SQL to access, retrieve and manipulate data in DolphinDB. DolphinDB's SQL syntax is very similar to the ANSI SQL language in a Relational Database Management System \(RDBMS\) such as MySQL, Oracle, SQL Server, etc.

## Syntax {#syntax}

```
select [top_clause] column_expressions
from table_name | table_expression
[where filtering_conditions]
[grouping_clause [having_clause] | order_clause]
```

## Common features with ANSI SQL {#common-features-with-standard-sql}

-   Support `select`, `insert`, `update` and `delete` statement for retrieving, inserting, updating and deleting records in a table, respectively. Since 1.30.17/2.00.5, DolphinDB supports the create statement to create a database \(table\), and the alter statement to add columns to a table. Starting in version 1.30.22/2.00.10, all DolphinDB SQL keywords can be written in all-capital letters \(e.g., SELECT, FROM, WHERE\).

-   Support the `where` clause.

-   Support `group by` and `order by` clauses.

-   Support table join, including `inner join`, `left join`, `left semijoin`, `full join`.

-   Starting from version 2.00.10, line breaks are supported for SQL statements. Note that:

    -   Keywords with multiple words \(such as ORDER BY, GROUP BY, UNION ALL, INNER JOIN\) cannot be split into two lines.

    -   If an alias for a column or table is not specified with keyword *as*, it must follow the original name without a line break.


## What are the differences? {#what-are-the-differences}

-   Most functions can be directly called in SQL queries.

-   Other differences as listed below.


|ANSI SQL syntax|DolphinDB syntax|Explanation|
|---------------|----------------|-----------|
|N/A|context by|context by is an innovation of DolphinDB. It simplifies processing time-series data within each group. With group by, each group returns a scalar; with context by, each group returns a vector of the same size as the group's records.|
|N/A|pivot by|pivot by transforms a vector into a matrix or table.|
|N/A|cgroup by|Perform cumulative grouping calculations.|
|N/A|map|Execute the SQL statement on each partition separately, then merge the results.|
|N/A|aj|Asof join. It takes each record in the left table as a reference and checks if there is a match in the right table. If there is no match, the most recent observation will be chosen. If there are more than one match, the last one will be chosen.|
|N/A|wj, pwj|Window join and prevailing window join. They are a generalization of asof join. For each row in the left table, window join applies aggregate functions on a window of rows in the right table. If the right table doesn't have a matching value for the window, prevailing window join will fill it with the last value before the window and then apply the aggregate functions.|

## Compatibility for SQL Dialects {#compatibility-for-sql-dialects}

Since version 1.30.22/2.00.10, DolphinDB has enhanced the compatibility for Oracle and MySQL dialects. In addition to supporting ANSI SQL syntax, DolphinDB deals with the inconsistent behaviors of functions with the same name due to dialect-specific features: You can select a dialect mode in a session and run scripts written in that dialect. Currently, three dialect modes are available: DolphinDB, Oracle and MySQL.

**Note:**

-   Scripts written in DolphinDB language can be correctly parsed in Oracle or MySQL dialect mode.

-   Only part of the functions or features of Oracle/MySQL are supported:

<table id="table_fbn_ln5_c2c"><thead><tr><th align="left">

**SQL Dialect**

</th><th align="left">

**Features**

</th><th align="left">

**Functions \(case insensitive\)**

</th><th align="left">

**Syntax Reference**

</th></tr></thead><tbody><tr><td align="left">

Oracle

</td><td align="left">

Comment symbols: --, /\*\*/

 Concatenation operator:\|\|

</td><td align="left">

asciistr, concat, decode, instr, length, listagg, nvl, nvl2, rank, regexp\_like, replace, to\_char, to\_date, to\_number, trunc, wm\_concat

 Note: to\_char only accepts numeric, DATE, DATEHOUR, and DATETIME types.

</td><td align="left">

[SQL Language Reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Functions.html#GUID-D079EFD3-C683-441F-977E-2C9503089982)

</td></tr><tr><td align="left">

MySQL

</td><td align="left">



</td><td align="left">

sysdate

</td><td align="left">

[MySQL :: MySQL 8.0 Reference Manual :: 12 Functions and Operators](https://dev.mysql.com/doc/refman/8.0/en/functions.html)

</td></tr></tbody>
</table>
