SQL Best Practices in Retool: A Cheatsheet for Every Database

If you're building internal tools in Retool, SQL is something you'll write constantly — and SQL best practices in Retool aren't always obvious, especially when prepared statements change how your queries behave. This cheatsheet covers the most important patterns: handling arrays across different database flavors, writing conditional filters that show all data when nothing is selected, and organizing WHERE clauses so your queries stay readable and fast.

Why SQL in Retool Behaves Differently Than You Expect

Retool converts queries to prepared statements by default. That's great for security, but it means passing an array like [1, 2, 3] as a single parameter doesn't work the same way across every database. Instead of disabling prepared statements (which opens you up to SQL injection), use the database-specific patterns below.

How to Use Arrays in Retool SQL Queries by Database

Every major SQL database handles array parameters differently inside Retool. Here are the correct patterns for each one:

• PostgreSQL
  SELECT * FROM users WHERE id = ANY({{ [1, 2, 3] }})
• MS SQL Server (2016+)
  SELECT * FROM users WHERE id IN ( SELECT convert(int, value) FROM string_split({{ [1, 2, 3].toString() }}, ',') )
• MS SQL Server (pre-2016) — Uses XML parsing to split the array string:
  SELECT * FROM users WHERE id IN ( SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA FROM ( SELECT CAST( '<X>' + REPLACE({{ [1,2,3].JOIN(',') }}, ',', '</X><X>') + '</X>' AS XML ) AS String ) AS A CROSS APPLY String.nodes('/X') AS Split(a) )
• MySQL
  SELECT * FROM users WHERE id IN ({{ [1, 2, 3] }})
• BigQuery
  SELECT * FROM users WHERE id IN UNNEST({{ [1, 2, 3] }})
• Snowflake
  SELECT * FROM PUBLIC.USERS WHERE ARRAY_CONTAINS(ID::variant, SPLIT({{[123,224].join()}}, ','))
• Redshift
  SELECT * FROM users WHERE id IN ({{ [1, 2, 3].join() }})
• Cosmos DB
  SELECT * FROM c WHERE array_contains({{ [1,2,3] }}, c.id)
• Databricks
  SELECT * FROM users WHERE contains({{ multiselect1.value.join() }}, user_name)
  Or: WHERE user_name IN (SELECT explode(split({{ "'" + multiselect1.value.join(',') + "'" }}, ',')))
• Apache Hive (JDBC)
  SELECT * FROM users WHERE array_contains(split({{ multiselect.value.join() }}, ','), user_name)
• SQLite (JDBC)
  SELECT * FROM users WHERE json({{ JSON.stringify(multiselect.value.reduce((o,k)=>(o[k]=1,o),{})) }}) -> user_name

Fallback for any SQL database with substring matching: Convert your array into a comma-separated string that starts and ends with a comma, then match against the column value also wrapped in commas using the || operator. This guarantees a unique match and avoids partial hits.

SELECT * FROM users WHERE CONTAINS({{','+'george,fred,chris'+',' }}, ',' || users.name || ',')

How to Show All Data When a Filter Is Not Selected

A very common Retool pattern: you have a select1 dropdown to filter by status, but when nothing is selected you want to return all rows. Here's how to handle it without writing two separate queries.

• Standard SQL
  SELECT * FROM users WHERE ( {{ !select1.value }} OR users.status = {{ select1.value }} )
• MS SQL Server — Booleans don't work the same way, so use a numeric flag:
  SELECT * FROM users WHERE ( {{!select1.value ? 1 : 0}} = 1 OR users.status = {{ select1.value }} )
• Multiselect fields — Use .value == 0 (not .length) to check for an empty selection:
  SELECT * FROM location JOIN leads ON leads."Location ID" = location."location_id" WHERE ({{source_select.value == 0}}) OR (leads."Source" = ANY ({{ source_select.value }}))

How to Organize WHERE Clauses in Retool Queries

Complex conditional logic is where Retool SQL queries go wrong most often. Here are three principles to follow:

• Combine logic — don't repeat it. If you're filtering by category and item, don't add redundant conditions. For example, if both Orange and Apple are already in the Fruit category, you don't need a separate AND category = 'Fruit' clause for each. Write it once:
  SELECT item, category FROM food WHERE (category = 'Fruit') AND (item = 'Orange' OR item = 'Apple')
• Use parentheses to make logic explicit. Wrap each logical group in () so precedence is clear and the query behaves the way you intend — especially when mixing AND and OR:
  SELECT * FROM users WHERE (status IN ('Active', 'Trial') AND last_active = '01-01-2022') OR (owner = '{{current_user.fullName}}')
• Use transformers for client-side filtering on small datasets. Retool transformers run in the browser using JavaScript and are ideal for slicing or reshaping query results after they've been fetched. This keeps your SQL simpler. However, for large datasets, always push the filtering logic into the SQL query itself — client-side filtering on thousands of rows will make your app feel slow.

The Quick Reference You'll Come Back To

Bookmark this page. Whether you're debugging a broken array filter at 11pm or setting up a new database resource in Retool, these patterns cover the overwhelming majority of SQL issues you'll run into. The key rule: stay inside prepared statements, adapt the syntax to your database flavor, and keep your WHERE clauses clean and explicit.