SQL - Comments (Single-line and Multi-line)

Key Insights

SQL comments are non-executable text used for documentation, code explanation, and temporarily disabling queries during development and debugging
Single-line comments use -- (double dash) and are ideal for brief annotations, while multi-line comments use /* */ for longer explanations or commenting out code blocks
Strategic commenting improves code maintainability, enables team collaboration, and serves as inline documentation without affecting query performance or execution

Understanding SQL Comment Syntax

SQL supports two distinct comment styles inherited from different programming language traditions. Single-line comments begin with two consecutive hyphens (--) and extend to the end of the line. Multi-line comments start with /* and end with */, allowing comments to span multiple lines or exist inline within statements.

-- This is a single-line comment
SELECT customer_id, order_total FROM orders;

/* This is a multi-line comment
   that spans multiple lines */
SELECT product_name, price FROM products;

SELECT order_id /* inline comment */, customer_id FROM orders;

All major database systems including PostgreSQL, MySQL, SQL Server, Oracle, and SQLite support both comment styles, making them portable across platforms.

Single-Line Comments for Quick Annotations

Single-line comments excel at providing brief context for individual statements or explaining specific column selections. They’re particularly useful for annotating business logic or clarifying non-obvious query decisions.

-- Retrieve active customers who made purchases in the last 90 days
SELECT 
    c.customer_id,
    c.email,
    c.last_purchase_date
FROM customers c
WHERE c.status = 'active'  -- Exclude suspended accounts
    AND c.last_purchase_date >= CURRENT_DATE - INTERVAL '90 days';

-- Calculate revenue metrics for Q4 2024
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(order_total) AS monthly_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date BETWEEN '2024-10-01' AND '2024-12-31'
GROUP BY DATE_TRUNC('month', order_date);

Single-line comments are also effective for temporarily disabling specific WHERE clause conditions during testing without deleting the code.

SELECT * 
FROM products
WHERE category = 'electronics'
--    AND price > 500  -- Temporarily disabled for testing
    AND in_stock = true;

Multi-Line Comments for Complex Documentation

Multi-line comments provide space for detailed explanations, query metadata, or comprehensive documentation blocks. They’re ideal for describing complex business rules, explaining optimization decisions, or providing usage instructions.

/*
 * Customer Lifetime Value Calculation
 * 
 * Purpose: Calculate CLV for customers acquired in 2024
 * Dependencies: orders, customers, product_categories tables
 * Performance: Uses indexed customer_id and order_date columns
 * Last Modified: 2024-01-15
 * Author: Data Analytics Team
 */
SELECT 
    c.customer_id,
    c.acquisition_date,
    COUNT(o.order_id) AS total_orders,
    SUM(o.order_total) AS lifetime_value,
    AVG(o.order_total) AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.acquisition_date >= '2024-01-01'
GROUP BY c.customer_id, c.acquisition_date
HAVING SUM(o.order_total) > 1000;

Multi-line comments can also document complex JOIN logic or explain why certain approaches were chosen over alternatives.

SELECT 
    o.order_id,
    o.order_date,
    c.customer_name,
    p.product_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
/*
 * Using LEFT JOIN for products to include orders where
 * products may have been deleted from the catalog.
 * Alternative INNER JOIN would exclude orphaned orders.
 */
LEFT JOIN products p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';

Commenting Out Code Blocks During Development

Comments serve as a powerful debugging tool by allowing developers to selectively disable portions of queries without deletion. This technique is invaluable when testing query variations or isolating problematic clauses.

SELECT 
    product_id,
    product_name,
    price,
    inventory_count
FROM products
WHERE category = 'electronics'
/*
-- Testing without these filters
    AND price BETWEEN 100 AND 500
    AND inventory_count > 0
    AND manufacturer = 'TechCorp'
*/
ORDER BY price DESC;

For stored procedures or complex scripts, commenting out sections helps isolate logic during troubleshooting.

CREATE OR REPLACE PROCEDURE process_monthly_orders()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Step 1: Archive old orders
    INSERT INTO orders_archive 
    SELECT * FROM orders 
    WHERE order_date < CURRENT_DATE - INTERVAL '1 year';
    
    /*
    -- Step 2: Temporarily disabled - needs review
    DELETE FROM orders 
    WHERE order_date < CURRENT_DATE - INTERVAL '1 year';
    */
    
    -- Step 3: Update statistics
    ANALYZE orders;
    
    RAISE NOTICE 'Monthly order processing completed';
END;
$$;

Strategic Commenting for Maintainability

Effective commenting balances documentation with code clarity. Over-commenting obvious operations creates noise, while under-commenting complex logic creates maintenance challenges.

Good commenting practices:

-- Calculate rolling 7-day average sales
WITH daily_sales AS (
    SELECT 
        order_date,
        SUM(order_total) AS daily_total
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
    GROUP BY order_date
)
SELECT 
    order_date,
    daily_total,
    AVG(daily_total) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_avg
FROM daily_sales;

Avoid obvious comments:

-- Bad: Over-commenting obvious operations
SELECT customer_id  -- Select customer ID
FROM customers      -- From customers table
WHERE status = 'active';  -- Where status equals active

Comments in Stored Procedures and Functions

Comments become especially critical in stored procedures where business logic complexity increases. They help future developers understand parameter purposes, processing steps, and error handling logic.

CREATE FUNCTION calculate_discount(
    order_total DECIMAL,
    customer_tier VARCHAR(20)
) RETURNS DECIMAL
LANGUAGE plpgsql
AS $$
DECLARE
    discount_rate DECIMAL;
BEGIN
    /*
     * Discount calculation based on customer tier
     * Bronze: 5%, Silver: 10%, Gold: 15%, Platinum: 20%
     * Applies only to orders over $100
     */
    
    IF order_total < 100 THEN
        RETURN 0;  -- No discount for orders under $100
    END IF;
    
    discount_rate := CASE customer_tier
        WHEN 'BRONZE' THEN 0.05
        WHEN 'SILVER' THEN 0.10
        WHEN 'GOLD' THEN 0.15
        WHEN 'PLATINUM' THEN 0.20
        ELSE 0  -- Default: no discount for unknown tiers
    END;
    
    RETURN order_total * discount_rate;
END;
$$;

Performance Considerations and Best Practices

Comments are stripped during query parsing and have zero runtime performance impact. However, excessive commenting in large SQL scripts can affect readability and file size.

Best practices:

Use comments to explain “why,” not “what” - The code shows what it does; comments should explain reasoning
Keep comments current - Outdated comments are worse than no comments
Comment complex JOIN conditions - Explain relationship logic that isn’t immediately obvious
Document assumptions - Note expected data states or business rule assumptions
Use consistent formatting - Establish team standards for comment style and placement

-- Correct: Explains reasoning
SELECT * FROM orders 
WHERE status = 'pending' 
    AND created_at < CURRENT_TIMESTAMP - INTERVAL '24 hours';
-- Orders pending over 24 hours require manual review per policy #2847

-- Incorrect: States the obvious
SELECT * FROM orders;  -- This selects all columns from orders table

SQL comments are essential tools for creating maintainable, collaborative database code. Use them strategically to document complex logic, assist debugging, and provide context that improves long-term code quality.