The relational model stores facts as rows, connects them through keys, and enforces declared constraints at the database boundary. SQL states the required result rather than an access path. The optimizer chooses scans, seeks, join order, and physical operators that preserve the query’s semantics. A relational database is a strong default when integrity constraints, multi-row transactions, and new query combinations matter more than storing one access pattern in its final read shape.
Relational Boundary
Relational storage fits data whose validity depends on relationships the database must reject when broken. It also supports several row changes under one commit decision and lets new joins emerge without rebuilding the stored shape. Denormalization can remove an expensive join from a measured hot path, but the duplicate state creates a write-side consistency obligation. Normalization and denormalization explains how keys and dependencies set that boundary.
Query Processing and Joins
SQL has declarative semantics and a separate physical plan. A useful logical order is FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/TOP. The optimizer may push predicates or reorder joins physically only when duplicates, three-valued NULL logic, and the final result remain equivalent.
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE hire_date >= DATE '2024-01-01'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY headcount DESC;WHERE cannot use headcount because the output alias is defined later. ORDER BY generally can. Alias visibility is dialect-specific. PostgreSQL permits a simple output alias in GROUP BY, while SQL Server requires the original expression. Portable SQL repeats the grouped or aggregate expression.
graph LR P1["Parse syntax to tree"] --> P2["Bind names and types"] --> P3["Optimize candidate plans"] --> P4["Execute physical operators"] --> P5["Read pages and indexes"]
Cardinality estimates connect query semantics to physical cost. If a predicate is estimated at 10 rows but returns 1,000,000, a nested loop or join order that appeared cheap can repeat millions of probes or force downstream spills. The result remains correct. The chosen work is wrong for the actual row counts.
Join Semantics
Suppose customers contains Ada and Lin, while orders contains two rows for Ada and none for Lin. A left join returns Ada twice and fills Lin’s missing order columns with NULL. A join combines matching rows. It does not deduplicate them.
SELECT c.name, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.total;name | total
Ada | 40
Ada | 70
Lin | NULLPutting o.total >= 50 in ON preserves Lin as an unmatched left row. Putting it in WHERE removes Lin because NULL >= 50 is unknown.
| Physical join | Strong fit | Cost to watch |
|---|---|---|
| Nested loop | Small outer input with indexed inner probes | Repeated inner work when estimates are wrong |
| Hash join | Large equality joins with enough memory | Build memory and spills |
| Merge join | Inputs already ordered on the join key | Sorting when order is absent |
No physical join is universally fastest. Input size, ordering, row width, indexes, available memory, and cache state determine which operator is cheapest for one execution.
Transactions and Scale
Database locks and MVCC enforce isolation inside one database. That note also contrasts pessimistic locks with optimistic version predicates for stale application writes. Replication copies data for availability and eligible read traffic, while sharding partitions ownership when one primary can no longer carry the measured write or storage load.
Questions
What is the difference between WHERE and HAVING?
WHEREfilters source rows before grouping and cannot use aggregate results.HAVINGfilters groups afterGROUP BYand can test aggregates such asCOUNT(*). A non-aggregate predicate belongs inWHEREwhen it can reduce the rows entering the grouping step without changing semantics.
What is the difference between a stored procedure and a function?
A stored procedure runs as a separate database operation. It can execute several statements, change data, and return result sets or output parameters. A function returns a scalar or table value and can be used inside a query, so the database limits what it can do. SQL Server may inline an eligible scalar UDF. Otherwise, the function may run once per row and limit plan choices such as parallelism.
What is a Common Table Expression (CTE), and when is a temp table a better fit?
A CTE gives a name to a query result for one statement. It does not automatically store the result or guarantee that repeated references reuse the same work. A temp table is usually better when the intermediate result must be inspected, indexed, reused across statements, or optimized with its own statistics.
What are SQL Server transaction isolation levels?
SQL Server provides
READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,SERIALIZABLE, andSNAPSHOT. Enabling Read Committed Snapshot Isolation changesREAD COMMITTEDreads to statement-level row versions.NOLOCKis not a general performance switch: it permits observations of rolled-back work and can return missing or duplicate rows while data changes concurrently.