SQL and Cypher: Query Efficiency Comparison

SQL Approach

SELECT o.order_id, o.order_date, o.shipped_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id = 'ALFKI';

How SQL executes this: 1. Look up customer 'ALFKI' using index on customer_id 2. Scan orders table for matching customer_id (or use index) 3. Join the results

Complexity: O(log n) for indexed lookup + O(m) for matching orders

Performance Comparison

For this simple query, both perform similarly because:

Winner: Tie for simple lookups

SQL Approach (3 JOINs)

SELECT DISTINCT
    p.product_name,
    cat.category_name,
    p.unit_price
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
WHERE c.customer_id = 'ALFKI'
ORDER BY cat.category_name, p.product_name;

How SQL executes this: 1. Look up customer 'ALFKI' 2. JOIN to orders (scan/index lookup) 3. JOIN to order_details (scan/index lookup) 4. JOIN to products (scan/index lookup) 5. JOIN to categories (scan/index lookup) 6. Apply DISTINCT and ORDER BY

Complexity: Multiple index lookups and hash/merge joins

Cypher Approach

MATCH (c:Customer {customerID: 'ALFKI'})-[:PLACED]->(o:Order)
      -[:CONTAINS]->(p:Product)-[:IN_CATEGORY]->(cat:Category)
RETURN DISTINCT p.productName, cat.categoryName, p.unitPrice
ORDER BY cat.categoryName, p.productName;

How Cypher executes this: 1. Look up customer 'ALFKI' 2. Traverse PLACED relationships to Orders 3. Traverse CONTAINS relationships to Products 4. Traverse IN_CATEGORY relationships to Categories 5. Apply DISTINCT and ORDER BY

Complexity: Index lookup + direct pointer traversal (constant time per hop)

Performance Comparison

As JOINs increase, SQL performance degrades while Cypher remains efficient:

Winner: Cypher - more efficient for multi-hop queries

SQL Approach (Recursive CTE)

WITH RECURSIVE management_chain AS (
    -- Base case: start with Nancy
    SELECT employee_id, first_name, last_name, reports_to, 1 as level
    FROM employees
    WHERE first_name = 'Nancy' AND last_name = 'Davolio'

    UNION ALL

    -- Recursive case: find managers
    SELECT e.employee_id, e.first_name, e.last_name, e.reports_to, mc.level + 1
    FROM employees e
    JOIN management_chain mc ON e.employee_id = mc.reports_to
)
SELECT * FROM management_chain ORDER BY level;

How SQL executes this: 1. Execute base query to find Nancy 2. Iteratively execute recursive query until no more results 3. Union all results 4. Complex query plan with multiple iterations

Complexity: O(n * d) where n is employees and d is hierarchy depth

Cypher Approach

MATCH path = (e:Employee {firstName: 'Nancy', lastName: 'Davolio'})
              -[:REPORTS_TO*]->(manager:Employee)
RETURN e.firstName + ' ' + e.lastName AS employee,
       [n IN nodes(path) | n.firstName + ' ' + n.lastName] AS chain,
       length(path) AS levels;

How Cypher executes this: 1. Find Nancy using index 2. Traverse REPORTS_TO relationships to any depth 3. Return path information

Complexity: O(k) where k is the actual path length

Performance Comparison

Winner: Cypher — simpler and more efficient for hierarchical queries (4 lines compared to 15+ lines)

SQL Approach

SELECT DISTINCT c2.customer_id, c2.company_name
FROM customers c1
JOIN orders o1 ON c1.customer_id = o1.customer_id
JOIN order_details od1 ON o1.order_id = od1.order_id
JOIN order_details od2 ON od1.product_id = od2.product_id
JOIN orders o2 ON od2.order_id = o2.order_id
JOIN customers c2 ON o2.customer_id = c2.customer_id
WHERE c1.customer_id = 'ALFKI'
  AND c2.customer_id != 'ALFKI'
ORDER BY c2.company_name;

How SQL executes this: 1. Find ALFKI’s orders 2. Find products in those orders 3. Find other order_details with same products 4. Find orders containing those details 5. Find customers who placed those orders 6. Exclude ALFKI

Complexity: 5 JOINs with potential for large intermediate result sets

Cypher Approach

MATCH (c1:Customer {customerID: 'ALFKI'})-[:PLACED]->(:Order)
      -[:CONTAINS]->(p:Product)<-[:CONTAINS]-(:Order)<-[:PLACED]-(c2:Customer)
WHERE c1 <> c2
RETURN DISTINCT c2.customerID, c2.companyName
ORDER BY c2.companyName;

How Cypher executes this: 1. Find ALFKI 2. Traverse to their ordered products 3. Traverse back through other orders to other customers 4. Filter out ALFKI

Complexity: Direct bidirectional traversal

Performance Comparison

Winner: Cypher - pattern matching is what graphs were built for

SQL Approach

SELECT
    c.customer_id,
    c.company_name,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(od.unit_price * od.quantity * (1 - od.discount)) AS total_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
GROUP BY c.customer_id, c.company_name
ORDER BY total_value DESC
LIMIT 5;

Cypher Approach

MATCH (c:Customer)-[:PLACED]->(o:Order)-[item:CONTAINS]->(p:Product)
WITH c, COUNT(DISTINCT o) AS orderCount,
     SUM(item.unitPrice * item.quantity * (1 - item.discount)) AS totalValue
ORDER BY totalValue DESC
LIMIT 5
RETURN c.customerID, c.companyName, orderCount, totalValue;

Performance Comparison

For aggregation queries, both databases perform similarly because:

Winner: Tie for aggregation-heavy queries