Comparing Cypher with SQL

relational vs graph
Figure 1. The relational and graph models of the Northwind dataset.

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 products table:

SELECT p.*
FROM products as p;

In Cypher, you MATCH a simple pattern: all nodes with the label :Product, and RETURN them:

MATCH (p:Product)
RETURN p;

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:

SELECT p.ProductName, p.UnitPrice
FROM products as p
ORDER BY p.UnitPrice DESC
LIMIT 10;

The statement is similar in Cypher, except for the pattern matching part:

MATCH (p:Product)
RETURN p.productName, p.unitPrice
ORDER BY p.unitPrice DESC
LIMIT 10;

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 WHERE clause:

SELECT p.ProductName, p.UnitPrice
FROM products AS p
WHERE p.ProductName = 'Chocolade';

In Cypher, the WHERE clause belongs to the MATCH statement:

MATCH (p:Product)
WHERE p.productName = 'Chocolade'
RETURN p.productName, p.unitPrice;

A shorter option is to use the label productName to specify the product in the MATCH statement:

MATCH (p:Product {productName:'Chocolade'})
RETURN p.productName, p.unitPrice;

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 IN:

SELECT p.ProductName, p.UnitPrice
FROM products as p
WHERE p.ProductName IN ('Chocolade','Chai');

Cypher has full collection support, including IN and other collection functions, predicates, and transformations:

MATCH (p:Product)
WHERE p.productName IN ['Chocolade','Chai']
RETURN p.productName, p.unitPrice;

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:

SELECT p.ProductName, p.UnitPrice
FROM products AS p
WHERE p.ProductName LIKE 'C%' AND p.UnitPrice > 100;

In Cypher, the LIKE operator is replaced by the STARTS WITH, CONTAINS, and ENDS WITH operators:

MATCH (p:Product)
WHERE p.productName STARTS WITH 'C' AND p.unitPrice > 100
RETURN p.productName, p.unitPrice;

You can also use a regular expression:

MATCH (p:Product)
WHERE p.productName =~ '^C.*'
RETURN p.productName, p.unitPrice

Joining products with customers

SQL Cypher

In SQL, if you want to see who bought Chocolade, you can join the four tables together:

SELECT DISTINCT c.CompanyName
FROM customers AS c
JOIN orders AS o ON (c.CustomerID = o.CustomerID)
JOIN order_details AS od ON (o.OrderID = od.OrderID)
JOIN products AS p ON (od.ProductID = p.ProductID)
WHERE p.ProductName = 'Chocolade';

In Cypher, there is no need to JOIN tables. You can express connections as graph patterns instead:

MATCH (p:Product {productName:'Chocolade'})<-[:ORDERS]-(:Order)<-[:PURCHASED]-(c:Customer)
RETURN DISTINCT c.companyName;

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 OUTER JOINS to make sure that results are returned even if there are no matching rows in other tables:

SELECT p.ProductName, sum(od.UnitPrice * od.Quantity) AS Volume
FROM customers AS c
LEFT OUTER JOIN orders AS o ON (c.CustomerID = o.CustomerID)
LEFT OUTER JOIN order_details AS od ON (o.OrderID = od.OrderID)
LEFT OUTER JOIN products AS p ON (od.ProductID = p.ProductID)
WHERE c.CompanyName = 'Drachenblut Delikatessen'
GROUP BY p.ProductName
ORDER BY Volume DESC;

In Cypher, the MATCH between customer and order becomes an OPTIONAL MATCH, which is the equivalent of an OUTER JOIN:

MATCH (c:Customer {companyName:'Drachenblut Delikatessen'})
OPTIONAL MATCH (p:Product)<-[o:ORDERS]-(:Order)<-[:PURCHASED]-(c)
RETURN p.productName, toInteger(sum(o.unitPrice * o.quantity)) AS volume
ORDER BY volume DESC;

Non-existing nodes and relationships will then have a null value, which will result in attributes being null and not being aggregated by sum.

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 GROUP BY clause. To see the top-selling employees, run the following query:

SELECT e.EmployeeID, e.FirstName, e.LastName, COUNT(*) AS Count
FROM Employee AS e
JOIN Orders AS o ON (o.EmployeeID = e.EmployeeID)
GROUP BY e.EmployeeID, e.FirstName, e.LastName
ORDER BY Count DESC
LIMIT 10;

In Cypher, grouping for aggregation is implicit. As soon as you use the first aggregation function, all non-aggregated columns automatically become grouping keys:

MATCH (:Order)<-[:SOLD]-(e:Employee)
WITH e, count(*) as cnt
ORDER BY cnt DESC LIMIT 10
RETURN e.employeeID, e.firstName, e.lastName, cnt

Additional aggregation functions like collect, percentileCont, stdDev are also available.

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:

SELECT e.LastName, et.Description
FROM Employee AS e
JOIN EmployeeTerritory AS et ON (et.EmployeeID = e.EmployeeID)
JOIN Territory AS t ON (et.TerritoryID = t.TerritoryID);

In Cypher, you can either return the structure like in SQL or use the collect() aggregation function, which aggregates values into a collection (list, array). This way, only one row per parent, containing an inlined collection of child values, is returned:

MATCH (t:Territory)<-[:IN_TERRITORY]-(e:Employee)
RETURN t.territoryDescription, collect(e.lastName);

This also works for nested values.

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 ProductCategory table):

SELECT p.ProductName
FROM Product AS p
JOIN ProductCategory pc ON (p.CategoryID = pc.CategoryID AND pc.CategoryName = "Dairy Products")

JOIN ProductCategory pc1 ON (p.CategoryID = pc1.CategoryID)
JOIN ProductCategory pc2 ON (pc2.ParentID = pc2.CategoryID AND pc2.CategoryName = "Dairy Products")

JOIN ProductCategory pc3 ON (p.CategoryID = pc3.CategoryID)
JOIN ProductCategory pc4 ON (pc3.ParentID = pc4.CategoryID)
JOIN ProductCategory pc5 ON (pc4.ParentID = pc5.CategoryID AND pc5.CategoryName = "Dairy Products")
;

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 * after the relationship type and optional limits (min..max):

MATCH (p:Product)-[:PART_OF]->(l:Category)-[:PARENT*0..]-(:Category {name:'Dairy Products'})
RETURN p.name;