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.
Updated 6 months ago