SQL Cheatsheet - best practices for different flavors of SQL syntax

[kbn]

Learn about some best practices for working with SQL! This post is back by popular demand -- we all use SQL in our Retool apps fairly often. Example SQL snippets to use for the various SQL resource types can be super useful to have on hand.

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.

Postgres

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)

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

MySQL

SELECT
  *
FROM
  users
WHERE
  id IN ({{ [1, 2, 3] }})

BigQuery

SELECT
  *
FROM
  users
WHERE
  id
IN UNNEST({{ [1, 2, 3] }})

Cosmos DB

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

Databricks

SELECT
 * 
FROM
 users
WHERE
 contains ( {{ multiselect1.value.join() }}, user_name );
-- replace multiSelect1 with the source of the array you are using
-- replace user_name with the column name you want to reference

-- or another option here is:
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
-- Use Javascript to create object from array for use in query
WHERE json({{ JSON.stringify(multiselect.value.reduce((o,k)=>(o[k]=1,o),{})) }}) -> user_name

Databases with unique array structures

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

OtherSQL

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.

SQL

SELECT
  *
FROM
  users
WHERE ( {{ !select1.value }} OR users.status = {{ select1.value }} )

MS SQL Server

SELECT
  *
FROM
  users
WHERE ( {{!select1.value ? 1 : 0}} = 1 OR users.status = {{ select1.value }} )

Show data when filter is not in use for multiselect fields

SELECT * FROM "location"
JOIN leads ON leads_test."Location ID" = "location"."location_id"
WHERE
  ({{source_select.value == 0}}) -- This worked for me, not .length, but .value
  OR (leads_test."Source" = ANY ({{ source_select.value }})) -- Here you replace it for your multiselect

Thanks to @Member_Machine_Tech_Support for this one!^

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.

SQL

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.

SQL

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.