# Value suggestions

> Inside a WHERE or HAVING comparison the completion list holds real values from the column you're filtering, sampled from the table for free, so you don't run a SELECT DISTINCT just to remember how a status is spelled.

Source: https://docs.querylab.io/writing-sql/value-suggestions/

```sql
SELECT * FROM `ace-analytics.warehouse.dim_defect_type` WHERE defect_type_name = ''
```

## Where they appear

Suggestions come up on the right-hand side of a comparison whose left side is a column of a table in the `FROM` clause:

```sql
WHERE status = ''
WHERE status IN ('active', '')
WHERE priority =
HAVING country = ''
```

Operators: `=`, `!=`, `<>`, `<`, `>`, `<=`, `>=`, `IN`, `NOT IN`, `LIKE`, `NOT LIKE`, `BETWEEN`. Inside an `IN (…)` list, values you've already written are left out.

The quote character opens the list. For numeric columns there's no quote: the list opens after the operator, and values insert without quotes. Typing a `'` or `"` for a STRING column and picking a value keeps your quotes and inserts only the text.

- The column belongs to a table named in FROM (or a CTE that resolves to one)
  - The column is not DATE, DATETIME, TIME, or TIMESTAMP
  - The column has at most 500 distinct values in the sample

## Where the values come from

Values are sampled from the table without running a query, so they cost nothing.

- The list fills in a moment after it first opens.
- If the sample contains more than 500 distinct values, nothing is suggested. `user_id = '¦'` stays empty on purpose.

> **It's a sample.** A value that exists in the table but not in the sampled rows won't be listed.   For rare values, type them.

## Seeing names next to ID values

When the column is a foreign key (`event_type_id`, `make_key`), the raw IDs alone aren't much help. Querylab.io looks for the dimension table the key points to and shows each ID with its display value:

```sql
SELECT * FROM `ace-analytics.warehouse.fact_mot_test` WHERE make_key =
```

Only the ID is inserted. Querylab.io matches the key to a dimension table by foreign key or by name and shows its label column.

## What isn't suggested

- `LIKE` patterns: values are listed, but no wildcard patterns are generated.
- Columns of tables that are only in a subquery or not in `FROM` at all.
- Date and timestamp columns.