SQL Cheatsheet

How can I use arrays in SQL queries?

Every SQL database has a slightly different way of handling arrays. Here are best practices for some of our most common databases.

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
  users
where
  id IN ({{ [1, 2, 3].join() }})
SELECT
*
FROM
c --c is the container ID input in the query editor UI
WHERE
array_contains( {{ [1,2,3] }}, c.id )
SELECT
  * 
FROM 
  PUBLIC.USERS
WHERE 
  array_contains(USER_ID, array_construct( {{[123,224]}}) )

Other databases

Another clever way of getting this to work in SQL databases that support the LIKE keyword and wildcards is to take advantage of the % wildcard operator in SQL as well as the string concatenation operator ||.

select
  *
from
  users
where
  {{ 'george,fred,chris' }} like '%' || users.name || '%'

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 }} )

Updated 21 days ago


SQL Cheatsheet


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.