SQL

Introduction to SQL

When it comes to query languages, SQL is the most widely recognized standard. The majority of developers have experience and are comfortable with SQL. For this reason ArcadeDB uses SQL as its query language and adds some extensions to enable graph functionality. There are a few differences between the standard SQL syntax and that supported by ArcadeDB, but for the most part, it should feel very natural. The differences are covered in the ArcadeDB SQL dialect section of this page.

If you are looking for the most efficient way to traverse a graph, we suggest using MATCH instead.

Many SQL commands share the WHERE condition. Keywords are case insensitive, but type names, property names and values are case sensitive. In the following examples keywords are in uppercase but this is not strictly required.

For example, if you have a type MyType with a field named id, then the following SQL statements are equivalent:

SELECT * FROM MyType WHERE id = 1
select * from MyType where id = 1

The following is NOT equivalent. Notice that the field name 'ID' is not the same as 'id'.

SELECT * FROM MyType WHERE ID = 1

Also the following query is NOT equivalent because of the type 'mytype ' is not the same as 'MyType'.

SELECT * FROM mytype WHERE id = 1

Automatic usage of indexes

ArcadeDB allows you to execute queries against any field, indexed or not-indexed. The SQL engine automatically recognizes if any indexes can be used to speed up execution. You can also query any indexes directly by using INDEX:<index-name> as a target. Example:

SELECT * FROM INDEX:myIndex WHERE key = 'Jay'

Extra resources

ArcadeDB SQL dialect

ArcadeDB supports SQL as a query language with some differences compared with SQL. The ArcadeDB team decided to avoid creating Yet-Another-Query-Language. Instead we started from familiar SQL with extensions to work with graphs. We prefer to focus on standards.

SQL command categorization

Learning SQL

If you want to learn SQL, there are many online courses such as:

alternatively, order a book such as:

For details on ArcadeDB’s dialect, see ArcadeDB SQL Syntax. You can also download a SQL Command Cheat Sheet (pdf).

The simplest SQL query just returns a constant, and is given by:

SELECT 1

and could have practical use, for example, as a connection test. Furthermore, such constant queries can be used as a calculator:

SELECT sqrt(6.0 * 7.0)

A typical ArcadeDB SQL query has the following components:

SELECT projections
FROM type
WHERE predicate
GROUP BY property
ORDER BY projection
SKIP number
LIMIT number

for further components or details, see the SELECT command. As for classic SQL the execution order in ArcadeDB SQL is:

  1. FROM

  2. WHERE

  3. GROUP BY

  4. SELECT

  5. ORDER BY

  6. SKIP

  7. LIMIT

No JOINs

The most important difference between ArcadeDB and a Relational Database is that relationships are represented by LINKS instead of JOINs.

For this reason, the typical JOIN syntax of relational databases is not supported. ArcadeDB uses the "dot (.) notation" to navigate LINKS. Example 1 : In SQL you might create a join such as:

SELECT *
FROM Employee A, City B
WHERE A.city = B.id
  AND B.name = 'Rome'

In ArcadeDB, an equivalent operation would be:

SELECT *
FROM Employee
WHERE city.name = 'Rome'

Another example:

SELECT *
FROM Employee A, City B, Country C
WHERE A.city = B.id
  AND B.country = C.id
  AND C.name = 'Italy'

In ArcadeDB, an equivalent operation would be:

SELECT *
FROM Employee
WHERE city.country.name = 'Italy'

Furthermore, RIDs can be resolved by nested projections.

Projection

In SQL, projections are mandatory and you can use the star character * to include all of the fields. With ArcadeDB this type of projection is optional. Example: In SQL to select all of the columns of Customer you would write:

SELECT * FROM Customer

In ArcadeDB, the * may be omitted:

SELECT FROM Customer

System Types

To retrieve information about the schema, indexes and the database, here a three special types from which one can "select":

  • schema:types

  • schema:buckets

  • schema:indexes

  • schema:database

these can be treated such as any other types, so projections and filters apply as for any other types.

SELECT FROM schema:types

DISTINCT

You can use DISTINCT keyword exactly as in a relational database:

SELECT DISTINCT name FROM City

HAVING

ArcadeDB does not support the HAVING keyword, but with a nested query it’s easy to obtain the same result. Example in SQL:

SELECT city, sum(salary) AS salary
FROM Employee
GROUP BY city
HAVING salary > 1000

This groups all of the salaries by city and extracts the result of aggregates with the total salary greater than 1000 dollars. In ArcadeDB the HAVING conditions go in a select statement in the predicate:

SELECT
FROM ( SELECT city, SUM(salary) AS salary FROM Employee GROUP BY city )
WHERE salary > 1000

Multiple targets

ArcadeDB allows only one type (types are equivalent to tables in this discussion) as opposed to SQL, which allows for many tables as the target. If you want to select from 2 types, you have to execute 2 sub queries and join them with the UNIONALL function:

SELECT FROM E, V

In ArcadeDB, you can accomplish this with a few variable definitions and by using the expand function to the union:

DISTINCT vs distinct()

Query results without duplicates can be realized either using the keyword DISTINCT or the function distinct(). The keyword DISTINCT is be used if the entire projection should be unique, while the distinct function returns unique elements only for its argument field

UNWIND vs expand()

Collections fields can be spread into a set of results by either using the keyword UNWIND or the function expand(). Both produce similar results but via different semantics and constraints.

SQL Differences

Following are some basic differences between ArcadeDB and PostgreSQL.

Postgres ArcadeDB

CREATE TABLE

CREATE TYPE

ALTER TABLE

ALTER TYPE

ADD COLUMN

CREATE PROPERTY

ALTER COLUMN

ALTER PROPERTY

SELECT * FROM information_schema.tables

SELECT FROM schema:types

SELECT * FROM pg_indexes

SELECT FROM schema:indexes

SELECT * FROM pg_database

SELECT FROM schema:database

However, there is a SQL condition where the two systems do differ. When you use functions that do not affect the column name, SQL defines that function name as the column name. In ArcadeDB, since we’re projecting from a record, the resulting name remains the property name.

For instance, in SQL:

SELECT count(*) FROM Employee

would result in a data set with one column named "count(*)".

The ArcadeDB equivalent is:

SELECT count(*) FROM Employee

resulting in a data set with one column name of "count". If you specify a name for a column in a projection using the AS keyword, both SQL and ArcadeDB would use the provided name.

If you prefer having the function name as the column name, like standard SQL, use AS, like: 
SELECT count(*) as _count_ FROM Employee

Commands

While the ArcadeDB SQL syntax is very similar to SQL-92, commands are similar but different. For more commands, see the diagram above.

Comments

Use // for single line comments:

// This is a single line comment

Use /* …​ */ for multi-line comments.

/* this is a
   multiline comment */