Custom SQL Query Guidelines

This guide provides best practices for writing well-structured SQL queries to improve readability, maintainability, and performance.

Formatting Instructions:

1. Use consistent indentation (2 or 4 spaces) for nested clauses.

2. Place each major SQL clause (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY) on a new line.

3. Write SQL keywords in uppercase for clarity.

4. Align columns, conditions, and JOIN clauses vertically when appropriate.

5. Use aliases for all column names, table names, subqueries, and join tables with the AS keyword.

6. Choose descriptive and meaningful aliases that accurately represent the data.

7. List each column on a separate line in the SELECT clause when selecting multiple columns.

8. Ensure proper spacing around operators and commas.

9. Use table aliases for all table references to improve query readability.

10. For database tenancy, replace all schema references with {{DATABASE_NAME}}.

To apply these guidelines to different SQL environments, refer to the sections below:

Redshift

Guideline 1: Use Aliases for Readability

Always use aliases for column names, table names, subqueries, and join tables using the AS keyword. Aliases improve readability and make queries more concise.

Guideline 2: Enclose Identifiers in Double Quotes (")

Always use double quotes for column, table, and subquery names to avoid conflicts with reserved keywords and case-sensitive identifiers.

Guideline 3: Reference Tables with the Database Name

Always prefix tables with the database and schema name to ensure clarity, especially in multi-schema databases.

Example Use Case

SELECT
  SUM("listing_data"."totalprice") AS "sum_of_totalprice",    -- ✔ Column Alias with Double Quotes
  "public_category"."catname" AS "catname",                   -- ✔ Column Alias with Double Quotes
  "public_event"."eventname" AS "eventname"                   -- ✔ Column Alias with Double Quotes
FROM
  "{{DATABASE_NAME}}"."category" AS "public_category"         -- ✔ Table Alias with Double Quotes and Database Reference
  INNER JOIN "{{DATABASE_NAME}}"."event" AS "public_event"    -- ✔ Join Table Alias with Double Quotes and Database Reference
    ON "public_category"."catid" = "public_event"."catid" 
  INNER JOIN (    
    SELECT
      "listing"."eventid" AS "eventid",                       -- ✔ Column Alias with Double Quotes
      "listing"."totalprice" AS "totalprice"                  -- ✔ Column Alias with Double Quotes
    FROM
      "{{DATABASE_NAME}}"."listing" AS "listing"              -- ✔ Table Alias with Double Quotes and Database Reference
  ) AS "listing_data"
    ON "public_event"."eventid" = "listing_data"."eventid"    
GROUP BY
  "public_category"."catname",
  "public_event"."eventname"

Snowflake

Guideline 1: Use Aliases for Readability

Always use aliases for column names, table names, subqueries, and join tables using the AS keyword. Aliases improve readability and make queries more concise.

Guideline 2: Enclose Identifiers in Double Quotes (")

Always use double quotes for column names to avoid conflicts with reserved keywords and case-sensitive identifiers.

Guideline 3: Reference Tables with the Database Name

Always prefix tables with the database and schema name to ensure clarity, especially in multi-schema databases.

Example Use Case

SELECT
  SUM("databrain_dev2_demo_sales"."quantity sold") AS "sum of quantity sold",  -- ✔ Column Alias with Double Quotes
  "databrain_dev2_demo_sales"."client name" AS "client name"                   -- ✔ Column Alias with Double Quotes
FROM
  "databrain_dev2"."demo_sales" AS databrain_dev2_demo_sales  -- ✔ Table Alias (No Quotes)
  INNER JOIN (
    SELECT 
      "customer id", 
      "sales representative id" 
    FROM "databrain_dev2"."customer_data"
  ) AS databrain_dev2_customer_data    -- ✔ Subquery Alias (No Quotes)
  ON "databrain_dev2_demo_sales"."client name" = "databrain_dev2_customer_data"."customer id"
GROUP BY
  "databrain_dev2_demo_sales"."client name"

BigQuery

Guideline 1: Use Aliases for Readability

Always use aliases for column names, table names, subqueries, and join tables using the AS keyword. Aliases improve readability and make queries more concise.

Guideline 2: Enclose Identifiers in Double Quotes (`)

Always use backticks for column names to avoid conflicts with reserved keywords and case-sensitive identifiers.

Guideline 3: Reference Tables with the Database Name

Always prefix tables with the database and schema name to ensure clarity, especially in multi-schema databases.

SELECT
  SUM(`databrain_dev2_demo_sales`.`quantity sold`) AS `sum of quantity sold`,  -- Column Alias with Backtiks
  `databrain_dev2_demo_sales`.`client name` AS `client name`                   -- Column Alias with Backtiks
FROM
  `databrain_dev2`.`demo_sales` AS databrain_dev2_demo_sales  -- Table Alias (NO BACKTICKS)
  INNER JOIN (
    SELECT 
      `customer id`, 
      `sales representative id` 
    FROM `databrain_dev2`.`customer_data`
  ) AS databrain_dev2_customer_data    -- Subquery Alias (NO BACKTICKS)
  ON `databrain_dev2_demo_sales`.`client name` = `databrain_dev2_customer_data`.`customer id`
GROUP BY
  `databrain_dev2_demo_sales`.`client name`

MySQL

Guideline 1: Use clear and consistent formatting

Guideline 2: Write precise queries

Guideline 3: Utilize column aliases

Example Use Case

SELECT 
    c.customer_id,
    CONCAT(c.last_name, ', ', c.first_name) AS customer_name,  -- ✔ Using column alias
    a.phone,
    f.title AS rented_film  -- ✔ Using column alias
FROM 
    rental r  
    INNER JOIN customer c ON r.customer_id = c.customer_id  -- ✔ Clear formatting with JOIN on new line
    INNER JOIN address a ON c.address_id = a.address_id     -- ✔ Consistent indentation
    INNER JOIN inventory i ON r.inventory_id = i.inventory_id
    INNER JOIN film f ON i.film_id = f.film_id
WHERE 
    r.return_date IS NULL  -- ✔ Precise filtering
    AND r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE()  -- ✔ Specific condition
ORDER BY 
    c.last_name, c.first_name  -- ✔ Major clause on new line
LIMIT 5;  -- ✔ Precise result set size

PostgreSQL

Guideline 1: Use Aliases for Readability

Always use aliases for column names, table names, subqueries, and join tables using the AS keyword. Aliases improve readability and make queries more concise.

Guideline 2: Enclose Identifiers in Double Quotes (")

Always use double quotes for column, table, and subquery names to avoid conflicts with reserved keywords and case-sensitive identifiers.

Guideline 3: Reference Tables with the Database Name

Always prefix tables with the database and schema name to ensure clarity, especially in multi-schema databases.

Example Use Case

SELECT
  COUNT("public_posts"."user_id") AS "count of user_id",  -- Column Alias with Double Quotes
  "public_users"."username" AS "username"                
FROM
  "public"."users" AS "public_users"                      -- Table Alias with Double Quotes & Database Reference
  INNER JOIN ( 
    SELECT 
      "user_id", 
      "id" AS "post_id" 
    FROM "public"."posts"
  ) AS "public_posts"                                     -- Subquery Alias with Double Quotes
  ON "public_users"."id" = "public_posts"."user_id"       
GROUP BY
  "public_users"."username"

MongoDB

Guideline 1: Use proper syntax for MongoDB collections and fields

When querying MongoDB with SQL, wrap collection names and field names in square brackets to avoid syntax errors.

Guideline 2: Utilize projections to return only necessary data

This improves query performance by limiting the amount of data transferred and processed.

Guideline 3: Leverage indexing for frequently queried fields

Create indexes on commonly searched fields to significantly enhance query performance

SELECT [first_name], [last_name], [address.city], [transactions]
FROM [Customers]
WHERE [author_name] = 'John Doe'
ORDER BY [timestamp] DESC
LIMIT 10

Elasticsearch

Guideline 1: Ensure proper indexing and mapping

Correctly index and map your data to optimize query efficiency. SQL utilizes Elasticsearch's indexing and mapping functionalities, so the effectiveness of your queries is directly influenced by accurate data organization.

Guideline 2: Utilize projections to return only necessary data

This improves query performance by limiting the amount of data transferred and processed. Use SQL's SELECT statement to specify only the required fields.

Guideline 3: Leverage Elasticsearch SQL optimizations

Take advantage of query rewriting, caching, and parallel execution to enhance the efficiency of your queries.

SELECT [first_name], [last_name], [address.city]
FROM [customers]
WHERE [created_at] BETWEEN '2025-01-01' AND '2025-03-20'
  AND [status] = 'active'
ORDER BY [last_login] DESC
LIMIT 10

Databricks

Guideline 1: Use specific column names instead of SELECT *

This reduces the amount of data processed and transferred, speeding up queries.

Guideline 2: Utilize WHERE clauses to filter rows early

This reduces the number of rows processed in later stages of the query, improving performance.

Guideline 3: Implement proper indexing

Create indexes on frequently queried columns to drastically improve query performance.

SELECT o.order_id, c.customer_name, o.order_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-01-01' AND o.order_date <= '2025-03-20'
  AND c.region = 'North America'
ORDER BY o.order_date DESC
LIMIT 10

ClickHouse

MSSQL

Guideline 1: Use Aliases for Readability

Always use aliases for column names, table names, subqueries, and join tables using the AS keyword. Aliases improve readability and make queries more concise.

Guideline 2: Enclose Identifiers in Double Quotes (")

Always use double quotes for column, table, and subquery names to avoid conflicts with reserved keywords and case-sensitive identifiers.

Guideline 3: Reference Tables with the Database Name

Always prefix tables with the database and schema name to ensure clarity, especially in multi-schema databases.

Example Use Case

SELECT
  TOP 100 "dbn_sql_table"."name" AS "name",
  "dbn_sql_table"."available" AS "available"
FROM
  (
    SELECT
      TOP 100 "dbo_person"."name" AS "name", -- ✔ Column Alias with Double Quotes        
      "dbo_pizza_topping"."available" AS "available" -- ✔ Column Alias with Double Quotes
    FROM
      "dbo"."person" AS "dbo_person" -- ✔ Table Alias with Double Quotes & Database Reference 
      INNER JOIN (
        SELECT
          "id",
          "available"
        FROM
          "dbo"."pizza_topping"
      ) AS "dbo_pizza_topping" -- Subquery Alias
      ON "dbo_person"."person_id" = "dbo_pizza_topping"."id"
  ) AS "dbn_sql_table"

AwsS3

Guideline 1: Use Aliases for Readability

Always use aliases for column names, table names, subqueries, and join tables using the AS keyword. Aliases improve readability and make queries more concise.

Guideline 2: Enclose Identifiers in Double Quotes (")

Always use double quotes for column, table, and subquery names to avoid conflicts with reserved keywords and case-sensitive identifiers.

Guideline 3: Reference Tables with the Database Name

Always prefix tables with the database and schema name to ensure clarity, especially in multi-schema databases.

Example Use Case

SELECT
  "main_orders"."order_id" AS "Order ID",        -- ✔ Column Alias & Double Quotes
  "main_customers"."first_name" AS "First Name", -- ✔ Column Alias & Double Quotes
  "main_customers"."last_name" AS "Last Name",   -- ✔ Column Alias & Double Quotes
  "main_coffees"."name" AS "Coffee Name",        -- ✔ Column Alias & Double Quotes
  "main_orders"."quantity" AS "Quantity"         -- ✔ Column Alias & Double Quotes
FROM 
  "main"."orders" AS "main_orders"                -- ✔ Table Alias, Database Reference & Double Quotes
  JOIN "main"."customers" AS "main_customers"     -- ✔ Join Table Alias, Database Reference & Double Quotes
  ON "main_orders"."customer_id" = "main_customers"."customer_id"
  JOIN "main"."coffees" AS "main_coffees"         -- ✔ Join Table Alias, Database Reference & Double Quotes
  ON "main_orders"."coffee_id" = "main_coffees"."coffee_id"
  JOIN (
    SELECT
      "main_orders"."customer_id" AS "customer_id",                              -- ✔ Column Alias & Double Quotes 
      SUM("main_orders"."quantity" * "main_coffees"."price") AS "Amount Spent"   -- ✔ Column Alias & Double Quotes
    FROM
      "main"."orders" AS "main_orders"        -- ✔ Table Alias, Database Reference & Double Quotes
      JOIN "main"."coffees" AS "main_coffees" -- ✔ Join Table Alias, Database Reference & Double Quotes
      ON "main_orders"."coffee_id" = "main_coffees"."coffee_id"
    GROUP BY
      "main_orders"."customer_id"
  ) AS "sq" -- ✔ Subquery Alias & Double Quotes
  ON "main_orders"."customer_id" = "sq"."customer_id"

Firebolt

Guideline 1: Use Aliases for Readability

Always use aliases for column names, table names, subqueries, and join tables using the AS keyword. Aliases improve readability and make queries more concise.

Guideline 2: Enclose Identifiers in Double Quotes (")

Always use double quotes for column, table, and subquery names to avoid conflicts with reserved keywords and case-sensitive identifiers.

Guideline 3: Reference Tables with the Database Name

Always prefix tables with the database and schema name to ensure clarity, especially in multi-schema databases.

Example Use Case

SELECT
  SUM("public_levels"."maxpoints") AS "sum of maxpoints", -- ✔ Column Alias with Double Quotes
  "public_games"."title" AS "title",                      -- ✔ Column Alias with Double Quotes
  COUNT("public_rankings"."playerid") AS "num_players"    -- ✔ Column Alias with Double Quotes
FROM
  "public"."games" AS "public_games"                      -- ✔ Table Alias with Double Quotes & Database Reference
  INNER JOIN "public"."levels" AS "public_levels"         -- ✔ Join Table Alias with Double Quotes & Database Reference
    ON "public_games"."gameid" = "public_levels"."gameid"
  INNER JOIN (
    SELECT DISTINCT "gameid", "playerid"
    FROM "public"."rankings"
    WHERE "totalscore" > 0 
  ) AS "public_rankings"    -- Subquery with Alias
    ON "public_games"."gameid" = "public_rankings"."gameid" 
GROUP BY
  "public_games"."title"

SingleStore

Guideline 1: Use Aliases for Readability

Always use aliases for column names, table names, subqueries, and join tables using the AS keyword. Aliases improve readability and make queries more concise.

Guideline 2: Enclose Identifiers in Backticks (`)

Database names, table names, and column names should be enclosed in backticks (`).

Guideline 3: Reference Tables with the Database Name

Always prefix tables with the database and schema name to ensure clarity, especially in multi-schema databases.

SELECT
  SUM(`public_levels`.`maxpoints`) AS `sum_of_maxpoints`, -- ✔ Column Alias with Backticks
  `public_games`.`title` AS `title`,                      -- ✔ Column Alias with Backticks
  COUNT(`public_rankings`.`playerid`) AS `num_players`    -- ✔ Column Alias with Backticks
FROM
  `public`.`games` AS `public_games`                      -- ✔ Table Alias with Backticks & Database Reference
  INNER JOIN `public`.`levels` AS `public_levels`         -- ✔ Join Table Alias with Backticks & Database Reference
    ON `public_games`.`gameid` = `public_levels`.`gameid`
  INNER JOIN (
    SELECT DISTINCT `gameid`, `playerid`
    FROM `public`.`rankings`
    WHERE `totalscore` > 0 
  ) AS `public_rankings`    -- Subquery with Alias
    ON `public_games`.`gameid` = `public_rankings`.`gameid` 
GROUP BY
  `public_games`.`title`;

To automatically correct and format your SQL query, click on the magic stick icon next to the "Format Query" button.

This ensures proper syntax, indentation, and readability.