WHERE clause

WHERE isn’t usually considered a standalone clause but rather a part of the MATCH, OPTIONAL MATCH and WITH clauses.

When used next to the WITH clause, the WHERE clause only filters the results, while when used with MATCH and OPTIONAL MATCH it adds constraints to the described patterns.

WHERE should be used directly after MATCH or OPTIONAL MATCH clauses in order to avoid problems with performance or results.

  1. Basic usage
    1.1. Boolean Operators
    1.2. Inequality Operators Operators
    1.3. Filter with node labels
    1.4. Filter with node properties
    1.5. Filter with relationship properties
    1.6. Check if property is not null
    1.7. Filter with pattern expressions
  2. String matching
  3. Regular Expressions
  4. Existential subqueries
    4.1. Basic EXISTS in WHERE
    4.2. Negation with NOT EXISTS
    4.3. When to use EXISTS instead of pattern expressions
    4.4. RETURN in EXISTS subqueries
    4.5. EXISTS with UNION
    4.6. Outer scope variables and WITH

Dataset

The following examples are executed with this dataset. You can create this dataset locally by executing the queries at the end of the page: Dataset queries.

Data set

1. Basic Usage

1.1. Boolean Operators

Standard boolean operators like NOT, AND, OR and XOR can be used:

MATCH (c:Country)
WHERE c.language = 'English' AND  c.continent = 'Europe'
RETURN c.name;

Output:

+----------------+
| c.name         |
+----------------+
| United Kingdom |
+----------------+

1.2. Inequality Operators Operators

Standard inequality operators like <, <=, > and >= can be used:

MATCH (c:Country)
WHERE (c.population > 80000000)
RETURN c.name;

Output:

+---------+
| c.name  |
+---------+
| Germany |
+---------+

1.3. Filter with node labels

Nodes can be filtered by their label using the WHERE clause instead of specifying it directly in the MATCH clause:

MATCH (c)
WHERE c:Country
RETURN c.name;

Output:

+----------------+
| c.name         |
+----------------+
| Germany        |
| France         |
| United Kingdom |
+----------------+

1.4. Filter with node properties

Just as labels, node properties can be used in the WHERE clause to filter nodes:

MATCH (c:Country)
WHERE c.population < 70000000
RETURN c.name;

Output:

+----------------+
| c.name         |
+----------------+
| France         |
| United Kingdom |
+----------------+

1.5. Filter with relationship properties

Just as with node properties, relationship properties can be used as filters:

MATCH (:Country {name: 'United Kingdom'})-[r]-(p)
WHERE r.date_of_start = 2014
RETURN p;

Output:

+---------------------------+
| p                         |
+---------------------------+
| (:Person {name: "Harry"}) |
| (:Person {name: "Anna"})  |
+---------------------------+

1.6. Check if property is not null

To check if a node or relationship property exists use the IS NOT NULL option:

MATCH (c:Country)
WHERE c.name = 'United Kingdom' AND c.population IS NOT NULL
RETURN c.name, c.population;

Output:

+----------------+----------------+
| c.name         | c.population   |
+----------------+----------------+
| United Kingdom | 66000000       |
+----------------+----------------+

1.7. Filter with pattern expressions

Currently, we support pattern expression filters with the exists(pattern) function, which can perform filters based on neighboring entities:

MATCH (p:Person)
WHERE exists((p)-[:LIVING_IN]->(:Country {name: 'Germany'}))
RETURN p.name
ORDER BY p.name;

The exists() function can be used only with the WHERE clause.

Output:

+----------------+
| c.name         |
+----------------+
| Anna           |
| John           |
+----------------+

2. String matching

Apart from comparison and concatenation operators Cypher provides special string operators for easier matching of substrings:

OperatorDescription
a STARTS WITH bReturns true if the prefix of string a is equal to string b.
a ENDS WITH bReturns true if the suffix of string a is equal to string b.
a CONTAINS bReturns true if some substring of string a is equal to string b.
MATCH (c:Country)
WHERE c.name STARTS WITH 'G' AND NOT c.name CONTAINS 't'
RETURN c.name;

Output:

+---------+
| c.name  |
+---------+
| Germany |
+---------+

3. Regular expressions

Inside WHERE clause, you can use regular expressions for text filtering. To use a regular expression, you need to use the =~ operator.

For example, finding all Person nodes which have a name ending with a:

MATCH (n:Person) WHERE n.name =~ ".*a$" RETURN n;

Output:

+--------------------------+
| n                        |
+--------------------------+
| (:Person {name: "Anna"}) |
+--------------------------+

The regular expression syntax is based on the modified ECMAScript regular expression grammar. The ECMAScript grammar can be found here, while the modifications are described in this document.

4. Existential subqueries

Existential subqueries allow you to use EXISTS { ... } within WHERE (or as a standalone expression) to test whether a subquery returns at least one row. The subquery can reference variables from the outer scope (correlated subquery), while variables created inside the subquery are not visible outside of it.

4.1. Basic EXISTS in WHERE

Return people who live in Germany:

MATCH (p:Person)
WHERE EXISTS {
  MATCH (p)-[:LIVING_IN]->(:Country {name: 'Germany'})
}
RETURN p.name
ORDER BY p.name;

Output:

+---------+
| p.name  |
+---------+
| Anna    |
| John    |
+---------+

4.2. Negation with NOT EXISTS

Return people who do not live in the United Kingdom:

MATCH (p:Person)
WHERE NOT EXISTS {
  MATCH (p)-[:LIVING_IN]->(:Country {name: 'United Kingdom'})
}
RETURN p.name
ORDER BY p.name;

Output:

+---------+
| p.name  |
+---------+
| John    |
+---------+

4.3. When to use EXISTS instead of pattern expressions

Pattern expressions like exists( (p)-[:FRIENDS_WITH]-() ) are convenient for simple existence checks, but they cannot contain additional clauses such as WHERE, WITH/aggregation, or multiple pattern parts. EXISTS { ... } supports this additional logic.

For example, return people who have at least two friendships (uses aggregation inside the subquery):

MATCH (p:Person)
WHERE EXISTS {
  MATCH (p)-[:FRIENDS_WITH]-(:Person)
  WITH count(*) AS friendsCount
  WHERE friendsCount >= 2
}
RETURN p.name
ORDER BY p.name;

Output:

+---------+
| p.name  |
+---------+
| Anna    |
| Harry   |
| John    |
+---------+

You can also include property predicates on relationships inside the subquery. For example, people connected by a friendship that started before 2012:

MATCH (p:Person)
WHERE EXISTS {
  MATCH (p)-[r:FRIENDS_WITH]-(:Person)
  WHERE r.date_of_start < 2012
}
RETURN p.name
ORDER BY p.name;

Output:

+---------+
| p.name  |
+---------+
| Harry   |
| John    |
+---------+

4.4. RETURN in EXISTS subqueries

EXISTS subqueries do not require a RETURN clause. If one is present, it does not need to be aliased (unlike CALL subqueries), and any variables returned within the EXISTS subquery are not available after the subquery finishes.

MATCH (person:Person)
WHERE EXISTS {
    MATCH (person)-[:LIVING_IN]->(country:Country)
    RETURN country.name
}
RETURN person.name AS name

Output:

+---------+
| name    |
+---------+
| Anna    |
| Harry   |
| John    |
+---------+

4.5. EXISTS with UNION

EXISTS can be used with a UNION clause, and the RETURN clauses are not required. If one branch has a RETURN clause, then all branches require one. If any UNION branch returns at least one row, the entire EXISTS expression evaluates to true.

MATCH (person:Person)
WHERE EXISTS {
        MATCH (person)-[:LIVING_IN]->(:Country {name: 'Germany'})
        UNION
        MATCH (person)-[:WORKING_IN]->(:Country {name: 'United Kingdom'})
    }
RETURN person.name AS name

Output:

+---------+
| name    |
+---------+
| Anna    |
| Harry   |
+---------+

4.6. Outer scope variables and WITH

Variables from the outside scope are visible for the entire subquery, even when using a WITH clause. Shadowing these variables is not allowed. An outside scope variable is shadowed when a newly introduced variable within the inner scope is defined with the same name. The example below shadows the outer variable countryName and will therefore throw an error.

WITH 'United Kingdom' as countryName
MATCH (person:Person)-[:LIVING_IN]->(c:Country {name: countryName})
WHERE EXISTS {
    WITH "Germany" AS countryName
    MATCH (person)-[:LIVING_IN]->(d:Country)
    WHERE d.name = countryName
}
RETURN person.name AS name
+---------+
| name    |
+---------+
| Anna    |
| John    |
+---------+

Dataset queries

We encourage you to try out the examples by yourself. You can get our dataset locally by executing the following query block.

MATCH (n) DETACH DELETE n;
 
CREATE (c1:Country {name: 'Germany', language: 'German', continent: 'Europe', population: 83000000});
CREATE (c2:Country {name: 'France', language: 'French', continent: 'Europe', population: 67000000});
CREATE (c3:Country {name: 'United Kingdom', language: 'English', continent: 'Europe', population: 66000000});
 
MATCH (c1),(c2)
WHERE c1.name= 'Germany' AND c2.name = 'France'
CREATE (c2)<-[:WORKING_IN {date_of_start: 2014}]-(p:Person {name: 'John'})-[:LIVING_IN {date_of_start: 2014}]->(c1);
 
MATCH (c)
WHERE c.name= 'United Kingdom'
CREATE (c)<-[:WORKING_IN {date_of_start: 2014}]-(p:Person {name: 'Harry'})-[:LIVING_IN {date_of_start: 2013}]->(c);
 
MATCH (p1),(p2)
WHERE p1.name = 'John' AND p2.name = 'Harry'
CREATE (p1)-[:FRIENDS_WITH {date_of_start: 2011}]->(p2);
 
MATCH (p1),(p2)
WHERE p1.name = 'John' AND p2.name = 'Harry'
CREATE (p1)<-[:FRIENDS_WITH {date_of_start: 2012}]-(:Person {name: 'Anna'})-[:FRIENDS_WITH {date_of_start: 2014}]->(p2);
 
MATCH (p),(c1),(c2)
WHERE p.name = 'Anna' AND c1.name = 'United Kingdom' AND c2.name = 'Germany'
CREATE (c2)<-[:LIVING_IN {date_of_start: 2014}]-(p)-[:LIVING_IN {date_of_start: 2014}]->(c1);
 
MATCH (n)-[r]->(m) RETURN n,r,m;