SQL

How To Format SQL Query Strings

intro

Let's look at how to format SQL query strings and find out why doing it in your database client makes everything easier and faster.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

SQL queries can become long and messy, making it harder for developers to understand what is going on. If you have come across a poorly formatted query at least once, you are in the right place! With the right tool, you can easily format SQL query strings.

In this article, you will take a look at SQL formatting, understanding how it works, why it is important, and how to apply it in a powerful database client.

Let's dive in!

What Does It Mean to Format an SQL Query?

Formatting a query means rewriting its SQL code to improve readability. This process is usually performed by a formatter, which follows the three-step procedure below to format SQL query strings:

  1. Analyze the input SQL code and identify its constituent parts, such as literals, keywords, space characters, and punctuation.
  2. Apply a set of rules, best practices, and conventions to rewrite each element of SQL code according to a specific standard or style guide.
  3. Reassemble the formatted elements into a structured and cohesive SQL statement.

To better understand what that means, consider the following unformatted query:

Copy
        
1 SELECT FIRST_NAME, LAST_NAME, SALARY, CASE WHEN SALARY > 10000 THEN 'High' WHEN SALARY BETWEEN 5000 AND 999 THEN 'Midlevel' ELSE 'Low' END AS "Income Level", CASE DEPARTMENT_ID WHEN 40 THEN 'Administration' WHEN 20 THEN 'Sales Related' ELSE 'Other' END AS "Special Departments" FROM EMPLOYEES;

The equivalent formatted SQL query is:

Copy
        
1 SELECT 2 FIRST_NAME, 3 LAST_NAME, 4 SALARY, 5 CASE 6 WHEN SALARY > 10000 7 THEN 'High' 8 WHEN SALARY BETWEEN 5000 AND 999 9 THEN 'Midlevel' 10 ELSE 'Low' 11 END AS "Income Level", 12 CASE DEPARTMENT_ID 13 WHEN 40 14 THEN 'Administration' 15 WHEN 20 16 THEN 'Sales Related' 17 ELSE 'Other' 18 END AS "Special Departments" 19 FROM 20 EMPLOYEES;

As you can see, the resulting query is also more maintainable and easier to understand.

In most cases, formatting an SQL query boils down to capitalizing keywords, separating different clauses clearly, and rewriting parts the code using consistent indentation.

Benefits of Formatting a Query in SQL

The top three benefits of formatting SQL queries are:

  1. Enhanced readability: Well-formatted SQL queries are much easier to read. Clear formatting helps to quickly understand the structure and logic of the query, reducing the cognitive load on developers.
  2. Improved team collaboration: Standardized query formatting ensures that all SQL code in a codebase adheres to the same style. This facilitates code reviews and collaboration, especially when the development team consists of several developers. It also helps new team members become familiar with the code more quickly and makes the queries look more professional.
  3. Easier debugging and maintenance: In a consistently formatted SQL query, it is easier to spot errors and inconsistencies at a glance. When the query structure is clear, identifying syntax errors, logical flaws or performance bottlenecks becomes quicker. That also simplifies the process of SQL query optimization.

Automatically Format SQL Queries in Your Database Client

As explored in our SQL formatter guide, there are numerous tools and approaches to formatting SQL queries. The most intuitive, logical, and time-saving solution is to format SQL query strings directly in your database client.

First, you need a database client that comes with SQL formatting capabilities, such as DbVisualizer. This tool equips you with everything you need to build, manage, and deal with state-of-the-art database technologies.

Download DbVisualizer, execute the installer, and follow the installation wizard. Once set up, launch the application and then open the SQL Commander tab:

Open an SQL Commander tab
Open an SQL Commander tab

Here, you can write any SQL query in the dozens of dialects supported by DbVisualizer. For example, write the following, messy, and unformatted SQL Server script that involves some CRUD statements:

Copy
        
1 -- Basic SELECT example, with Sub-SELECT and JOIN 2 SELECT e.LAST_NAME AS "Last Name", e.FIRST_NAME AS "First Name", d.DEPARTMENT_NAME AS "Department", e.SALARY AS "Salary", e.SALARY + e.SALARY * e.COMMISSION_PCT, e.COMMISSION_PCT * 100 || '%', ROUND(e.SALARY / ( SELECT MAX(SALARY) FROM HR.EMPLOYEES), 2) * 100 AS "Percentage of Max" FROM HR.EMPLOYEES e INNER JOIN HR.DEPARTMENTS d ON ( e.DEPARTMENT_ID = d.DEPARTMENT_ID) WHERE d.DEPARTMENT_ID IN (10, 20, 90, 210) AND e.SALARY > 3000; 3 -- JOIN example, with GROUP BY, HAVING and ORDER BY 4 SELECT COUNT(d.DEPARTMENT_NAME) AS "Departments per Location", c.COUNTRY_NAME, l.STATE_PROVINCE FROM DEPARTMENTS d INNER JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID INNER JOIN COUNTRIES c USING (COUNTRY_ID) GROUP BY c.COUNTRY_NAME, l.STATE_PROVINCE HAVING COUNT(d.DEPARTMENT_NAME) > 1 ORDER BY 2, 3, 1; 5 -- UPDATE example 6 UPDATE EMPLOYEES SET COMMISSION_PCT = 10 WHERE COMMISSION_PCT = 0 AND SALARY < 5000; 7 -- INSERT example 8 INSERT INTO EMPLOYEES ( FIRST_NAME, LAST_NAME ) VALUES ( 'Roger', 'Bjarevall' ); 9 -- DELETE example 10 DELETE FROM EMPLOYEES WHERE HIRE_DATE < to_timestamp('1900-01-10', 'RR-MM-DD'); 11 -- CREATE TABLE example 12 CREATE TABLE DEPARTMENTS ( DEPARTMENT_ID NUMBER(4) NOT NULL, DEPARTMENT_NAME VARCHAR2(30) NOT NULL, MANAGER_ID NUMBER(6), LOCATION_ID NUMBER(4), CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID), CONSTRAINT DEPT_LOC_FK FOREIGN KEY (LOCATION_ID) REFERENCES "LOCATIONS" ("LOCATION_ID"), CONSTRAINT DEPT_MGR_FK FOREIGN KEY (MANAGER_ID) REFERENCES "EMPLOYEES" ("EMPLOYEE_ID"), CONSTRAINT DEPT_NAME_NN CHECK ("DEPARTMENT_NAME" IS NOT NULL) );

This wall of SQL code will now become a well-formatted SQL query. Right-click on the code editing canvas and select the “Format SQL > Format Buffer” option to format it:

Formatting the entire query
Formatting the entire query

The resulting formatted SQL query will be as follows:

Copy
        
1 -- Basic SELECT example, with Sub-SELECT and JOIN 2 SELECT 3 e.LAST_NAME AS "Last Name", 4 e.FIRST_NAME AS "First Name", 5 d.DEPARTMENT_NAME AS "Department", 6 e.SALARY AS "Salary", 7 e.SALARY + e.SALARY * e.COMMISSION_PCT, 8 e.COMMISSION_PCT * 100 || '%', 9 ROUND(e.SALARY / 10 ( SELECT 11 MAX(SALARY) 12 FROM 13 HR.EMPLOYEES), 2) * 100 AS "Percentage of Max" 14 FROM 15 HR.EMPLOYEES e 16 INNER JOIN 17 HR.DEPARTMENTS d 18 ON 19 ( 20 e.DEPARTMENT_ID = d.DEPARTMENT_ID) 21 WHERE 22 d.DEPARTMENT_ID IN (10, 23 20, 24 90, 25 210) 26 AND e.SALARY > 3000; 27 -- JOIN example, with GROUP BY, HAVING and ORDER BY 28 SELECT 29 COUNT(d.DEPARTMENT_NAME) AS "Departments per Location", 30 c.COUNTRY_NAME, 31 l.STATE_PROVINCE 32 FROM 33 DEPARTMENTS d 34 INNER JOIN 35 LOCATIONS l 36 ON 37 d.LOCATION_ID = l.LOCATION_ID 38 INNER JOIN 39 COUNTRIES c 40 USING 41 (COUNTRY_ID) 42 GROUP BY 43 c.COUNTRY_NAME, 44 l.STATE_PROVINCE 45 HAVING 46 COUNT(d.DEPARTMENT_NAME) > 1 47 ORDER BY 48 2, 49 3, 50 1; 51 -- UPDATE example 52 UPDATE 53 EMPLOYEES 54 SET 55 COMMISSION_PCT = 10 56 WHERE 57 COMMISSION_PCT = 0 58 AND SALARY < 5000; 59 -- INSERT example 60 INSERT INTO 61 EMPLOYEES 62 ( 63 FIRST_NAME, 64 LAST_NAME 65 ) 66 VALUES 67 ( 68 'Roger', 69 'Bjarevall' 70 ); 71 -- DELETE example 72 DELETE 73 FROM 74 EMPLOYEES 75 WHERE 76 HIRE_DATE < to_timestamp('1900-01-10', 'RR-MM-DD'); 77 -- CREATE TABLE example 78 CREATE TABLE 79 DEPARTMENTS 80 ( 81 DEPARTMENT_ID NUMBER(4) NOT NULL, 82 DEPARTMENT_NAME VARCHAR2(30) NOT NULL, 83 MANAGER_ID NUMBER(6), 84 LOCATION_ID NUMBER(4), 85 CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID), 86 CONSTRAINT DEPT_LOC_FK FOREIGN KEY (LOCATION_ID) REFERENCES "LOCATIONS" ("LOCATION_ID"), 87 CONSTRAINT DEPT_MGR_FK FOREIGN KEY (MANAGER_ID) REFERENCES "EMPLOYEES" ("EMPLOYEE_ID"), 88 CONSTRAINT DEPT_NAME_NN CHECK ("DEPARTMENT_NAME" IS NOT NULL) 89 );

Much more readable, don't you find?

Note: Keep in mind that the “Format SQL” options are only available in DbVisualizer Pro. For more information, check our pricing page.

What if you want to format only a portion of the previous SQL statement? You can do that by selecting the SQL code to format and then clicking the “Format SQL > Format Buffer” option:

Formatting a portion of the query
Formatting a portion of the query

Wonderful! For more details on this feature, explore our documentation.

Conclusion

In this article, you learned what it means to format SQL query strings and how to do it. This is a useful technique for standardizing SQL code in a codebase and making it more readable and maintainable by applying standard stylistic conventions.

Here, you learned that the ideal solution to SQL query formatting is doing it in your database client. For that, you need a full-featured database client, such as DbVisualizer. This also offers many other capabilities, including query optimization, ERD-like schema generation, and more. Try it for free today!

FAQ

What are some online format SQL query tools?

Some popular online SQL formatter tools are:

What are some SQL query date format approaches?

Some SQL query date format approaches include:

  1. Using DATE_FORMAT(): MySQL function to format dates (e.g., DATE_FORMAT(date, '%Y-%m-%d')).
  2. Using TO_CHAR(): Oracle function to format dates (e.g., TO_CHAR(date, 'YYYY-MM-DD')). This function is also available in PostgreSQL.
  3. Using FORMAT(): SQL Server function to format dates (e.g., FORMAT(date, 'yyyy-MM-dd')).

What impact does the format of an SQL query have on readability?

The format of a SQL query string significantly impacts its readability. Proper formatting and spacing help to effectively grasp the structure of the query, making it easier for the reader to understand what the query does.

Is it possible to format SQL code directly in a database client?

Yes, but you need the right tool. A full-featured database client like DbVisualizer comes with SQL query formatting capabilities and many other features.

What role does indentation play in SQL query formatting?

Indentation plays a crucial role in SQL query formatting by improving readability and highlighting the query's structure and logic. It visually separates different parts of the query, such as clauses and SQL subqueries. This enables developers to quickly identify the relationships and hierarchy within the tables and columns used in the query.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Clustered vs Non-Clustered Index: Complete SQL Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2025-02-13
title

Queries and Subqueries in SQL: The Definitive Guide

author Lukas Vileikis tags DbVisualizer MySQL POSTGRESQL SQL SQL SERVER 7 min 2025-02-12
title

How to Optimize a Clustered Index Scan in SQL

author Antonello Zanini tags MySQL SQL SQL SERVER 7 min 2025-02-11
title

SQL Derived Table: Everything You Need to Know

author Antonello Zanini tags MySQL POSTGRESQL SQL SQL SERVER 9 min 2025-02-06
title

Understanding MVCC in MySQL

author Lukas Vileikis tags DbVisualizer MySQL SQL 7 min 2025-02-05
title

Postgres Create Array of Numbers: Complete Guide

author Lukas Vileikis tags POSTGRESQL SQL 7 min 2025-02-03
title

SQL Injection Cheat Sheet: SQLi 101

author Lukas Vileikis tags MySQL SECURITY SQL 10 min 2025-01-29
title

SQL PARTITION BY in PostgreSQL: A Guide to Window Functions and Data Segmentation

author Leslie S. Gyamfi tags SQL 7 min 2025-01-28
title

A Complete Guide to the ALTER TABLE DROP COLUMN Statement

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2025-01-27
title

How to Use a Recursive CTE in SQL

author Antonello Zanini tags MySQL POSTGRESQL SQL SQL SERVER 5 min 2025-01-22

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.