# WHERE

The WHERE command filters rows from the input table based on a condition that evaluates to true. Fields with null values are excluded from results when using value exclusions, meaning WHERE field != "value" is interpreted as WHERE field != "value" AND field IS NOT NULL.

## Syntax

`WHERE expression`

### Parameters

#### expression

A boolean expression used to filter rows.

## Examples

Filter employees who are still hired:

Returns only employees whose `still_hired` field is true, showing their first and last names.
```esql
FROM employees
| KEEP first_name, last_name, still_hired
| WHERE still_hired == true
```

Simplify the condition when the field is boolean:

Filters employees to include only those who are still hired, using the boolean field directly.
```esql
FROM employees
| KEEP first_name, last_name, still_hired
| WHERE still_hired
```

Retrieve logs from the last hour using date math:

Returns log entries from the last hour by comparing the `@timestamp` field to the current time minus one hour.
```esql
FROM sample_data
| WHERE @timestamp > NOW() - 1 hour
```

Filter rows using a function, such as LENGTH:

Shows employees whose first names are shorter than four characters.
```esql
FROM employees
| KEEP first_name, last_name, height
| WHERE LENGTH(first_name) < 4
```

Check for NULL values using IS NULL:

Finds employees whose `birth_date` field is missing or null.
```esql
FROM employees
| WHERE birth_date IS NULL
```

Check for non-NULL values and count employees:

Counts the number of employees who have a non-null value in the `is_rehired` field.
```esql
FROM employees
| WHERE is_rehired IS NOT NULL
| STATS COUNT(emp_no)
```

Match text using the MATCH function:

Finds books where the `author` field matches the term "Faulkner".
```esql
FROM books
| WHERE MATCH(author, "Faulkner")
```

Match text with multiple terms and keep the title:

Returns book titles that match all the terms "Hobbit", "Back", and "Again" in the `title` field.
```esql
FROM books
| WHERE MATCH(title, "Hobbit Back Again", {"operator": "AND"})
| KEEP title;
```

Use the LIKE operator to filter based on string patterns:

Finds employees whose first names match the pattern "?b*", where "?" is any character followed by "b" and any characters after.
```esql
FROM employees
| WHERE first_name LIKE """?b*"""
| KEEP first_name, last_name
```

Escape special characters in LIKE patterns:

Filters rows where the `message` field exactly matches "foo * bar", escaping the asterisk.
```esql
ROW message = "foo * bar"
| WHERE message LIKE "foo \\* bar"
```

Reduce escaping overhead with triple quotes in LIKE:

Filters rows where the `message` field matches "foo * bar", using triple quotes to simplify escaping.
```esql
ROW message = "foo * bar"
| WHERE message LIKE """foo \* bar"""
```

Match against multiple LIKE patterns:

Returns rows where the `message` field matches either the pattern "foo*" or "bar?".
```esql
ROW message = "foobar"
| WHERE message like ("foo*", "bar?")
```

Use REST query placeholders with LIKE:

Filters employees whose first names match a pattern provided as a REST query parameter.
```esql
FROM employees
| WHERE first_name LIKE ?pattern
| KEEP first_name, last_name
```

Filter using RLIKE with regular expressions:

Finds employees whose first names match the regular expression ".leja.*".
```esql
FROM employees
| WHERE first_name RLIKE """.leja.*"""
| KEEP first_name, last_name
```

Escape special characters in RLIKE patterns:

Filters rows where the `message` field matches the regular expression "foo \( bar", escaping the parenthesis.
```esql
ROW message = "foo ( bar"
| WHERE message RLIKE "foo \\( bar"
```

Reduce escaping overhead with triple quotes in RLIKE:

Filters rows where the `message` field matches the regular expression "foo \( bar", using triple quotes for easier escaping.
```esql
ROW message = "foo ( bar"
| WHERE message RLIKE """foo \( bar"""
```

Match against multiple RLIKE patterns:

Returns rows where the `message` field matches either the regular expression "foo.*" or "bar.".
```esql
ROW message = "foobar"
| WHERE message RLIKE ("foo.*", "bar.")
```

Use REST query placeholders with RLIKE:

Filters employees whose first names match a regular expression provided as a REST query parameter.
```esql
FROM employees
| WHERE first_name RLIKE ?pattern
| KEEP first_name, last_name
```

Test membership in a list using IN:

Keeps rows where the value of `c-a` is in the list (3, b / 2, a).
```esql
ROW a = 1, b = 4, c = 3
| WHERE c-a IN (3, b / 2, a)
```