Claude for SQL Query Generation and Review

1. Why SQL query generation with Claude needs its own approach

Claude can produce syntactically correct SQL almost every time, that is not the real problem. The challenge with SQL query generation is that a language model does not know the actual database schema, the cardinalities between tables or the indexing unless you tell it. A query can be syntactically perfect and still trigger a full table scan on a million rows because the model was not aware that a column carries no index. This is exactly where it is decided whether Claude acts as a time saver or as a source of risk.

The second aspect concerns SQL review: existing queries that have grown over years often contain implicit assumptions nobody documents anymore. Claude does not read such queries with operational blindness, instead it asks questions a human stops asking after years of familiarity: why this LEFT JOIN when NULL values are never expected anywhere? Why no index on the filter column? This article shows how to use Claude productively both for generating new queries and for reviewing existing SQL statements, without blind trust.

2. From schema to query: providing the right context

The quality of a generated query depends almost entirely on the quality of the schema you supply. Instead of only naming a table for Claude, you should paste the CREATE TABLE statements with all columns, data types, primary keys, foreign keys and existing indexes. Without this information Claude guesses column names and produces queries that look plausible but rest on wrong assumptions. For SQL query generation in Claude Code it pays off to export the schema into a file once and reference it in the prompt, instead of retyping it for every request.

A second important piece of context is the approximate row count per table. A query that is sensible for 500 rows may need a completely different execution plan at 50 million rows. Claude reacts noticeably differently to this information: for large tables the model tends to suggest indexes, LIMIT clauses or pagination, for small reference tables it skips unnecessary optimization. Anyone working repeatedly with the same schema should store this context in a project level CLAUDE.md so it does not have to be rewritten every time.

-- Schema context for Claude: paste full DDL, not just table names
CREATE TABLE orders (
  order_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending', 'processing', 'shipped', 'cancelled') NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_customer (customer_id),
  INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB;
-- Approximate row count: 4.2 million rows, growing ~30k/day

CREATE TABLE order_items (
  item_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  sku VARCHAR(64) NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  INDEX idx_sku (sku)
) ENGINE=InnoDB;
-- Approximate row count: 11 million rows

-- Prompt: "Generate a query returning top 20 customers by revenue
-- in the last 90 days, using the schema and row counts above."

3. Generating complex JOINs and aggregations

As soon as more than two tables are involved, hand writing JOINs becomes error prone. Cardinality mistakes, duplicate rows from a missing GROUP BY, or misplaced WHERE conditions combined with LEFT JOINs are the most common sources of errors. For SQL query generation with Claude it pays off to describe the desired result in a single sentence instead of dictating the JOIN logic yourself. Claude then chooses INNER JOIN, LEFT JOIN or subqueries depending on the described requirement and explains the choice when asked.

Window functions like ROW_NUMBER(), RANK() or LAG() are another area where Claude demonstrably helps, because the syntax is documented but rarely intuitive. For tasks like "the last three orders per customer" or "percentage change versus the previous month" Claude delivers ready made window function constructs that would otherwise require a lookup. It remains important that every generated aggregation is checked against a known test result before it feeds into a report.

-- Generated by Claude from schema above + natural language request:
-- "Top 20 customers by revenue in the last 90 days, with order count"
SELECT
  c.customer_id,
  c.email,
  COUNT(DISTINCT o.order_id) AS order_count,
  SUM(oi.quantity * oi.unit_price) AS total_revenue,
  ROUND(SUM(oi.quantity * oi.unit_price) / COUNT(DISTINCT o.order_id), 2) AS avg_order_value
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status != 'cancelled'
  AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY c.customer_id, c.email
ORDER BY total_revenue DESC
LIMIT 20;

-- Follow-up prompt: "Add month-over-month revenue change per customer
-- using a window function, without a second query"
SELECT
  customer_id,
  revenue_month,
  monthly_revenue,
  LAG(monthly_revenue) OVER (PARTITION BY customer_id ORDER BY revenue_month) AS prev_month,
  ROUND(
    (monthly_revenue - LAG(monthly_revenue) OVER (PARTITION BY customer_id ORDER BY revenue_month))
    / NULLIF(LAG(monthly_revenue) OVER (PARTITION BY customer_id ORDER BY revenue_month), 0) * 100, 1
  ) AS pct_change
FROM customer_monthly_revenue;

4. Query review: Claude as a second pair of eyes before deploy

Besides generation, reviewing existing queries is the second major use case for SQL review. Instead of only checking a finished query for syntactic correctness, a targeted review prompt pays off: paste the query together with the schema and explicitly ask Claude for an assessment of performance, readability and correctness of the result set. Claude reliably recognizes patterns such as implicit type casts in WHERE clauses that make an index unusable, or SELECT * in production code where only three columns are needed.

Review is especially valuable for legacy queries extended by multiple developers over years. Claude frequently finds redundant subqueries that could be replaced by a single JOIN, or conditions that mutually exclude each other and therefore never fire. The review process works best iteratively: request a general assessment first, then ask targeted follow up questions about individual findings, instead of having everything corrected in a single step.

-- Query submitted for review, with schema context attached
SELECT * FROM orders o
WHERE o.customer_id IN (
  SELECT customer_id FROM customers WHERE country = 'DE'
)
AND CAST(o.status AS CHAR) = 'shipped'
ORDER BY o.created_at DESC;

-- Claude's review findings, applied:
-- 1. SELECT * fetches unused columns — list only what the caller needs
-- 2. CAST on an ENUM column disables the idx_status_created index
-- 3. Subquery can become a JOIN, often faster with the optimizer used here
SELECT o.order_id, o.total_amount, o.created_at
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id
WHERE c.country = 'DE'
  AND o.status = 'shipped'
ORDER BY o.created_at DESC;

5. Performance pitfalls: reading EXPLAIN together with Claude

An EXPLAIN or EXPLAIN ANALYZE output is often hard to interpret for developers without a DBA background. Claude translates the output into understandable statements: which join type was chosen, how many rows were actually examined, where a temporary filesort occurred. The practical workflow: run the query, copy the EXPLAIN output, hand it to Claude together with the query and ask specifically about the most expensive step in the execution plan.

This approach is especially useful when a query is fast in the development environment with little test data but suddenly needs seconds instead of milliseconds in production with millions of rows. Claude can propose concrete countermeasures based on the EXPLAIN plan, such as a composite index, a reformulation of the WHERE clause, or splitting a query into two simpler steps. It is important to actually test any suggested index before adopting it into the production migration, because extra indexes also cost time on every INSERT and UPDATE.

# Practical EXPLAIN workflow with Claude Code
mysql -e "EXPLAIN ANALYZE SELECT * FROM orders
  WHERE status = 'pending' AND created_at > '2026-01-01'\G" > explain.txt

# Prompt: "Here is the query and the EXPLAIN ANALYZE output.
# Which step is the most expensive, and what index would help?"
cat explain.txt
# -> rows=4200000 examined, type=ALL, Extra: Using where; Using filesort
# Claude's suggestion: composite index on (status, created_at)
# reduces the scan to an index range scan (type=range)

# Verify before applying to production
mysql -e "CREATE INDEX idx_status_created ON orders(status, created_at);"
mysql -e "EXPLAIN ANALYZE SELECT * FROM orders
  WHERE status = 'pending' AND created_at > '2026-01-01'\G"
# -> rows=1800, type=range, Extra: Using index condition

6. Safe query generation: injection and parametrization

When Claude generates not just plain SQL but the connection to an application language, the risk of SQL injection appears if user input is concatenated directly into query strings. Claude sometimes leans toward simple string interpolation in short example snippets if the prompt names no security requirement. The reliable countermeasure: explicitly request "prepared statements with bound parameters" in the prompt, then Claude consistently generates parametrized code instead of string concatenation.

For SQL query generation in PHP projects, PDO with bound parameters is the standard that should be anchored in the project's CLAUDE.md, so every later request automatically respects this rule. An additional security check in the review step: ask Claude to explicitly check every generated database connection for injection susceptibility before the code is merged. This does not replace automated security scanners, but it sensibly complements them as a fast first check.

<?php
declare(strict_types=1);

// WRONG: string concatenation is vulnerable to SQL injection
// $sql = "SELECT * FROM orders WHERE customer_id = " . $customerId;

// RIGHT: prepared statement with bound parameters, as requested from Claude
final class OrderRepository
{
    public function __construct(private readonly \PDO $connection)
    {
    }

    /**
     * Fetches recent orders for a given customer using a bound parameter.
     *
     * @param int $customerId Customer identifier from validated request data.
     * @return array<int, array<string, mixed>> Rows matching the criteria.
     */
    public function findRecentByCustomer(int $customerId): array
    {
        $statement = $this->connection->prepare(
            'SELECT order_id, status, total_amount, created_at
             FROM orders
             WHERE customer_id = :customerId
             ORDER BY created_at DESC
             LIMIT 20'
        );
        $statement->execute(['customerId' => $customerId]);

        return $statement->fetchAll(\PDO::FETCH_ASSOC);
    }
}

7. Using Claude for Magento and Hyvä database work

Magento projects add an extra layer of complexity: EAV tables, flat indexes and Magento's own Select query builder API instead of plain SQL. Claude generates usable code for both, provided the prompt clarifies whether raw SQL for a reporting script or a ResourceModel using Magento's query builder is needed. For EAV queries, specifying the concrete attribute code and entity type ID is decisive, otherwise Claude produces generic but incorrect attribute joins.

For performance analysis on Magento databases the same EXPLAIN workflow described in section five applies, with the difference that many Magento tables like catalog_product_entity_int or sales_order_grid have special indexing patterns that should be given to Claude as context. A common use case in Hyvä development is debugging slow category or search pages, where the generated query must first be checked against the actual EXPLAIN output of the Magento instance before it is adopted into a custom module.

8. Iterative workflow from raw request to finished query

The most productive approach for SQL query generation is rarely "ask once, copy done". Instead, the best query emerges iteratively: generate a first version, run it against real test data, name deviations from the expected result, ask Claude for a corrected version. For complex reports this cycle rarely takes more than three to four rounds and is still noticeably faster than writing and debugging the same query manually from scratch.

A proven pattern: after the final query, explicitly ask for a short explanation of every clause. This incidentally produces documentation that eases later maintenance work, and forces you to actually understand the generated logic instead of adopting it uncritically. Anyone who regularly skips this step risks having queries in production that nobody on the team can fully explain.

9. SQL query generation in direct comparison

Depending on the task, the amount of context and the number of iteration steps needed for a reliable result differ. The following overview classifies typical tasks by effort and risk when using Claude for SQL query generation and review.

The clear trend: the more structured context sits in the prompt, the less often correction is needed afterward. Time invested in the first prompt usually saves several correction loops later, especially for complex reporting queries with many JOINs and aggregations.

10. Summary

Claude for SQL query generation and review works reliably when the schema, the approximate row count and the security requirements are stated explicitly in the prompt. Complex JOINs, window functions and aggregations can be generated noticeably faster than writing them by hand, as long as the result is checked against real test data. During review, Claude uncovers patterns that are easily overlooked in everyday work: implicit type casts that render indexes unusable, redundant subqueries and missing parametrization.

EXPLAIN analysis becomes noticeably more accessible with Claude as a translator, even for developers without deep DBA knowledge. In Magento and Hyvä projects, the added complexity of EAV structures remains manageable with concrete attribute and entity context. The iterative workflow, generate, test, correct, remains the most reliable path to production ready queries.

11. FAQ: Claude for SQL Query Generation and Review