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
-
Match for traversing graphs
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.
Learning SQL
If you want to learn SQL, there are many online courses such as:
alternatively, order a book such as:
-
or any of these.
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:
-
FROM
-
WHERE
-
GROUP BY
-
SELECT
-
ORDER BY
-
SKIP
-
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'
This is much more straight forward and powerful! If you use multiple JOINs, the ArcadeDB SQL equivalent will be an even larger benefit. Example 2: In SQL you might create a join such as:
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.
Projections
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
The result is similar, for details see Query Results.
System Types
To retrieve information about the schema, indexes and the database, there 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 1,000 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:
SELECT expand( $c ) LET $a = ( SELECT FROM E ), $b = ( SELECT FROM V ), $c = unionall( $a, $b )
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 |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|