Comparing Cypher with SQL
The following query examples use the Northwind dataset. They are designed to help anyone familiar with SQL to understand Cypher® and write Cypher queries equivalent to SQL. See Figure 1 for a visual representation of the difference between relational and graph models of the dataset used in the following queries.
Refer to Example datasets to learn how to load Northwind to your Neo4j instance and try the examples out.
For a more in-depth explanation on the differences and similarities between graph and relational databases, see Transition from relational to graph database. |
Query examples
Select and return records
SQL | Cypher |
---|---|
To select and return records in SQL, select everything from the
|
In Cypher, you
|
Field access, ordering, and paging
Rather than returning all attributes, you can filter out the ones you are interested in — ProductName
and UnitPrice
, for example.
SQL | Cypher |
---|---|
In SQL, this is how you order items by price and return the 10 most expensive items:
|
The statement is similar in Cypher, except for the pattern matching part:
|
Remember that labels, relationship types, and property names are case sensitive in Neo4j. For more details on naming rules, see the Cypher Manual → Naming rules and recommendations. |
Find a single product by name
There are different ways to query the database and retrieve a single item, for example, a product named Chocolade
.
Filtering by equality
SQL | Cypher |
---|---|
In SQL, you can filter data using the
|
In Cypher, the
A shorter option is to use the label
|
Indexing
Indexes are available both in SQL and Cypher and make searching for a specific node label and attribute combination more efficient.
Indexes in Cypher are only used for finding the starting points of a query; all subsequent pattern matching is done through the graph structure. Cypher supports range, text, point, lookup, full-text, and vector indexes.
In the Northwind dataset, adding indexes on the node labels productName
and unitPrice
makes searching for a product and its price quicker:
CREATE INDEX Product_productName IF NOT EXISTS FOR (p:Product) ON p.productName;
CREATE INDEX Product_unitPrice IF NOT EXISTS FOR (p:Product) ON p.unitPrice;
Indexes in Cypher are only used for finding the starting points of a query. All subsequent pattern matching is done through the graph structure. Cypher supports range, text, point, lookup, full-text, and vector indexes. Read more about how to use indexes in Cypher Manual → Using indexes. |
Filter products
There are several ways to filter results in Cypher that are similar to SQL.
Filter by list/range
SQL | Cypher |
---|---|
In SQL, you can use the operator
|
Cypher has full collection support, including
|
Filter by multiple numeric and textual predicates
SQL | Cypher |
---|---|
This query retrieves products with a name starting with "C" and a price larger than 100:
|
In Cypher, the
You can also use a regular expression:
|
Joining products with customers
SQL | Cypher |
---|---|
In SQL, if you want to see who bought
|
In Cypher, there is no need to
|
Return customers without existing orders
If you instead want to see who bought what and what they paid in total, the JOIN
in the previous SQL query stays the same, only the filter expression changes.
However, if you have customers without any orders and still want to return them, you will need to make some adjustments.
SQL | Cypher |
---|---|
In SQL, you have to use
|
In Cypher, the
Non-existing nodes and relationships will then have a |
Top-selling employees
The previous example mentioned aggregation. By summing up product prices and ordered quantities, an aggregated view per product for the customer was provided.
You can use aggregation functions like sum
, count
, avg
, and max
in both SQL and Cypher.
SQL | Cypher | ||
---|---|---|---|
In SQL, aggregation is explicit, so you have to provide all grouping keys again in the
|
In Cypher, grouping for aggregation is implicit. As soon as you use the first aggregation function, all non-aggregated columns automatically become grouping keys:
|
Employee territories
In SQL, dealing with master-detail information can be challenging. One example is when you have one main entity (master, head, parent) and many dependent ones (detail, position, child).
You can either write a query that joins both and returns the master data multiple times (once for each detail) or you fetch only the primary key of the master and then pull all detail rows via that foreign key.
SQL | Cypher | ||
---|---|---|---|
In SQL, if you look at the employees per territory, then the territory information is returned for each employee:
|
In Cypher, you can either return the structure like in SQL or use the
|
Product categories
If you have to express category, territory or organizational hierarchies in SQL, it is usually modeled with a self-join via a foreign key from child to parent. In the example of the product categories, you have to decide upfront how many levels of categories you want to query.
SQL | Cypher |
---|---|
Only three potential levels are shown here (which means 1+2+3 = 6 self-joins of the
|
Cypher is able to express hierarchies of any depth using only the appropriate relationships.
Variable levels are represented by variable length paths, which are denoted by a star
|