How to Comment in SQL?

Commenting in SQL is primarily achieved using specific syntax that allows you to add explanatory notes within your code without affecting its execution. These comments enhance readability, aid in debugging, and serve as documentation for both yourself and other developers.

Understanding SQL Comment Syntax

SQL supports different types of comments, primarily distinguished by whether they span a single line or multiple lines.

Single-Line Comments (-- and #)

Single-line comments are used for brief notes or to comment out a single line of code.

Double Hyphen (--)

The most widely supported single-line comment syntax across various SQL database systems is the double hyphen (--). Anything following -- to the end of the line will be ignored by the SQL interpreter.

Example:

SELECT
    product_name, -- Selects the name of the product
    price           -- Retrieves the product's price
FROM
    products
WHERE
    category = 'Electronics'; -- Filters for electronic products

Common Database Support:

• SQL Server
• MySQL
• PostgreSQL
• Oracle
• DB2
• And many other ANSI SQL-compliant databases.

Hash/Pound Sign (#)

Some database systems, most notably MySQL, also support the hash or pound sign (#) for single-line comments. Similar to --, everything after # on that line is treated as a comment.

Example (MySQL-specific):

SELECT
    first_name,
    last_name    # Selects the first and last name of a customer
FROM
    customers
WHERE
    customer_id = 101; # Filters for a specific customer ID

Common Database Support:

• MySQL

Multi-Line Comments (/* ... */)

Multi-line comments are ideal for longer explanations, block comments, or to comment out larger sections of SQL code. This syntax is also known as a C-style comment.

To create a multi-line comment, you begin the comment with a slash and an asterisk (/*). You can then proceed with the text of the comment, which can span multiple lines. End the comment with an asterisk and a slash (*/).

Example:

/*
This query retrieves details for all active users
who have made at least one order in the last 30 days.
It joins the 'users' and 'orders' tables.
*/
SELECT
    u.user_id,
    u.username,
    u.email,
    COUNT(o.order_id) AS total_orders
FROM
    users u
JOIN
    orders o ON u.user_id = o.user_id
WHERE
    u.is_active = TRUE
    AND o.order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY
    u.user_id, u.username, u.email
HAVING
    COUNT(o.order_id) > 0;

Common Database Support:

• SQL Server
• MySQL
• PostgreSQL
• Oracle
• DB2
• And most other SQL database systems.

Why Comment Your SQL Code?

Effective commenting brings numerous benefits to SQL development:

• Improved Readability: Explains complex logic, making it easier for anyone (including your future self) to understand the code's purpose and functionality.
• Debugging Assistance: Temporarily disable sections of code by commenting them out, which is invaluable during troubleshooting.
• Documentation: Serves as inline documentation, detailing the 'why' behind certain design choices, assumptions, or business rules implemented in the query.
• Collaboration: Facilitates teamwork by providing context and explanations to other developers working on the same codebase.
• Maintainability: Reduces the time and effort required to maintain or modify existing queries by clearly outlining their structure and intent.

Best Practices for SQL Comments

To maximize the effectiveness of your SQL comments, consider these best practices:

• Be Meaningful: Comments should explain why something is done, not just what is being done (which should be evident from the code itself).
• Keep Them Updated: Outdated comments can be misleading. Ensure comments are revised whenever the code they describe changes.
• Avoid Over-Commenting: Don't comment on every line or obvious piece of code. Focus on complex logic, non-obvious choices, or critical business rules.
• Use for Complex Logic: Use multi-line comments to explain intricate joins, complex WHERE clauses, or the purpose of stored procedures and functions.
• Standardize: If working in a team, agree on a consistent commenting style and stick to it.

SQL Comment Types at a Glance

Comment Type	Syntax	Description	Common Database Support
Single-Line	-- text	Comments out the rest of the line.	SQL Server, MySQL, PostgreSQL, Oracle, DB2
Single-Line	# text	Comments out the rest of the line.	MySQL
Multi-Line/Block	/* text */	Comments out all text between /* and */, spanning lines.	SQL Server, MySQL, PostgreSQL, Oracle, DB2, and others