---
title: ES|QL WHERE command
description: 
url: https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/where
products:
  - Elasticsearch
---

# ES|QL WHERE command
<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available
</applies-to>

The `WHERE` processing command produces a table that contains all the rows from
the input table for which the provided condition evaluates to `true`.
<tip>
  In case of value exclusions, fields with `null` values will be excluded from search results.
  In this context a `null` means either there is an explicit `null` value in the document or
  there is no value at all. For example: `WHERE field != "value"` will be interpreted as
  `WHERE field != "value" AND field IS NOT NULL`.
</tip>


## Syntax

```esql
WHERE expression
```


## Parameters

<definitions>
  <definition term="expression">
    A boolean expression.
  </definition>
</definitions>


## Examples

The following examples show common ways to filter rows with `WHERE`.

### Filter rows by field value

```esql
FROM employees
| KEEP first_name, last_name, still_hired
| WHERE still_hired == true
```

Which, if `still_hired` is a boolean field, can be simplified to:
```esql
FROM employees
| KEEP first_name, last_name, still_hired
| WHERE still_hired
```


### Filter by time range

Use date math to retrieve data from a specific time range. For example, to
retrieve the last hour of logs:
```esql
FROM sample_data
| WHERE @timestamp > NOW() - 1 hour
```


### Use WHERE with a function

`WHERE` supports various [functions](/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-functions-operators#esql-functions).
For example the [`LENGTH`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/string-functions/length) function:
```esql
FROM employees
| KEEP first_name, last_name, height
| WHERE LENGTH(first_name) < 4
```

For a complete list of all functions, refer to [Functions overview](/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-functions-operators#esql-functions).

### NULL Predicates

For NULL comparison, use the `IS NULL` and `IS NOT NULL` predicates.
**Example**
```esql
FROM employees
| WHERE birth_date IS NULL
```


| first_name:keyword | last_name:keyword |
|--------------------|-------------------|
| Basil              | Tramer            |
| Florian            | Syrotiuk          |
| Lucien             | Rosenbaum         |

**Example**
```esql
FROM employees
| WHERE is_rehired IS NOT NULL
| STATS COUNT(emp_no)
```


| COUNT(emp_no):long |
|--------------------|
| 84                 |


### Matching text

Use [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/search-functions/match) to perform a
[match query](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/query-dsl/query-dsl-match-query) on a specified field.
`MATCH` works on text fields as well as boolean, date, and numeric types.

## Examples

```esql
FROM books
| WHERE MATCH(author, "Faulkner")
```


| book_no:keyword | author:text                                        |
|-----------------|----------------------------------------------------|
| 2378            | [Carol Faulkner, Holly Byers Ochoa, Lucretia Mott] |
| 2713            | William Faulkner                                   |
| 2847            | Colleen Faulkner                                   |
| 2883            | William Faulkner                                   |
| 3293            | Danny Faulkner                                     |

```esql
FROM books
| WHERE MATCH(title, "Hobbit Back Again", {"operator": "AND"})
| KEEP title;
```


| title:text                         |
|------------------------------------|
| The Hobbit or There and Back Again |

<tip>
  You can also use the shorthand [match operator](/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/operators#esql-match-operator) `:` instead of `MATCH`.
</tip>


### LIKE and RLIKE

Use `LIKE` to filter data based on string patterns using wildcards. `LIKE` usually acts on a field placed on the left-hand side of the operator, but it can also act on a constant (literal) expression. The right-hand side of the operator represents the pattern.
The following wildcard characters are supported:
- `*` matches zero or more characters.
- `?` matches one character.

**Supported types**

| str     | pattern | result  |
|---------|---------|---------|
| keyword | keyword | boolean |
| text    | keyword | boolean |

**Example**
```esql
FROM employees
| WHERE first_name LIKE """?b*"""
| KEEP first_name, last_name
```


| first_name:keyword | last_name:keyword |
|--------------------|-------------------|
| Ebbe               | Callaway          |
| Eberhardt          | Terkki            |

When used on `text` fields, `LIKE` treats the field as a `keyword` and does not use the analyzer.
This means the pattern matching is case-sensitive and must match the exact string indexed.
To perform full-text search, use the `MATCH` or `QSTR` functions.
Matching the exact characters `*` and `.` will require escaping.
The escape character is backslash `\`. Since also backslash is a special character in string literals,
it will require further escaping.
```esql
ROW message = "foo * bar"
| WHERE message LIKE "foo \\* bar"
```

To reduce the overhead of escaping, we suggest using triple quotes strings `"""`
```esql
ROW message = "foo * bar"
| WHERE message LIKE """foo \* bar"""
```

<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available since 9.1
</applies-to>

Both a single pattern or a list of patterns are supported. If a list of patterns is provided,
the expression will return true if any of the patterns match.
```esql
ROW message = "foobar"
| WHERE message like ("foo*", "bar?")
```

Patterns may be specified with REST query placeholders as well
```esql
FROM employees
| WHERE first_name LIKE ?pattern
| KEEP first_name, last_name
```

<applies-to>
  - Elastic Stack: Generally available since 9.3
</applies-to>

Use `RLIKE` to filter data based on string patterns using using [regular expressions](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/query-dsl/regexp-syntax). `RLIKE` usually acts on a field placed on the left-hand side of the operator, but it can also act on a constant (literal) expression. The right-hand side of the operator represents the pattern.
**Supported types**

| str     | pattern | result  |
|---------|---------|---------|
| keyword | keyword | boolean |
| text    | keyword | boolean |

**Example**
```esql
FROM employees
| WHERE first_name RLIKE """.leja.*"""
| KEEP first_name, last_name
```


| first_name:keyword | last_name:keyword |
|--------------------|-------------------|
| Alejandro          | McAlpine          |

When used on `text` fields, `RLIKE` treats the field as a `keyword` and does not use the analyzer.
This means the pattern matching is case-sensitive and must match the exact string indexed.
To perform full-text search, use the `MATCH` or `QSTR` functions.
Matching special characters (eg. `.`, `*`, `(`...) will require escaping.
The escape character is backslash `\`. Since also backslash is a special character in string literals,
it will require further escaping.
```esql
ROW message = "foo ( bar"
| WHERE message RLIKE "foo \\( bar"
```

To reduce the overhead of escaping, we suggest using triple quotes strings `"""`
```esql
ROW message = "foo ( bar"
| WHERE message RLIKE """foo \( bar"""
```

<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available since 9.2
</applies-to>

Both a single pattern or a list of patterns are supported. If a list of patterns is provided,
the expression will return true if any of the patterns match.
```esql
ROW message = "foobar"
| WHERE message RLIKE ("foo.*", "bar.")
```

Patterns may be specified with REST query placeholders as well
```esql
FROM employees
| WHERE first_name RLIKE ?pattern
| KEEP first_name, last_name
```

<applies-to>
  - Elastic Stack: Generally available since 9.3
</applies-to>


### IN

The `IN` operator allows testing whether a field or expression equals an element
in a list of literals, fields or expressions:
**Example**
```esql
ROW a = 1, b = 4, c = 3
| WHERE c-a IN (3, b / 2, a)
```


| a:integer | b:integer | c:integer |
|-----------|-----------|-----------|
| 1         | 4         | 3         |

For a complete list of all operators, refer to [Operators](/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-functions-operators#esql-operators-overview).