SQL Cheatsheet

Learn about some best practices for working with SQL.

Use arrays in queries

Every SQL database has a slightly different way of handling arrays. Because SQL resources in Retool are set to convert queries to prepared statements by default this can have more challenging interactions with multiple values inside a single parameter. Here are best practices for some of our most common databases without having to disable prepared statements.

select
  *
from
  users
where id = ANY({{ [1, 2, 3] }})

SELECT
  *
FROM
  users
WHERE
  id IN (
    SELECT
      convert(int, value)
    FROM
      string_split({{ [1, 2, 3] }}, ',')
  )

SELECT
  *
FROM
  users
WHERE
  id IN (
    SELECT
      Split.a.value('.', 'NVARCHAR(MAX)') DATA
    FROM
      (
        SELECT
          CAST(
            '<X>' + REPLACE({{ [1,2,3,4,5,5,6].join(',') }}, ',', '</X><X>') + '</X>' AS XML
          ) AS String
      ) AS A
      CROSS APPLY String.nodes('/X') AS Split(a)
  )

select
  *
from
  users
where
  id IN ({{ [1, 2, 3] }})

select
  *
from
  users
where id IN UNNEST({{ [1, 2, 3] }})

SELECT
*
FROM
c --c is the container ID input in the query editor UI
WHERE
array_contains( {{ [1,2,3] }}, c.id )

Databases with unique array structures

SELECT
  * 
FROM 
  PUBLIC.USERS
WHERE 
  ARRAY_CONTAINS(ID::variant, SPLIT( {{[123,224].join()}}, ',') )

select
  *
from
  users
where
  id IN ({{ [1, 2, 3].join() }})

select
  *
from
  users
where
  CONTAINS({{','+'george,fred,chris'+',' }}, ',' || users.name || ',')

As a final fallback, another clever way of getting this to work in SQL databases that support substring matching is to convert your array into a comma separated string beginning and ending with a comma. If your column to matches with a comma added before and after (using the || operator), that would be a unique match.

Show all data when a filter is not in use

A common use case is to have a dropdown that allows a user to filter the users by status. However, if you want to show all statuses when no status is selected, you will need to use the following pattern to achieve your goal.

select
  *
from
  users
where
  ( {{ !select1.value }} OR users.status = {{ select1.value }} )

select 
  * 
from 
  users
where 
  ( {{!select1.value ? 1 : 0}} = 1 OR users.status = {{ select1.value }} )

Organize WHERE clauses

There are three approaches to keep in mind whenever you write queries with complex or specific conditional logic.

Combine logic often

Avoid writing redundant logic inside a WHERE clause and combine conditions to make queries quicker and easier to understand.

select
  item,
  category
from
  food
where
  (category = 'Fruit') AND (item = 'Orange' OR item = 'Apple')

This WHERE clause contains a redundant AND condition since both Orange and Apple are already part of the Fruit category. Removing unnecessary conditions speeds up queries and reduces complexity.

Organize logic

Use parentheses () to organize conditions. This helps your queries perform as expected and are easier to understand.

select
  *
from
  users
where
  (status in ('Active', 'Trial') AND last_active = '01-01-2022') 
  OR (owner = '{{current_user.fullName}}' )

Filter data with transformers

Use transformers to filter queries of smaller data sets. Transformers further reduce complexity and allow you to refine query data using JavaScript.

Transformers run client-side in the browser. For larger data sets, keep conditional logic within your queries so that your apps remain performant.