SQL Cheatsheet

How can I use arrays in SQL 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.

How do I show all my 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 }} )

Did this page help you?