SQL
A declarative language for defining, querying, changing, and controlling data in relational database systems.
Download Markdown (.md)Soroush’s Product Wiki · https://wiki.soroush.uk/topic/sql/
01What it means
SQL, or Structured Query Language, is the standard language family used with relational databases. It expresses desired results and changes through statements for data definition, queries, updates, transactions, and access control, while the database chooses an execution strategy. Products implement standards with differing features and dialects.[1][2]
- 01Schema definitionTables, types, keys, constraints
- 02Declarative statementSelect, filter, join, group, change
- 03PlannerChoose access and join strategy
- 04Transaction engineIsolation, errors, atomic changes
- 05Result + measurementInspect plans before tuning queries or indexes
SQL standards form a language family, while products differ in dialect and behavior. Parameterize untrusted values and grant least privilege.
02Why it matters
SQL lets applications and analysts work with related data without specifying low-level traversal procedures. Query meaning, parameterization, transactions, and indexing directly affect correctness, security, latency, and infrastructure cost.
03When to use it
Use SQL to work with relational data and systems that expose compatible query interfaces. Prefer application APIs for enforcing a product boundary, and use specialized query languages when another data model or workload is the true source of record.
04How it works
- Define tables, columns, data types, keys, and constraints with data-definition statements.
- Form queries by selecting desired columns and combining filtering, joins, grouping, ordering, and aggregation.
- Use parameterized statements for untrusted values and grant only the privileges each caller needs.
- Group dependent changes into transactions with suitable isolation and explicit error handling.
- Inspect execution plans and measurements before changing queries, indexes, or structures.
- Account for the target database dialect and test migrations and queries against its actual behavior.
05A practical example
A support tool uses a parameterized SQL query to join accounts, subscriptions, and recent invoices for one customer. An execution plan reveals a costly scan, so the team adds a targeted index after measuring the write and storage cost.
06Where it breaks
SQL dialects differ, and valid statements can still be slow, unsafe, or semantically wrong. Declarative syntax does not remove the need to understand data modeling, nulls, concurrency, access control, query plans, and transaction boundaries.
Common mistakes
- Building SQL by concatenating untrusted input instead of using parameters.
- Using SELECT * when callers need a stable, limited set of columns.
- Optimizing from query text alone without inspecting plans and production-like data.
- Assuming statements behave identically across database products and versions.