Beyond plain queries, SQL offers ways to encapsulate logic and reshape results. A Common Table Expression (CTE), introduced with WITH, names a subquery so that the main query can reference it like a table; recursive CTEs reference themselves and are ideal for traversing hierarchical data such as org charts or comment threads. Temporary tables exist only for the lifetime of a session or transaction, providing a scratch space without polluting the schema. Views are virtual tables defined by a stored query—convenient for security, reuse, and abstraction—while materialized views store the result physically and refresh it periodically, trading freshness for performance.
Programmability extends to stored procedures (reusable sets of SQL statements), SQL functions (routines that return a value), and triggers (code that runs automatically on data events such as inserts or updates). These let developers push business logic into the database, though doing so requires care to keep logic portable and testable. Some databases, such as PostgreSQL with PL/pgSQL and Oracle with PL/SQL, offer rich procedural languages, while T-SQL extends SQL Server with similar capabilities.
Several specialized operations reshape data. PIVOT rotates rows into columns—useful for cross-tab reports—and UNPIVOT does the reverse. Modern systems also offer native JSON column types and functions, enabling hybrid document-relational designs, as well as full-text search capabilities. PostgreSQL exemplifies this with tsvector and tsquery types and specialized GIN and GiST indexes that make text and JSONB searches fast. These advanced features let a single SQL engine cover use cases that would otherwise require separate systems.