ANSI SQL Compatibility of DolphinDB SQL

The following table lists 92 commonly used ANSI SQL-92 keywords, 62 of which are supported in DolphinDB. Of the 30 that are not supported: 5 are partially compatible, 7 are constraint-related (less important for data analysis), and 10 have alternatives.

DolphinDB SQL does not cover DCL commands that deal with user access control, therefore privilege-related keywords like GRANT and DENY are not included in the table below.

Note:

• √: Compatible
• ×: Incompatible
• ○: Partially compatible
• The "Description" field will provide the DolphinDB alternatives to incompatible or partially compatible keywords.

• SQL data types

• Conversion functions

DolphinDB provides keywords specific to distributed computing, as well as some special join methods.

DolphinDB also provides some query hints for SQL statements:

The following example uses the HR Sample Table from Oracle as the sample data. It is saved as the following DolphinDB tables:

• countries (dimension table)
• departments (dimension table)
• employees (dimension table)
• jobs (dimension table)
• job_history (DFS table hashed by EMPLOYEE_ID)
• locations (dimension table)
• regions (dimension table)

The schema of each table is listed below:

• countries (COUNTRIES.csv)

• departments (DEPARTMENTS.csv)

• employees (EMPLOYEES.csv)

• jobs (JOBS.csv)

• job_history (JOB_HISTORY.csv)

• locations (LOCATIONS.csv)

• regions (REGIONS.csv)

To create databases and tables in DolphinDB, you can use the create statement in a SQL statement, or use DolphinDB built-in funtions.

The following scripts use the SQL statements.

(1) Create the database

create database "dfs://hr" partitioned by HASH([INT, 10])

(2) Create tables

Take "job_history" as an example. For scripts creating other tables, refer to create_db_table_sql.txt

STEP 1: Create a DFS table.

create table "dfs://hr"."job_history" (
	EMPLOYEE_ID INT,
	START_DATE DATE,
	END_DATE DATE,
	JOB_ID SYMBOL,
	DEPARTMENT_ID INT
)
partitioned by EMPLOYEE_ID

STEP 2: Import data

The INSERT INTO statement in DolphinDB only supports appending records to in-memory tables. For dimension and DFS tables, you need to use function tableInsert/append!.

job_history_tmp=loadText(dir+"JOB_HISTORY.csv")
job_history = loadTable("dfs://hr", "job_history")

job_history.append!(job_history_tmp)
            

Note that the name of a DFS database or table cannot be referenced in DolphinDB directly because it may conflict with the variable name. You must use the loadTable function to load a table.

The ALTER statement is used to add, delete or rename columns to an existing table.

alter table tableObj add columnName columnType;
alter table tableObj drop [column] columnName;
alter table tableObj rename [column] columnName to newColumnName;

Alternatively, you can use DolphinDB built-in functions addColumn, dropColumns!, and rename!.

Note: For DFS tables, only the OLAP engine supports delete and rename operations.

(1) Add columns

Add a column "FULL_NAME" for table "employees", and update the table.

employees = loadTable("dfs://hr", "employees") // load table "employees"

alter table employees add FULL_NAME STRING 
employees = loadTable("dfs://hr", `employees) // reload the table after adding the new column
update employees set FULL_NAME=FIRST_NAME + " " + LAST_NAME
select * from employees

Output:

(2) Rename columns

Rename the column "FULL_NAME" to "EMPLOYEE_NAME".

alter table employees rename "FULL_NAME" to "EMPLOYEE_NAME" 
employees = loadTable("dfs://hr", `employees) // reload the table after renaming the column
select * from employees

Output:

(3) Delete columns

Delete the newly added column "EMPLOYEE_NAME".

alter table employees drop EMPLOYEE_NAME 
employees = loadTable("dfs://hr", `employees) // reload the table after deleting the column
select * from employees

Output:

(1) Delete the database

drop database if exists "dfs://hr"

(2) Delete the table

drop table if exists "dfs://hr"."job_history"
            

DolphinDB predicates are keywords that evaluate to true or false. The supported predicates include: (not) in, (not) like, between, (not) exists, is (not) null.

(1) (not) in

Query the records from table "employees" where "EMPLOYEE_ID" is [101, 103, 152].

select * from employees where EMPLOYEE_ID in [101, 103, 152];

Query the records from table "employees" where "EMPLOYEE_ID" is not in 100~150.

select * from employees where EMPLOYEE_ID not in 100..150;

(2) (not) like

Query the records from table "employees" where "PHONE_NUMBER" starts with "515".

select * from employees where PHONE_NUMBER like "515%";

Query the records from table "employees" where "JOB_ID" does not start with "AD".

select * from employees where JOB_ID not like "AD%";

(3) between

Count the number of employees hired in 2006.

select count(*) from employees where date(HIRE_DATE) between 2006.01.01 and 2006.12.31 // output: 24

(4) (not) exists

Since keyword exists does not support distributed queries, we need to load dimension and DFS tables as in-memory tables first.

job_history = select * from loadTable("dfs://hr", "job_history")
employees = select * from loadTable("dfs://hr", "employees")

Query the records from the "employees" table for employees who have corresponding entries in the "job_history" table.

select * from employees where exists(select * from job_history where employees.EMPLOYEE_ID in job_history.EMPLOYEE_ID)

Query the records from the "employees" table for employees who do not have corresponding entries in the "job_history" table.

select * from employees where not exists(select * from job_history where employees.EMPLOYEE_ID in job_history.EMPLOYEE_ID)

(5) is (not) nullL

Query the records from table "departments" where "MANAGE_ID" is not null.

select * from departments where MANAGER_ID is not null

Query the records from table "employees" where "COMMISSION_PCT" is null.

select * from employees where COMMISSION_PCT is null
            

The keyword distinct is used with the select/exec clause to eliminate all the duplicate records and return distinct values.

Note:

• The keyword distinct supports distributed queries to access data from DFS tables, but cannot be used with GROUP BY, CONTEXT BY, or PIVOT BY.
• The SQL keyword distinct differs from the distinct function in that the latter does not preserve the order of the returned elements, and renames the columns in the result to "distinct_colName" by default.

select distinct COUNTRY_ID from locations // (1)
select distinct(COUNTRY_ID) from locations // (2)

Output:

Count the number of distinct job IDs in table "employees"

select count(distinct JOB_ID) from employees // output: 19

Query the records with distinct department IDs and manager IDs from table "employees".

select distinct DEPARTMENT_ID, MANAGER_ID from employees
            

The any and all keywords allow you to perform a comparison between an operand (i.e., a single column value) and a range of other values. The operand includes: =, !=, >, <, <=, >=.

• any

Query the records on employees whose salary is the same as any employee in the purchasing department (with department ID=30).

select * from employees
where salary = 
any(select salary 
 from employees
 where department_id = 30) 
order by employee_id

Output:

• all

Query the records on employees whose salary is greater than or equal to the minimum salary for the IT department (with department ID=60).

 select * from employees
 where salary >=
 all (select salary from employees where department_id=60)
 order by employee_id

Output:

Note: Currently, the comparison such as ALL(1400, 3000) is not supported.

The null ordering can be specified in an order by clause with nulls first/last.

Sort the records by manager IDs in table "employees" with NULL values returned before non-NULL values.

select * from employees order by manager_id asc nulls first

Output:

Sort the records by manager IDs in table "employees" with NULL values returned after non-NULL values.

select * from employees
order by manager_id asc nulls last

Output:

Using the with clause simplifies complex SQL queries and improves readability. Repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being re-queried by each reference.

For example, we query the records on employees who have been working in the finance department for more than 5 years and have a salary of 8,000 or more.

The query first queries employees who have been working in the finance department (with department ID=100) for more than 5 years and generates a temporary table "employees_with_salary_increase". Then, queries are executed on table "employees_with_salary_increase" to select employees with a salary greater than 8,000. The results of this query are saved into table "employees_with_raise". The final results are obtained based on table "employees_with_raise".

with  
  employees_with_salary_increase as (  
    select employee_id, salary, year(now()) as current_year,   
           case when  year(now()) - year(hire_date) > 5 then 1 else 0 end as has_5_years  
    from employees  
    where department_id = 100  
  ),  
  employees_with_raise as (  
    select employee_id, salary, has_5_years  
    from employees_with_salary_increase  
    where salary > 8000  
    and has_5_years = 1  
  )  
select employee_id, salary, has_5_years  
from employees_with_raise  
order by salary desc;

Output:

In subsequent versions, by using the with clause, subqueries can be used within a complex query to perform operations in multiple steps and the temporary results will be stored, which can greatly simplify the query process and improve the efficiency.

union/union all is used to combine the result sets of 2 or more select / exec statements.

• union all

Query location IDs from the "locations" and "department" tables and use union all to combine the query results (with duplicate records keeping).

select location_id from locations 
union all 
select location_id from departments
order by location_id

Output:

• union

Query location IDs from the "locations" and "department" tables and use union to combine the query results (with duplicate records removed).

select location_id from locations 
union  
select location_id from departments
order by location_id

Output:

This section provides examples demonstrating how SQL joins are implemented in DolphinDB. Various table joiners are covered, including the cross join, inner join, left/semi join, right join and full join.

• cross join

Query all information on employees from both tables (employees a and employees b).

SQL 92

select * 
from employees a, employees b
where a.employee_id <> b.employee_id

SQL99

 select * 
 from employees a
 cross join employees b
 where a.employee_id <> b.employee_id

Part of the result:

• inner join

Query the ID of each employee and his/her manager's ID.

SQL92

select e1.employee_id, e1.manager_id
from employees e1, employees e2
where e1.manager_id = e2.employee_id
order by e1.employee_id, e1.manager_id

SQL99

select e1.employee_id, e1.manager_id
from employees e1 
inner join employees e2
on e1.manager_id = e2.employee_id
order by e1.employee_id, e1.manager_id

Part of the result:

• left join

Query the ID of each employee and his/her manager's ID (if available).

select e1.employee_id, e1.last_name, e2.employee_id as manage_id, e2.last_name as manager_name  
from employees e1
left join employees e2 
on e1.manager_id = e2.employee_id

Part of the result:

• left semi join

Query the records each department and one of its employees with salary higher than 2500.

select department_id, department_name,employee_id, first_name, last_name, salary
from departments 
left semi join employees // or left semijoin
on departments.department_id = employees.department_id 
       and employees.salary > 2500
order by department_id

Part of the result:

• right join

Query the information on each employee with a salary higher than 2500 and his/her department ID (if available).

  select department_id, employee_id, first_name, last_name, salary
 from departments 
 right join employees
 on departments.department_id = employees.department_id 
 where employees.salary > 2500
 order by department_id;

Part of the result:

• full join

Query the information on all employees and their departments.

 select department_id, department_name, employee_id, first_name, last_name, salary
 from departments a
 full join employees b
 on a.department_id = b.department_id 

Part of the result:

SQL joins supports in-memory tables, DFS tables, dimension tables, and temporary tables (generated by uncorrelated subqueries).

Query the job history of each employee to test the compatibility of joins on each type of table.

select j.job_id, j.job_title, j.min_salary
  , h.start_date, h.end_date
from jobs j  
left join job_history h 
on h.job_id = j.job_id 

In previous version, only two partitioned table can be joined. Now with the latest version, you can join multiple tables.

Join the employees and departments tables to obtain the detailed information on employees, including their IDs, names, managers, and departments.

 select a.employee_id, a.last_name, a.manager_id, b.last_name as manager_name
    , a.department_id, c.department_name
 from employees a
 inner join employees b 
 on a.manager_id = b.employee_id 
 inner join departments c 
 on a.department_id = c.department_id 

Part of the result:

Query the job history of each employee. Both FI_ACCOUNT and AC_ACCOUNT are treated as AC_ACCOUNT (accountant).

select employee_id, j.job_id, j.job_title, j.min_salary
  , h.start_date, h.end_date
from job_history h 
left join jobs j
on j.job_id = case when h.job_id in ("FI_ACCOUNT", "AC_ACCOUNT") then "FI_ACCOUNT" else h.job_id end  
order by employee_id

Output:

The join columns support:

• functions
• CASE WHEN
• columns of INT/STRING/SYMBOL type

Note that the constant expression, such as on 1=2, is not supported currently.

The dialect mode can be set in a session of the client tool, and then the parsing will be performed according to the specified SQL dialect. Take the DolphinDB GUI as an example:

Select File → Preferences → Always show sqlStd dropDown(√)

Then select the corresponding SQL dialect. Currently, three dialect modes are available: DolphinDB, Oracle and MySQL.

The following example queries information on employee salaries by department in Oracle mode, including department information, the number of employees, etc:

select   
  d.department_id, 
  d.department_name,  
  count(a.employee_id) as num_of_employee_id,
  sum(a.salary) as total_salary,  
  avg(a.salary) as avg_salary,  
  max(a.salary) as max_salary,
  decode(a.job_id, 'IT_PROG' , 'Programmer', 'FI_ACCOUNT', 'Accountant', 'Others') as job_title
from employees a 
inner join departments d
on a.department_id = d.department_id  
group by   
  d.department_id,  
  d.department_name,  
  decode(a.job_id, 'IT_PROG' , 'Programmer', 'FI_ACCOUNT', 'Accountant', 'Others') as job_title

Output:

The example above uses the decode function. Other supported functions include: concat, sysdate, nvl, to_char, to_date, to_number, regexp_like, trunc, asciistr, instr, row_number. With SQL dialect, the cost of SQL code migration is greatly reduced when migrating from Oracle to DolphinDB.

MySQL mode is used in a similar way. Currently, MySQL only supports the sysdate() function.

• Java API

Specify SqlstdEnum (with three options: DolphinDB, Oracle, MySQL) when constructing a DBconnection object.

package com.dolphindb.sqlstd;

import com.xxdb.DBConnection;
import com.xxdb.comm.SqlStdEnum;
import com.xxdb.data.Entity;
import java.io.IOException;

public class OracleMode {
    public static void main(String[] args) throws IOException {
        DBConnection connection = new DBConnection(SqlStdEnum.Oracle);
        connection.connect("192.168.1.206", 11702, "admin", "123456");
        String sql = String.format(
                "select employee_id, first_name, last_name, \n" +
                "  decode(job_id, 'IT_PROG' , 'Programmer', 'FI_ACCOUNT', 'Accountant', 'Others') as jobs_title\n" +
                "from loadTable(%s, %s) a"
                , "\"dfs://hr\"", "\"employees\""
        );
        Entity result = connection.run(sql);
        System.out.println(result.getString());
    }
}

• JDBC

Add the configuration parameter sqlStd to the url.

spring.datasource.url=jdbc:dolphindb://192.168.1.206:11702?databasePath=dfs://hr&sqlStd=Oracle
spring.datasource.username=admin
spring.datasource.password=123456
spring.datasource.driver-class-name=com.dolphindb.jdbc.Driver