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.

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:

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 read with BigQuery’s tabledata.list API, which doesn’t run a query and isn’t billed. Querylab.io reads about 1,000 rows and keeps the distinct values. Small tables are read from the start; large ones from the start, middle, and end, so a date-ordered table doesn’t only show its oldest data.

• The first request for a column starts the fetch in the background; the list re-opens by itself when the values arrive.
• Results are cached in your browser for an hour, so the next time is instant.
• If the sample contains more than 500 distinct values, the column is treated as high-cardinality and nothing is suggested. user_id = '¦' stays empty on purpose.

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:

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

Only the ID is inserted. The dimension table is found in one of two ways:

1. A foreign-key constraint on the table, when one is declared.
2. Naming convention: make_key or make_id looks for dim_make, makes, make, DimMake, and similar in the same dataset.

The display value is the first of name, title, label, description, display_name, value, text, code that the dimension table has. Dimension tables over 1,000 rows are skipped; the plain sampled values are shown instead.

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 (a 1,000-row sample of a multi-year table would suggest the wrong dates).