OrientDB focuses on standards when it comes to query languages. SQL is widely used and well understood, so we extended it to also include graph functionality. Most developers already know SQL and feel comfortable using something familiar. However there are few differences between the standard SQL syntax and that supported by OrientDB. This is covered in OrientDB SQL dialect.
Many SQL commands share the WHERE conditions. Keywords and class names in OrientDB SQL are case insensitive. Field names and values are case sensitive. In these examples, the keywords are in uppercase but this is not required.
For example, if you have a class MyClass with a field named id, then the following SQL statements are equivalent:
SELECT FROM MyClass WHERE id = 1
select from myclass where id = 1
The following is NOT equivalent. Notice that the field name 'ID' is not the same as 'id'.
SELECT FROM MyClass WHERE ID = 1
OrientDB allow to execute queries against any field, indexed and not-indexed. The SQL engine automatically recognizes if any indexes can be used to speed up execution. You can also query directly any indexes by using index:
select from index:myIndex where key = 'Jay'
OrientDB supports SQL as a query language with some differences compared with SQL. We decided to avoid creating Yet-Another-Query-Language, but rather to start from familiar SQL with extensions to work with graphs. We prefer to focus on standards. Also, many developers are already familiar with SQL.
If you want learn SQL, there are many online courses such as:
To know more, look to OrientDB SQL Syntax.
Or order any book like these
The most important difference between OrientDB and a Relational Database is that relationships are represented by LINKS instead of JOINs.
For this reason, the classic JOIN syntax is not supported. OrientDB 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'
But in OrientDB it becomes:
SELECT * FROM Employee WHERE city.name = 'Rome'
This is much simpler, shorter and more powerful! If you use multiple JOINs, the OrientDB SQL equivalent will be even shorter. 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'
But in OrientDB it becomes:
SELECT * FROM Employee WHERE city.country.name = 'Italy'
In SQL projections are mandatory and you can use the star character * to include all of the fields. In OrientDB this type of projection is optional. Example: In SQL to select all of the columns of Customer you would write:
SELECT * FROM Customer
But in OrientDB the * is optional:
SELECT FROM Customer
In SQL, DISTINCT is a keyword but in OrientDB it is a function, so if your query is:
SELECT DISTINCT name FROM City
In OrientDB you would write:
SELECT DISTINCT(name) FROM City
OrientDB 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 OrientDB the HAVING conditions go in a select statement in the predicate:
SELECT (
SELECT city, SUM(salary) AS salary
FROM Employee
GROUP BY city
) WHERE salary > 1000
OrientDB allows only one class (classes 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 classes, you have to execute 2 sub queries and join them with the UNIONALL function:
SELECT FROM E, V
In OrientDB, 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 )