> ## Documentation Index
> Fetch the complete documentation index at: https://docs.dynamosql.com/llms.txt
> Use this file to discover all available pages before exploring further.

# WHERE Predicates

> Filter rows with comparisons, BETWEEN, IN, EXISTS, LIKE, IS NULL, and more.

## Comparison Operators

Standard equality and inequality operators:

```sql theme={null}
SELECT * FROM myschema.orders WHERE status = 'pending'
SELECT * FROM myschema.orders WHERE status != 'cancelled'
SELECT * FROM myschema.orders WHERE status <> 'cancelled'   -- synonym for !=
SELECT * FROM myschema.orders WHERE total > 100
SELECT * FROM myschema.orders WHERE total >= 50
SELECT * FROM myschema.orders WHERE total < 1000
SELECT * FROM myschema.orders WHERE total <= 999.99
```

## BETWEEN / NOT BETWEEN

```sql theme={null}
SELECT * FROM myschema.orders WHERE total BETWEEN 50 AND 200
SELECT * FROM myschema.orders WHERE total NOT BETWEEN 50 AND 200
```

`BETWEEN a AND b` is inclusive on both bounds.

## IN / NOT IN with Value List

```sql theme={null}
SELECT * FROM myschema.orders WHERE status IN ('pending', 'processing', 'shipped')
SELECT * FROM myschema.orders WHERE status NOT IN ('cancelled', 'refunded')
```

## IN / NOT IN with Subquery

```sql theme={null}
SELECT * FROM myschema.customers
WHERE id IN (SELECT customer_id FROM myschema.orders WHERE total > 500)
```

## IN / NOT IN with Row Value Constructor

```sql theme={null}
SELECT * FROM myschema.shipments
WHERE (origin, destination) IN (('NYC', 'LAX'), ('SFO', 'ORD'))
```

## EXISTS / NOT EXISTS (Correlated Subquery)

```sql theme={null}
-- Customers who have placed at least one order
SELECT c.id, c.name
FROM myschema.customers AS c
WHERE EXISTS (
  SELECT 1 FROM myschema.orders AS o WHERE o.customer_id = c.id
)

-- Customers with no orders
SELECT c.id, c.name
FROM myschema.customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM myschema.orders AS o WHERE o.customer_id = c.id
)
```

## LIKE / NOT LIKE

Two wildcard characters are supported:

| Wildcard | Matches                                 |
| -------- | --------------------------------------- |
| `%`      | Any sequence of zero or more characters |
| `_`      | Exactly one character                   |

```sql theme={null}
SELECT * FROM myschema.products WHERE name LIKE 'Widget%'   -- starts with "Widget"
SELECT * FROM myschema.products WHERE name LIKE '%Pro%'      -- contains "Pro"
SELECT * FROM myschema.products WHERE sku LIKE 'A__-%%'      -- "A" + 2 chars + "-" + anything
SELECT * FROM myschema.products WHERE sku NOT LIKE 'DISC-%'
SELECT * FROM myschema.products WHERE sku LIKE 'A#_%' ESCAPE '#'      -- starts with literal "A_"
SELECT * FROM myschema.products WHERE sku LIKE 'A#_#%' ESCAPE '#'     -- exactly literal "A_%"
```

<Note>
  Use `ESCAPE` when you need literal `%`, `_`, or the escape character itself inside the pattern. Escaping is explicit only: there is no implicit backslash escape mode.
</Note>

## IS NULL / IS NOT NULL

```sql theme={null}
SELECT * FROM myschema.orders WHERE shipped_at IS NULL
SELECT * FROM myschema.orders WHERE shipped_at IS NOT NULL
```

## Not Supported

* `IS DISTINCT FROM` / `IS NOT DISTINCT FROM`
* `ANY` / `ALL` quantifiers (e.g., `WHERE x > ANY (SELECT ...)`)