SQL Query Expert

Key Principles

• Always clarify which SQL dialect is being used — syntax differs significantly between PostgreSQL, MySQL, SQLite, and SQL Server.
• Write readable SQL: use consistent casing (uppercase keywords, lowercase identifiers), meaningful aliases, and proper indentation.
• Prefer explicit

  JOIN

  syntax over implicit joins in the

  WHERE

  clause.
• Always consider the query execution plan when optimizing — use

  EXPLAIN

  or

  EXPLAIN ANALYZE

  .

Query Optimization

• Add indexes on columns used in

  WHERE

  ,

  JOIN

  ,

  ORDER BY

  , and

  GROUP BY

  clauses.
• Avoid

  SELECT *

  in production queries — specify only the columns you need.
• Use

  EXISTS

  instead of

  IN

  for subqueries when checking existence, especially with large result sets.
• Avoid functions on indexed columns in

  WHERE

  clauses (e.g.,

  WHERE YEAR(created_at) = 2025

  prevents index use; use range conditions instead).
• Use

  LIMIT

  and pagination for large result sets. Never return unbounded results to an application.
• Consider CTEs (

  WITH

  clauses) for readability, but be aware that some databases materialize them (impacting performance).

Schema Design

• Normalize to at least 3NF for transactional workloads. Denormalize deliberately for read-heavy analytics.
• Use appropriate data types:

  TIMESTAMP WITH TIME ZONE

  for dates,

  NUMERIC

  /

  DECIMAL

  for money,

  UUID

  for distributed IDs.
• Always add

  NOT NULL

  constraints unless the column genuinely needs to represent missing data.
• Define foreign keys for referential integrity. Add

  ON DELETE

  behavior explicitly.
• Include

  created_at

  and

  updated_at

  timestamp columns on all tables.

Analysis Patterns

• Use window functions (

  ROW_NUMBER

  ,

  RANK

  ,

  LAG

  ,

  LEAD

  ,

  SUM OVER

  ) for running totals, rankings, and comparisons.
• Use

  GROUP BY

  with

  HAVING

  to filter aggregated results.
• Use

  COALESCE

  and

  NULLIF

  to handle null values gracefully in calculations.

Pitfalls to Avoid

• Never concatenate user input into SQL strings — always use parameterized queries.
• Do not add indexes without measuring — too many indexes slow writes and increase storage.
• Do not use

  OFFSET

  for deep pagination — use keyset pagination (

  WHERE id > last_seen_id

  ) instead.
• Avoid implicit type conversions in joins and comparisons — they prevent index usage.