SQL Essentials for AI Engineers

SQL describes the rows you want, not a step-by-step loop for finding them. AI retrieval uses ordinary SQL to enforce tenant, state, language, freshness, and authorization before or alongside vector ordering. Learn selection, joins, aggregation, common table expressions, parameters, and transactions so a framework cannot hide unsafe or inefficient database behavior.

What will you be able to do?

Read SELECT, FROM, JOIN, WHERE, ORDER BY, and LIMIT.
Use parameters instead of string interpolation.
Aggregate ingestion and retrieval state.
Structure complex retrieval with a common table expression.
make multi-statement changes transactionally.

In what order should you read a query?

Read the logical story as: rows come from FROM/JOIN, are filtered by WHERE, grouped, projected by SELECT, ordered, and limited. The optimizer may execute a different physical plan while preserving SQL semantics.

sql

SELECT d.title, c.ordinal, c.content
FROM app.documents AS d
JOIN app.chunks AS c ON c.document_id = d.id
WHERE d.tenant_id = $1
  AND d.status = 'published'
  AND c.content <> ''
ORDER BY d.updated_at DESC, c.ordinal ASC
LIMIT $2;

$1 and $2 are parameters supplied separately by a PostgreSQL client. Never concatenate untrusted text into SQL. Parameters protect values; dynamic table or column names require a fixed allowlist and safe identifier composition.

How do you combine vector ordering safely?

sql

SELECT c.id, c.content, c.embedding <=> $2::vector AS distance
FROM app.chunks AS c
JOIN app.documents AS d ON d.id = c.document_id
WHERE d.tenant_id = $1
  AND d.status = 'published'
  AND c.embedding IS NOT NULL
ORDER BY c.embedding <=> $2::vector
LIMIT $3;

The application obtains $1 from authenticated server context, not request JSON. It validates $2 against the selected model and clamps $3 to an approved range.

When are grouping and CTEs useful?

sql

WITH ready AS (
  SELECT document_id, count(*) AS ready_chunks
  FROM app.chunks
  WHERE embedding_status = 'ready'
  GROUP BY document_id
)
SELECT d.id, d.title, coalesce(r.ready_chunks, 0) AS ready_chunks
FROM app.documents AS d
LEFT JOIN ready AS r ON r.document_id = d.id
WHERE d.tenant_id = $1;

Common table expressions improve readability and can define retrieval stages. They are not automatically faster; inspect the plan.

How do you verify it?

Use EXPLAIN (ANALYZE, BUFFERS) with non-sensitive test parameters. Compare returned row counts with a direct count and deliberately use a different tenant ID. Verify that no row crosses the boundary. Wrap mutation experiments in BEGIN and ROLLBACK.

What breaks in production?

Unsafe interpolation creates SQL injection.
LEFT JOIN accidentally becomes an inner join through a WHERE predicate.
LIMIT lacks deterministic ordering.
Tenant filters exist in one query path but not another.
An ORM loads rows and filters them in application memory.

AI pair-work prompt

Explain this SQL query clause by clause [paste]. Identify trust boundaries, parameters, possible duplicates, null behavior, and indexes worth testing. Rewrite it safely without changing semantics, then propose result and plan assertions.

Verification contract: Compare old and new results on fixtures, test cross-tenant denial, and inspect both plans before accepting the rewrite.

Check your understanding

Why can values be parameters but identifiers need another strategy?
Add a language filter to the vector query.
Write a query that counts failed embedding jobs per error code.

Official references