How to Write a Comment in SQL

Writing a sql comment is essential for adding explanations and disabling code without removing it. Comments help improve the readability and maintainability of your SQL queries.

How to Comment in SQL

In SQL, there are two primary types of comments: single-line and multi-line (block) comments.

Single-Line Comments

Single-line comments start with two hyphens and a space. This is the standard syntax and works across all SQL databases.

-- This is a single-line comment
SELECT * FROM users;

In MySQL and MariaDB, you can also use the hash symbol (#) for single-line comments.

# This is a single-line comment in MySQL
SELECT * FROM users;

Multi-Line (Block) Comments

Multi-line comments are enclosed within /* ... */. This type of comment is supported by major databases like PostgreSQL, MySQL, SQL Server, Oracle, and SQLite.

/* This is a
   multi-line comment */
SELECT * FROM users;

You can also use block comments to comment out a line of code to disable it.

SELECT * FROM users
WHERE id = 1;
/* AND status = 'active'; */

Inline Block Comments

Block comments can be used inline within a statement to add explanations.

SELECT name, /* This is an inline comment */ age FROM users;

Table of Comment Syntax

It’s important to note that the -- sequence is also famously used in SQL-injection payloads to comment out the rest of a query. This is a good reason to use parameterized queries to prevent such attacks.

For more on structuring your SQL code, check out our SQL formatter and SQL formatting style guide.

Comments in migrations and stored procedures

When working with database migrations and stored procedures, SQL comments serve a crucial purpose beyond just annotating code. In migrations, comments help document the changes being made to the database schema. This is particularly useful for team collaboration and for future reference when revisiting past changes. Clear comments in migrations can explain the rationale behind structural changes, making it easier to understand the evolution of the database over time.

In stored procedures, comments are essential for maintaining readability and understanding the functionality of complex SQL logic. They provide insights into the purpose of specific code blocks, the expected input and output, and any assumptions or constraints. This is invaluable for debugging and for onboarding new developers who need to quickly grasp the stored procedure’s functionality.

Nested block comments

Standard SQL does not guarantee that nested block comments using the /* */ syntax will work as intended. The behavior of nested block comments varies across different database systems. Some databases, like MySQL, allow nested block comments, while others, such as PostgreSQL, do not. This inconsistency means that relying on nested block comments can lead to unexpected results or syntax errors when moving code between different database environments.

To ensure portability and avoid issues, it is best to avoid nesting block comments. Instead, use single-line comments (--) for additional annotations within a block comment. This approach ensures that your comments are interpreted correctly across all SQL databases.

Optimizer hints that look like comments

In Oracle and MySQL, special optimizer hints are often written inside comments that resemble standard block comments. These hints are not ignored by the SQL parser; instead, they provide instructions to the query optimizer on how to execute the query. For example, the hint /*+ HINT_NAME(arg) */ is used in Oracle to suggest a specific optimization strategy.

It is important to recognize that not every block comment is treated as a comment by the database. In the case of optimizer hints, the database actively parses the content within the comment to apply the specified optimization. This dual use of the comment syntax can be confusing, so always be mindful of the context when using comments that resemble optimizer hints.

A quick recap across databases

Tips for readable team SQL

Writing clear and maintainable SQL is crucial for effective collaboration. Here are some tips to make your SQL comments more useful for your team:

• Comment the intent, not the syntax: Instead of explaining what the code is doing, explain why it is doing it. This provides context and helps others understand your thought process.
• Keep comments close to the clause they explain: Place comments near the SQL clause or section they are referring to. This makes it easier to associate the comment with the relevant code.
• Remove commented-out dead code before committing: Dead code can confuse team members and clutter the codebase. Before committing your changes, remove any commented-out code that is no longer needed.

By following these guidelines, you can ensure that your SQL code is well-documented and easy for your team to understand and maintain.

Using QuikConsole’s SQL Comment Tool

You can easily write and manage SQL comments using QuikConsole’s free, browser-based SQL Comment Tool. This tool runs in your browser and ensures that nothing you paste ever leaves your computer, maintaining your privacy.

Good uses for comments

Comments in SQL can greatly enhance the maintainability and clarity of your queries. Here are some specific scenarios where comments are particularly useful:

• Explaining non-obvious joins: When you use complex join conditions, especially those that are not immediately apparent, a comment can clarify why the join is necessary and what it achieves.
• Documenting a tricky WHERE clause: If your query includes a complex or unintuitive WHERE clause, a comment can explain the logic behind it. This is particularly helpful for future maintenance or for other developers who may work with your code.
• Leaving TODOs: Comments can be used to flag parts of the query that need further attention or improvement. This is useful for tracking tasks and ensuring that important changes are not overlooked.
• Temporarily disabling a line while debugging: When troubleshooting, you may want to comment out a line of code to see how it affects the query’s output. This allows you to test changes without permanently deleting code.

Comments and readability

While comments are essential for explaining complex logic, they should not be used as a substitute for clear and readable code. Here are some tips to ensure that your comments enhance, rather than detract from, the readability of your SQL queries:

• Format first, then comment: Before adding comments, ensure that your SQL is well-formatted. Proper indentation, spacing, and consistent naming conventions can make your code more understandable. Once your code is well-structured, add comments to explain aspects that the formatting cannot convey.
• For more detailed guidance on writing readable SQL, refer to the SQL formatting style guide.

Frequently asked questions

What is the syntax for a single-line comment in SQL?

A single-line comment starts with two hyphens and a space: — comment. In MySQL and MariaDB, you can also use # comment.

How do I write a multi-line comment in SQL?

Multi-line comments are enclosed within /* … */. For example: /* This is a multi-line comment */.

Can I use block comments to disable a line of code?

Yes, you can use block comments to comment out a line of code. For example: /* AND status = ‘active’; */.

Are SQL comments supported by all databases?

Single-line comments using — and block comments using /* … */ are supported by all major SQL databases. The # syntax is specific to MySQL and MariaDB.

Why should I use parameterized queries instead of comments in SQL?

Using parameterized queries helps prevent SQL-injection attacks, as the — sequence is often used in injection payloads to comment out the rest of the query.