## 3.7. WHERE

`WHERE` adds constraints to the patterns in a `MATCH` or `OPTIONAL MATCH` clause or filters the results of a `WITH` clause.

### 3.7.1. Introduction

`WHERE` is not a clause in its own right — rather, it’s part of `MATCH`, `OPTIONAL MATCH`, `START` and `WITH`.

In the case of `WITH` and `START`, `WHERE` simply filters the results.

For `MATCH` and `OPTIONAL MATCH` on the other hand, `WHERE` adds constraints to the patterns described. It should not be seen as a filter after the matching is finished.

 In the case of multiple `MATCH` / `OPTIONAL MATCH` clauses, the predicate in `WHERE` is always a part of the patterns in the directly preceding `MATCH` / `OPTIONAL MATCH`. Both results and performance may be impacted if the `WHERE` is put inside the wrong `MATCH` clause.
 Indexes` may be used to optimize queries using `WHERE` in a variety of cases.

The following graph is used for the examples below:

### 3.7.2. Basic usage

#### 3.7.2.1. Boolean operations

You can use the boolean operators `AND`, `OR`, `XOR` and `NOT`. See Section 2.14, “Working with `null` for more information on how this works with `null`.

Query.

``````MATCH (n)
WHERE n.name = 'Peter' XOR (n.age < 30 AND n.name = 'Timothy') OR NOT (n.name = 'Timothy' OR n.name = 'Peter')
RETURN n.name, n.age``````

Table 3.46. Result
n.name n.age

3 rows

`"Andy"`

`36`

`"Timothy"`

`25`

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.name = 'Peter' XOR (n.age < 30 AND n.name = 'Timothy') OR NOT (n.name = 'Timothy' OR n.name = 'Peter') RETURN n.name, n.age

#### 3.7.2.2. Filter on node label

To filter nodes by label, write a label predicate after the `WHERE` keyword using `WHERE n:foo`.

Query.

``````MATCH (n)
WHERE n:Swedish
RETURN n.name, n.age``````

The name and age for the 'Andy' node will be returned.

Table 3.47. Result
n.name n.age

1 row

`"Andy"`

`36`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n:Swedish RETURN n.name, n.age

#### 3.7.2.3. Filter on node property

To filter on a node property, write your clause after the `WHERE` keyword.

Query.

``````MATCH (n)
WHERE n.age < 30
RETURN n.name, n.age``````

The name and age values for the 'Timothy' node are returned because he is less than 30 years of age.

Table 3.48. Result
n.name n.age

1 row

`"Timothy"`

`25`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.age < 30 RETURN n.name, n.age

#### 3.7.2.4. Filter on relationship property

To filter on a relationship property, write your clause after the `WHERE` keyword.

Query.

``````MATCH (n)-[k:KNOWS]->(f)
WHERE k.since < 2000
RETURN f.name, f.age, f.email``````

The name, age and email values for the 'Peter' node are returned because Andy has known him since before 2000.

Table 3.49. Result
f.name f.age f.email

1 row

`"Peter"`

`35`

`"peter_n@example.com"`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n)-[k:KNOWS]->(f) WHERE k.since < 2000 RETURN f.name, f.age, f.email

#### 3.7.2.5. Filter on dynamically-computed node property

To filter on a property using a dynamically computed name, use square bracket syntax.

Query.

``````WITH 'AGE' AS propname
MATCH (n)
WHERE n[toLower(propname)]< 30
RETURN n.name, n.age``````

The name and age values for the 'Timothy' node are returned because he is less than 30 years of age.

Table 3.50. Result
n.name n.age

1 row

`"Timothy"`

`25`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) WITH 'AGE' as propname MATCH (n) WHERE n[toLower(propname)] < 30 RETURN n.name, n.age

#### 3.7.2.6. Property existence checking

Use the `exists()` function to only include nodes or relationships in which a property exists.

Query.

``````MATCH (n)
WHERE exists(n.belt)
RETURN n.name, n.belt``````

The name and belt for the 'Andy' node are returned because he is the only one with a `belt` property.

 The `has()` function has been superseded by `exists()` and has been removed.
Table 3.51. Result
n.name n.belt

1 row

`"Andy"`

`"white"`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE exists(n.belt) RETURN n.name, n.belt

### 3.7.3. String matching

The prefix and suffix of a string can be matched using `STARTS WITH` and `ENDS WITH`. To undertake a substring search - i.e. match regardless of location within a string - use `CONTAINS`. The matching is case-sensitive. Attempting to use these operators on values which are not strings will return `null`.

#### 3.7.3.1. Prefix string search using `STARTS WITH`

The `STARTS WITH` operator is used to perform case-sensitive matching on the beginning of a string.

Query.

``````MATCH (n)
WHERE n.name STARTS WITH 'Pet'
RETURN n.name, n.age``````

The name and age for the 'Peter' node are returned because his name starts with 'Pet'.

Table 3.52. Result
n.name n.age

1 row

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.name STARTS WITH 'Pet' RETURN n.name, n.age

#### 3.7.3.2. Suffix string search using `ENDS WITH`

The `ENDS WITH` operator is used to perform case-sensitive matching on the ending of a string.

Query.

``````MATCH (n)
WHERE n.name ENDS WITH 'ter'
RETURN n.name, n.age``````

The name and age for the 'Peter' node are returned because his name ends with 'ter'.

Table 3.53. Result
n.name n.age

1 row

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.name ENDS WITH 'ter' RETURN n.name, n.age

#### 3.7.3.3. Substring search using `CONTAINS`

The `CONTAINS` operator is used to perform case-sensitive matching regardless of location within a string.

Query.

``````MATCH (n)
WHERE n.name CONTAINS 'ete'
RETURN n.name, n.age``````

The name and age for the 'Peter' node are returned because his name contains with 'ete'.

Table 3.54. Result
n.name n.age

1 row

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.name CONTAINS 'ete' RETURN n.name, n.age

#### 3.7.3.4. String matching negation

Use the `NOT` keyword to exclude all matches on given string from your result:

Query.

``````MATCH (n)
WHERE NOT n.name ENDS WITH 'y'
RETURN n.name, n.age``````

The name and age for the 'Peter' node are returned because his name does not end with 'y'.

Table 3.55. Result
n.name n.age

1 row

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE NOT n.name ENDS WITH 'y' RETURN n.name, n.age

### 3.7.4. Regular expressions

Cypher supports filtering using regular expressions. The regular expression syntax is inherited from the Java regular expressions. This includes support for flags that change how strings are matched, including case-insensitive `(?i)`, multiline `(?m)` and dotall `(?s)`. Flags are given at the beginning of the regular expression, for example `MATCH (n) WHERE n.name =~ '(?i)Lon.*' RETURN n` will return nodes with name 'London' or with name 'LonDoN'.

#### 3.7.4.1. Matching using regular expressions

You can match on regular expressions by using `=~ 'regexp'`, like this:

Query.

``````MATCH (n)
WHERE n.name =~ 'Tim.*'
RETURN n.name, n.age``````

The name and age for the 'Timothy' node are returned because his name starts with 'Tim'.

Table 3.56. Result
n.name n.age

1 row

`"Timothy"`

`25`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.name =~ 'Tim.*' RETURN n.name, n.age

#### 3.7.4.2. Escaping in regular expressions

Characters like `.` or `*` have special meaning in a regular expression. To use these as ordinary characters, without special meaning, escape them.

Query.

``````MATCH (n)
WHERE n.email =~ '.*\\.com'
RETURN n.name, n.age, n.email``````

The name, age and email for the 'Peter' node are returned because his email ends with '.com'.

Table 3.57. Result
n.name n.age n.email

1 row

`"Peter"`

`35`

`"peter_n@example.com"`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.email =~ '.*\\\\.com' RETURN n.name, n.age, n.email

#### 3.7.4.3. Case-insensitive regular expressions

By pre-pending a regular expression with `(?i)`, the whole expression becomes case-insensitive.

Query.

``````MATCH (n)
WHERE n.name =~ '(?i)AND.*'
RETURN n.name, n.age``````

The name and age for the 'Andy' node are returned because his name starts with 'AND' irrespective of casing.

Table 3.58. Result
n.name n.age

1 row

`"Andy"`

`36`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.name =~ '(?i)AND.*' RETURN n.name, n.age

### 3.7.5. Using path patterns in `WHERE`

#### 3.7.5.1. Filter on patterns

Patterns are expressions in Cypher, expressions that return a list of paths. List expressions are also predicates — an empty list represents `false`, and a non-empty represents `true`.

So, patterns are not only expressions, they are also predicates. The only limitation to your pattern is that you must be able to express it in a single path. You cannot use commas between multiple paths like you do in `MATCH`. You can achieve the same effect by combining multiple patterns with `AND`.

Note that you cannot introduce new variables here. Although it might look very similar to the `MATCH` patterns, the `WHERE` clause is all about eliminating matched subgraphs. `MATCH (a)-[*]→(b)` is very different from `WHERE (a)-[*]→(b)`. The first will produce a subgraph for every path it can find between `a` and `b`, whereas the latter will eliminate any matched subgraphs where `a` and `b` do not have a directed relationship chain between them.

Query.

``````MATCH (timothy { name: 'Timothy' }),(others)
WHERE others.name IN ['Andy', 'Peter'] AND (timothy)<--(others)
RETURN others.name, others.age``````

The name and age for nodes that have an outgoing relationship to the 'Timothy' node are returned.

Table 3.59. Result
others.name others.age

1 row

`"Andy"`

`36`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (timothy {name: 'Timothy'}), (others) WHERE others.name IN ['Andy', 'Peter'] AND (timothy)<--(others) RETURN others.name, others.age

#### 3.7.5.2. Filter on patterns using `NOT`

The `NOT` operator can be used to exclude a pattern.

Query.

``````MATCH (persons),(peter { name: 'Peter' })
WHERE NOT (persons)-->(peter)
RETURN persons.name, persons.age``````

Name and age values for nodes that do not have an outgoing relationship to the 'Peter' node are returned.

Table 3.60. Result
persons.name persons.age

2 rows

`"Timothy"`

`25`

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (persons), (peter {name: 'Peter'}) WHERE NOT (persons)-->(peter) RETURN persons.name, persons.age

#### 3.7.5.3. Filter on patterns with properties

Query.

``````MATCH (n)
WHERE (n)-[:KNOWS]-({ name: 'Timothy' })
RETURN n.name, n.age``````

Finds all name and age values for nodes that have a `KNOWS` relationship to a node with the name 'Timothy'.

Table 3.61. Result
n.name n.age

1 row

`"Andy"`

`36`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE (n)-[:KNOWS]-({name: 'Timothy'}) RETURN n.name, n.age

#### 3.7.5.4. Filter on relationship type

You can put the exact relationship type in the `MATCH` pattern, but sometimes you want to be able to do more advanced filtering on the type. You can use the special property `type` to compare the type with something else. In this example, the query does a regular expression comparison with the name of the relationship type.

Query.

``````MATCH (n)-[r]->()
WHERE n.name='Andy' AND type(r)=~ 'K.*'
RETURN type(r), r.since``````

This returns all relationships having a type whose name starts with 'K'.

Table 3.62. Result
type(r) r.since

2 rows

`"KNOWS"`

`1999`

`"KNOWS"`

`2012`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n)-[r]->() WHERE n.name='Andy' AND type(r) =~ 'K.*' RETURN type(r), r.since

### 3.7.6. Lists

#### 3.7.6.1. `IN` operator

To check if an element exists in a list, you can use the `IN` operator.

Query.

``````MATCH (a)
WHERE a.name IN ['Peter', 'Timothy']
RETURN a.name, a.age``````

This query shows how to check if a property exists in a literal list.

Table 3.63. Result
a.name a.age

2 rows

`"Timothy"`

`25`

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (a) WHERE a.name IN ['Peter', 'Timothy'] RETURN a.name, a.age

### 3.7.7. Missing properties and values

#### 3.7.7.1. Default to `false` if property is missing

As missing properties evaluate to `null`, the comparison in the example will evaluate to `false` for nodes without the `belt` property.

Query.

``````MATCH (n)
WHERE n.belt = 'white'
RETURN n.name, n.age, n.belt``````

Only the name, age and belt values of nodes with white belts are returned.

Table 3.64. Result
n.name n.age n.belt

1 row

`"Andy"`

`36`

`"white"`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.belt = 'white' RETURN n.name, n.age, n.belt

#### 3.7.7.2. Default to `true` if property is missing

If you want to compare a property on a node or relationship, but only if it exists, you can compare the property against both the value you are looking for and `null`, like:

Query.

``````MATCH (n)
WHERE n.belt = 'white' OR n.belt IS NULL RETURN n.name, n.age, n.belt
ORDER BY n.name``````

This returns all values for all nodes, even those without the belt property.

Table 3.65. Result
n.name n.age n.belt

3 rows

`"Andy"`

`36`

`"white"`

`"Peter"`

`35`

`<null>`

`"Timothy"`

`25`

`<null>`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (n) WHERE n.belt = 'white' OR n.belt IS NULL RETURN n.name, n.age, n.belt ORDER BY n.name

#### 3.7.7.3. Filter on `null`

Sometimes you might want to test if a value or a variable is `null`. This is done just like SQL does it, using `IS NULL`. Also like SQL, the negative is `IS NOT NULL`, although `NOT(IS NULL x)` also works.

Query.

``````MATCH (person)
WHERE person.name = 'Peter' AND person.belt IS NULL RETURN person.name, person.age, person.belt``````

The name and age values for nodes that have name 'Peter' but no belt property are returned.

Table 3.66. Result
person.name person.age person.belt

1 row

`"Peter"`

`35`

`<null>`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (person) WHERE person.name = 'Peter' AND person.belt IS NULL RETURN person.name, person.age, person.belt

### 3.7.8. Using ranges

#### 3.7.8.1. Simple range

To check for an element being inside a specific range, use the inequality operators `<`, `<=`, `>=`, `>`.

Query.

``````MATCH (a)
WHERE a.name >= 'Peter'
RETURN a.name, a.age``````

The name and age values of nodes having a name property lexicographically greater than or equal to 'Peter' are returned.

Table 3.67. Result
a.name a.age

2 rows

`"Timothy"`

`25`

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (a) WHERE a.name >= 'Peter' RETURN a.name, a.age

#### 3.7.8.2. Composite range

Several inequalities can be used to construct a range.

Query.

``````MATCH (a)
WHERE a.name > 'Andy' AND a.name < 'Timothy'
RETURN a.name, a.age``````

The name and age values of nodes having a name property lexicographically between 'Andy' and 'Timothy' are returned.

Table 3.68. Result
a.name a.age

1 row

`"Peter"`

`35`

Try this query live.  CREATE (andy:Swedish {name: 'Andy', age: 36, belt: 'white'}), (timothy {name: 'Timothy', age: 25, address: 'Sweden/Malmo'}), (peter {name: 'Peter', age: 35, email: 'peter_n@example.com'}), (andy)-[:KNOWS {since: 2012}]->(timothy), (andy)-[:KNOWS {since: 1999}]->(peter) MATCH (a) WHERE a.name > 'Andy' AND a.name < 'Timothy' RETURN a.name, a.age