SQL

SQL, or Structured Query Language, is the declarative language used to create, query, modify, and control data in relational database management systems. Users state what result they want, and the database engine decides how to compute it. Its formal name is ISO/IEC 9075 "Database Language SQL", and the current edition is SQL:2023. It grew out of IBM research in the 1970s on relational theory and became one of the most widely used languages in computing. It is embedded in nearly every kind of software that stores structured data, from banking and enterprise systems to web applications, scientific data analysis, and data warehouses.

SQL matters for two reasons. First, it made the relational model practical, turning abstract ideas from set theory and logic into a working tool for storing and retrieving data at scale. Second, its standardization, combined with vendor dialects, created an ecosystem in which skills and many applications can move between products, even though portability is never complete. The language also has a long history of criticism, mainly for departing from the pure relational model in areas such as duplicate rows and missing values, and that tension shapes much of its design and of later alternatives.

The entry first explains the origins of SQL and the relational concepts it implements, then covers the main language components, query semantics, data types and NULL handling, constraints, transactions, and query optimization. It then follows standardization and vendor dialects, procedural extensions, advanced analytical features, semi-structured data, schema design, security, application interfaces, big-data and analytical use, criticism and alternatives, and the newer role of SQL in AI-assisted querying.

Origins: From Relational Theory to SEQUEL

Before 1970, the main database systems were built on hierarchical and network models, and programs usually had to follow stored links, so they depended on how the data was physically laid out. In 1970, there were two schools of thought about how to structure a database: the hierarchical model and the network model. The hierarchical model was used by IBM's Information Management System (IMS), the dominant database system at the time. In June 1970, Edgar F. Codd, working at IBM's San Jose research laboratory, published "A Relational Model of Data for Large Shared Data Banks" in Communications of the ACM. The paper built on n-ary relations, a normal form for relations, and a universal data sublanguage, and it aimed to protect users from needing to know the machine's internal representation. Its key move was to separate the logical description of data (what it means and what questions can be asked of it) from physical storage (layout, access paths, and location on disk). Codd called this property data independence: application programs should keep working when storage changes. The model rested on mathematical foundations drawn from set theory and first-order predicate logic, which is why it was attractive to theorists even though commercial systems were slow to adopt it.

IBM was initially reluctant to pursue the idea, since its existing hierarchical product was successful. It wasn't until 1973 that IBM started System R, a research project to explore Codd's ideas for the relational data model. Before System R began, Donald D. Chamberlin and Raymond F. Boyce had designed a language called SQUARE, which expressed relational queries in mathematical notation with subscripts that were hard to type. Chamberlin and Boyce decided to adapt it so that it resembled the structure of an English sentence and was easier to type. The result was SEQUEL (Structured English Query Language). By 1974, Chamberlin and Boyce published "SEQUEL: A Structured English Query Language" which detailed their refinements to SQUARE and introduced the data retrieval aspects of SEQUEL. SEQUEL was built to work with System R, an IBM research prototype that was described as a quasi-relational system, since it did not implement the full relational model.

The name changed for legal reasons. The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley Dynamics Engineering Limited company. Commercial products followed. Relational Software, Inc., later renamed Oracle Corporation, introduced the first commercially available implementation of SQL, Oracle V2 (Version2) for VAX computers. IBM's own products came later: IBM began developing commercial products based on their System R prototype including System/38, SQL/DS, and DB2, which were commercially available in 1979, 1981, and 1983, respectively. Some popular histories give 1981 as the date of IBM's first commercial SQL product, so the exact sequence of early releases depends on how "SQL product" is defined. Oracle and IBM's IBM products are often cited as the first two major commercial lines.

A central question in the 1970s was whether a declarative language, in which the user states the desired result and not the procedure for obtaining it, could run fast enough to compete with hand-navigated programs. The System R project was in part a test of this claim. Its answer was a query optimizer that considered alternative ways to execute a query, such as which index to use or how to join tables, and picked the cheapest one using statistics about stored data. The optimizer work was described in a 1979 paper by Patricia Selinger and colleagues, and it showed that the user did not need to specify the access strategy. This separation between what a query asks for and how the system executes it is one of the main reasons SQL became the dominant interface to relational databases rather than only a research notation.

Relational Foundations and Where SQL Departs from Them

From relations to tables

Edgar Codd's 1970 paper, written while he worked at IBM, treated data as a relation in the sense of set theory: a collection of tuples, each with one value for every named attribute. In the everyday picture, a relation is a table in which each row is a tuple and each column is an attribute. That correspondence is close but not exact. A SQL table has a fixed set of named columns, and its body is a collection of rows that the standard does not require to be a set. Codd also treated attributes as identified by name rather than by position, and he regarded the order of columns as inessential. SQL keeps named columns, but it does impose a column order in its catalog and in the output of SELECT *.

Codd's central aim was to separate the logical description of data from its physical storage, so that a user could state which rows are wanted without specifying how they are laid out on disk. That separation is the reason the table, rather than the pointer-linked records of earlier navigational systems, became the unit of data description. The consequences of the separation for SQL's own design are discussed in the origins section.

Keys and row identity

In the relational model, a superkey is any set of attributes whose values determine a tuple uniquely. A candidate key is a minimal superkey, meaning that dropping any attribute would destroy the uniqueness property. A primary key is the candidate key chosen to identify rows in a relation. Codd's 1970 paper used the primary key as the mechanism for linking relations: a foreign key is a set of attributes whose values must match the primary key of another relation. Entity integrity requires that primary key values never be undefined.

SQL expresses these ideas through PRIMARY KEY, UNIQUE and FOREIGN KEY constraints, but it does not enforce them in the same way. A table is legal without any key at all, so its rows have no identity beyond their contents, and two identical rows cannot be told apart by any query. The UNIQUE constraint also departs from the theory when nulls are involved. Under the standard, a null does not equal another null for the purpose of uniqueness, so several rows with a null in the constrained column can coexist. Such a column therefore does not behave as a candidate key in the strict sense. Vendors have also differed in how many nulls their unique indexes accept.

Where SQL departs: duplicates and order

Codd's relations are sets, so no two tuples are identical and tuples carry no inherent order. SQL tables are instead multisets (bags). In pure set theory, sets contain no duplicate elements; however, SQL's practical design chose to work with multisets (also called bags) by default, meaning a query's result can contain duplicate rows. A projection such as selecting only the city column from a customer table can therefore return the same city many times, and a table can store several rows that are exactly alike.

The designers made this choice for performance. IBM researchers Chamberlin and Boyce create SEQUEL (later SQL), choosing multiset semantics for performance reasons and adding DISTINCT as an explicit deduplication operator. Removing duplicates requires sorting or hashing the full result, which costs time and memory, so SQL keeps duplicates unless the query asks for their removal. The DISTINCT keyword and the difference between UNION and UNION ALL are the usual ways to request set behaviour.

Row order follows a similar pattern. A table has no inherent sequence, so the order of rows in a result is undefined unless the query includes an ORDER BY clause. SQL relies on order only when a query asks for it.

Consequences for correctness

The departures from set semantics change which algebraic laws hold. For sets, $R \cup R = R$ is an identity. On bags it fails, because the union of a bag with itself doubles every multiplicity, and SQL's own bag operators follow that rule. Several familiar techniques depend on set laws, so they can go wrong on bags.

Joins are the most common source of error. A join multiplies multiplicities: if one table contains a row twice and another contains the matching row twice, the join yields four copies of the result. Aggregates then inflate silently. A SUM computed after joining a parent table to several child tables counts the parent's values once per child row, and a COUNT(*) may count duplicates that a COUNT(DISTINCT ...) would not. These errors are often hard to notice because the query still returns plausible numbers.

The theory itself is also affected. Codd's theorem also doesn't consider SQL nulls and the three-valued logic they entail; the logical treatment of nulls remains mired in controversy. Additionally, SQL has multiset semantics as allowing duplicate rows. Critics from the relational camp argue that the duplicates should be prohibited, and that allowing them lets modelling errors pass unnoticed. If the source data contains identical rows, one reading is that some real-world entity was never given its own key. Correct SQL therefore depends on the author knowing whether a given table, or an intermediate result, is meant to be a set. In practice this means declaring keys wherever a row represents a unique entity, using DISTINCT deliberately rather than by habit, and aggregating before joining when one-to-many relationships would otherwise multiply values.

Language Components: DDL, DML, DQL, DCL, and TCL

SQL statements are commonly sorted by purpose into five families, and most tutorials and certification syllabi use the same labels. The grouping is a teaching convention rather than a formal division of the language. The standard organizes its statements in its own way, and database engines do not enforce the families as separate modes. The labels remain useful for reasoning about what a given statement can change in a relational database.

Data definition (DDL)

Data definition statements create, change, and remove schema objects. CREATE adds objects such as tables, views, indexes, and routines. ALTER changes the structure of an existing object, for example by adding a column or a constraint. DROP removes an object together with its definition. TRUNCATE is usually grouped here as well, although it empties a table rather than removing it, and it does not accept a WHERE clause. A DDL statement therefore changes the catalog that describes the database as well as the stored data. Dropping a table removes both its rows and its definition in one step.

Transactional behavior of DDL differs between products. In MySQL, CREATE, ALTER, and DROP cannot be rolled back, whereas PostgreSQL allows most DDL inside a transaction. Because of such differences, a migration script that works in one engine may leave a partially applied schema in another.

Data manipulation (DML)

Data manipulation statements change the rows of existing tables. INSERT adds rows, UPDATE rewrites the values of rows that match a condition, and DELETE removes rows that match a condition. INSERT grows a table, while UPDATE and DELETE affect only the rows selected by their predicates and leave the table's structure intact. Without a WHERE clause, UPDATE and DELETE apply to every row, which is a common source of accidents. MERGE, which conditionally inserts, updates, or deletes rows in a single statement, is generally classed as DML as well. Constraints still apply to DML: an INSERT or UPDATE that violates a constraint is rejected, and the rows it touched are not stored.

Data query (DQL)

SELECT is the most used statement in the language, and in most treatments it is the only member of DQL. It reads rows and returns a result set without modifying stored data. Its clauses are covered in the section on query semantics. Some textbooks fold SELECT into DML and count four families. Keeping it separate reflects how much of everyday SQL is read-only, and read-only work raises distinct questions about permissions, caching, and query tuning.

Access control (DCL)

Data control statements manage privileges. GRANT gives users or roles specific rights on tables, views, schemas, or routines, and REVOKE withdraws them. Privileges can also be granted with the option to pass them on to others. These statements are the SQL-level basis of the access control described later in the entry, and they are often run by a database administrator rather than by application code.

Transaction control (TCL)

COMMIT makes the changes of the current transaction permanent and visible to other sessions. ROLLBACK discards them and restores the state the database had when the transaction began. SAVEPOINT creates a named marker inside a transaction, and ROLLBACK TO SAVEPOINT undoes only the work performed after that marker while the transaction remains open. RELEASE SAVEPOINT removes a marker that is no longer needed. The standard requires the SAVEPOINT keyword in ROLLBACK TO, but PostgreSQL and Oracle allow it to be omitted. Support is also uneven across engines: in MySQL, only the InnoDB storage engine supports all three statements.

Where the boundaries blur

The five families are clean in textbook examples but overlap in practice. SELECT ... FOR UPDATE reads rows yet also takes locks, so it behaves partly like a write. SELECT INTO in some dialects, and CREATE TABLE ... AS SELECT in others, create new objects from a query, mixing DQL with DDL. INSERT ... SELECT and MERGE combine reading and writing in a single statement. TRUNCATE is filed under DDL, yet its effect is on data. Vendors also add utility statements, such as those for session settings, statistics collection, or maintenance, that fit none of the five categories cleanly. Because of this, the families are best read as a practical vocabulary for discussing risk and responsibility, such as which statements a read-only account should be unable to run, rather than as a strict taxonomy.

Query Semantics: Clauses, Joins, and Set Operations

Logical evaluation order

A SQL query is written beginning with SELECT, but the database reasons about it in a different sequence. The logical order for a single query block is FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT (including window functions), DISTINCT, ORDER BY, and finally LIMIT or OFFSET. This is a conceptual model, not a promise about execution. Optimizers may reorder work internally, but they must return the result this order defines.

The order explains several common rules. A column alias created in SELECT does not yet exist when WHERE runs, so it cannot be referenced there, but it can be used in ORDER BY, which runs later. Aggregate functions such as COUNT or SUM only exist after GROUP BY, which is why row filters belong in WHERE and group filters belong in HAVING. Because the join happens first, it fixes the row population that every later clause operates on, so a join condition placed in the wrong place can silently change aggregate results.

Joins

A join combines rows from two or more tables according to a condition. Consider two small tables: customers (1 Ann, 2 Ben, 3 Cy) and orders (101 for customer 1, 102 for customer 1, 103 for customer 2, and 104 for customer 9, which does not exist).

• Inner join keeps only pairs whose keys match. The query SELECT c.name, o.id FROM customers c INNER JOIN orders o ON o.customer_id = c.id returns three rows: Ann–101, Ann–102, and Ben–103.
• Left outer join keeps every row of the left table. Adding LEFT JOIN yields the same three rows plus Cy with NULL in the order column, for four rows.
• Right outer join keeps every row of the right table, so order 104 appears with a NULL customer name.
• Full outer join keeps unmatched rows from both sides, giving five rows: the three matches plus Cy and order 104, each padded with NULLs.
• Cross join pairs every row of one table with every row of the other, producing the Cartesian product. Here it returns 3 × 4 = 12 rows, and it is usually written only when that is intended, since omitting an ON condition in an ordinary join produces this result by accident.
• Self join joins a table to itself under different aliases. For example, an employees table with a manager_id column can be joined to itself so that each employee row is paired with the row of their manager.

Outer joins are where NULL padding appears, and a later WHERE condition on the padded side can quietly turn a left join back into an inner join, a frequent source of bugs.

Set operations

UNION, INTERSECT, and EXCEPT combine the results of two queries vertically, adding, keeping, or removing whole rows rather than adding columns as joins do. The operands must have the same number of columns with compatible types, and columns are matched by position, not by name; the result takes its column names from the first query.

By default these operators follow set semantics: duplicates are eliminated, as if DISTINCT had been applied. Each operator also has an ALL form that follows bag, or multiset, semantics and keeps duplicates. If a row occurs m times in one input and n times in the other, UNION ALL yields m + n copies, INTERSECT ALL yields the smaller count, and EXCEPT ALL yields the difference, floored at zero. Oracle uses MINUS as a synonym for EXCEPT. The ALL forms are generally cheaper because they skip the sort or hash step needed to remove duplicates, which is why a common practice is to use UNION ALL unless deduplication is actually required.

A subtle point is NULL handling. In ordinary comparisons NULL = NULL evaluates to unknown, but set operations treat two NULLs in the same column as equal for duplicate detection. Thus a row with a NULL value is deduplicated against an identical row, even though a WHERE clause would never match them.

Subqueries

A subquery is a query nested inside another statement. An uncorrelated subquery does not refer to the outer query and can be evaluated once, independently of each outer row. A correlated subquery refers to columns of the outer query, so its result depends on the current outer row and it is conceptually re-evaluated for each row. EXISTS returns true if the subquery produces at least one row, and it is often used to test for related rows without multiplying results through a join. IN tests whether a value appears in a set of values, which may come from a subquery.

NOT IN has a well-known pitfall. If the subquery's result contains a NULL and no other value matches, the comparison yields unknown rather than true, so the row is filtered out. A query such as selecting customers whose id is not in a column of orders can therefore return no rows at all once one order carries a NULL customer id. NOT EXISTS avoids this behavior, which is one reason it is frequently preferred.

Data Types and NULL Semantics

Type families and implicit conversion

SQL columns are typed, and the standard and major products group those types into a few families. Exact numeric types such as INTEGER and DECIMAL (NUMERIC) differ from approximate types such as REAL and DOUBLE PRECISION, which inherit the rounding behaviour of floating-point arithmetic. Character types hold fixed-length (CHAR) or variable-length (VARCHAR) text, and comparison and sorting depend on a collation. Date and time types include DATE, TIME and TIMESTAMP, with or without a time zone, plus INTERVAL for durations. BOOLEAN entered the standard in SQL:1999, but several widely used products have historically lacked a native boolean column and used a bit or small integer instead. Binary types (BINARY, VARBINARY and large-object types such as BLOB) store raw byte strings.

When an expression mixes types, the engine converts operands without being asked. Exact numbers are promoted in mixed arithmetic, and a text literal compared with a number is often cast to a number, although which side is converted, and how non-numeric text is handled, varies between engines. These conversions are convenient, but they can make a predicate behave differently from what its author intended. A conversion applied to an indexed column can also stop the optimizer from using that index.

Three-valued logic and NULL comparisons

Codd's scheme for relational databases with missing values extends Boolean reasoning with a third verdict, unknown. When a NULL is involved in a comparison operation, the result is unknown. The connectives follow the usual pattern for this kind of logic: FALSE AND UNKNOWN is FALSE and TRUE OR UNKNOWN is TRUE, while other combinations remain unknown. Negating unknown leaves it unknown, whereas negating true or false flips the value. (For a deeper treatment, see truth table and first-order logic.)

The practical consequence is that a WHERE or HAVING clause keeps only rows whose condition evaluates to true, so rows that yield unknown are discarded, just as false rows are. Comparisons between two null values, or between a null value and any other value, return unknown because the value of each NULL is unknown. Consequently, IS NULL and IS NOT NULL are the only tests that always return true or false, because they check for absence rather than compare values.

Grouping and duplicate elimination treat nulls differently from comparisons. All rows with a NULL in the GROUP BY column land in one group, which is the opposite of how comparisons behave.

Aggregates and COALESCE

Aggregate functions handle nulls by their own rule. In an aggregate, a null is left out of the calculation for every function except the row-counting form, so COUNT() counts every row. For example, in a 100-row orders table where 25 rows lack a shipment date, COUNT() returns 100, while COUNT(shipped_at) returns 75. The same rule distorts averages: if ten rows contain two nulls, AVG divides by eight, not ten. On an empty input, COUNT returns 0, but the other common aggregates return NULL, so a sum over no matching rows is unknown rather than zero.

COALESCE returns its first non-null argument and is the standard tool for substituting a default. It must be used with care: replacing a missing value with zero before calling AVG, as in AVG(COALESCE(x, 0)), changes the result, because the substituted zeros enter the denominator.

Criticism of NULL

NULL was introduced to satisfy a practical need. Codd created the concept so that relational systems could represent missing information and inapplicable information. Codd later separated these into two kinds of null, one for a value that exists but is not yet known and one for an attribute that does not apply to the entity. SQL, which was designed before this split, has only one NULL serving both purposes. Chris Date argued that this produces inconsistent semantics, and the critique goes beyond theory. Both Codd and SQL co-designer Donald Chamberlin pointed to the many defects that nulls cause in both database engines and application code.

The bugs are concrete. A NOT IN test whose subquery returns a null returns the empty set whenever the subquery contains any row with a null in that column, because the membership test becomes unknown and the filter removes the row. A CHECK constraint is subtler: when the constraint is evaluated against an inserted NULL, the comparison produces unknown, and the constraint treats that as satisfied. Vendor implementations add further variation, since commercial engines have only partly implemented the standard's logic, with variations of their own. Proposals that replace three-valued logic with richer many-valued logics, some with six values, have not gained wide use because they are harder to reason about.

Integrity Constraints

Integrity constraints are declarative rules stored in the schema that the database engine checks whenever data changes. A constraint is declared once on a table or column, and every statement that touches that data is then subject to it, regardless of which application issued the statement. The standard constraint types are PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

PRIMARY KEY designates the column or set of columns that identifies each row. A table can have at most one primary key, and its columns may not contain NULL, so the constraint combines uniqueness with non-nullability. UNIQUE forbids duplicate values across a column or column set, but unlike a primary key it can coexist with several other unique constraints. Its treatment of NULL varies. The standard and most products treat NULLs as distinct, so several rows may hold NULL in a unique column, although some implementations differ. NOT NULL rejects any statement that would leave a column empty. CHECK attaches a boolean condition to a row, for example requiring a quantity to be positive. A CHECK constraint is violated only when its condition evaluates to false; an UNKNOWN result, which can arise from NULL, is accepted. This asymmetry is a common source of surprise.

FOREIGN KEY expresses referential integrity: each non-null value in the referencing columns must match a value in a referenced key of another table, usually that table's primary key, or a unique column. Under the default matching rule, a foreign key that contains a NULL in any column is not checked. The constraint therefore prevents "orphan" rows, such as an order that points to a customer who does not exist, but it does not by itself say what should happen when the referenced row is later removed or changed. That is the role of referential actions. The standard defines NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT, which can be attached separately to ON DELETE and ON UPDATE. NO ACTION, the usual default, rejects the change if any dependent row remains, with the check performed at the end of the statement. RESTRICT rejects it immediately. CASCADE propagates the deletion or key change to the dependent rows, SET NULL replaces the foreign key values with NULL, and SET DEFAULT replaces them with the column's default. The choice is a modeling decision. CASCADE suits child rows that have no meaning apart from their parent, such as line items of an order, while RESTRICT suits records that must be kept for history, such as financial transactions. Cascading actions can chain across several tables, and in some products they create ordering problems when the same table is affected by more than one path.

Constraint checking is normally immediate: each statement is validated when it executes, and a violation aborts that statement. Many systems also allow a constraint to be declared DEFERRABLE, with an initial mode of either IMMEDIATE or DEFERRED. A deferred constraint is checked only at COMMIT, which allows a transaction to pass through temporarily inconsistent states, for example when inserting two rows that reference each other. Session-level statements can switch the mode for a given transaction. Support varies: PostgreSQL and Oracle offer deferrable constraints, whereas some engines, such as MySQL's InnoDB, do not provide deferred foreign-key checking. Where it is unavailable, developers must order their statements carefully or temporarily disable checks, which is itself a risk.

Constraints move validation from application code into the database. A rule written once in the schema applies to every client, batch job, and ad hoc session, so it cannot be bypassed by a forgotten check in one service. This centralization is the main argument for declaring rules in the schema, and it is one reason why the relational model's designers emphasized declarative integrity. The trade-offs are practical. Each constraint adds work on every write, and foreign keys often require indexes on the referencing columns to be checked efficiently. Bulk loads may be slowed or need constraints temporarily disabled. Error messages are generated by the engine and may be less friendly to end users than application validation. Constraints also cover only what the schema can express. Rules that span several tables, such as requiring that an order total equal the sum of its lines, are not covered by the standard CREATE ASSERTION construct, which few products implement, and must instead be enforced with triggers or by application logic. For this reason, mature designs usually combine declarative constraints for structural rules with application-level checks for richer business logic.

Transactions, ACID, and Concurrency Control

A database transaction groups several operations into one unit that either takes effect as a whole or not at all. The four guarantees usually summarized as ACID are atomicity (all of a transaction's changes are applied, or none are), consistency (a transaction moves the database from one state that satisfies its integrity rules to another that does too), isolation (concurrent transactions do not expose each other's intermediate work beyond what the chosen isolation level allows), and durability (committed changes survive a crash). Atomicity and durability are mainly the job of logging and recovery machinery. Isolation is where the real trade-offs appear, because enforcing it means restricting how concurrent operations may interleave, which is the central problem of concurrent computing.

Isolation levels and anomalies

The SQL-92 standard defined four isolation levels by reference to three phenomena. A dirty read occurs when a transaction sees data written by another transaction that has not yet committed and may later roll back. A non-repeatable read occurs when the same row, read twice in one transaction, returns different values because another transaction committed a change in between. A phantom occurs when a repeated search returns a row that was not there before, because another transaction inserted or modified a matching row. The levels, from weakest to strongest, are:

The table describes the minimum guarantees. Real systems vary: a level may prevent more than the standard requires, and the standard's wording was written with lock-based systems in mind. In 1995, Berenson and colleagues showed that snapshot isolation exhibits none of the anomalies SQL-92 prohibited yet is not serializable, because it permits write skew, in which two transactions read overlapping data and write disjoint rows so that the combined result violates a rule neither could see alone. Default levels also differ across products; PostgreSQL and Oracle default to read committed, while MySQL's InnoDB engine defaults to repeatable read. Developers therefore need to check what a given engine actually guarantees rather than assume the name of the level.

Locking and two-phase locking

Lock-based concurrency control grants shared locks for reading and exclusive locks for writing. Shared locks are compatible with each other, but an exclusive lock excludes all other locks on the same item. Two-phase locking (2PL) constrains when locks may be taken and released: during a growing phase a transaction acquires locks, and once it releases any lock it enters a shrinking phase in which it may acquire no more. This discipline guarantees conflict-serializable schedules. Plain 2PL can still allow dirty reads and cascading aborts, so practical systems usually use strict or rigorous 2PL, which holds exclusive locks until commit or abort.

2PL does not prevent deadlock. A deadlock is a cycle of transactions, each waiting for a lock held by the next. Most systems use deadlock detection: the engine maintains a waits-for graph, in which each transaction is a node and an edge runs from Ti to Tj when Ti waits for Tj, and periodically searches it for cycles, typically with a background thread. When a cycle is found, the engine chooses a victim by criteria such as transaction age, progress, number of locks held, or prior restarts, and aborts or restarts it. Checking more often costs CPU, while checking less often lengthens waits. Alternatives are deadlock prevention schemes such as wound-wait and wait-die, and conservative 2PL, which acquires every lock before execution begins and so never deadlocks but may hold locks longer than necessary.

Multiversion concurrency control

Multiversion concurrency control (MVCC) avoids much of this blocking by never overwriting a row in place. An update creates a new version, and each transaction reads the version that was committed as of its snapshot. Because readers see an older version rather than waiting for a writer's lock, readers do not block writers and writers do not block readers. Snapshot isolation is the most common isolation level built on MVCC, and it usually detects write-write conflicts at commit time, so the first committer wins and the other transaction is aborted.

MVCC does not remove locking. Writers still need some locks and commit-time checks, and the approach has costs. Old versions occupy storage and must eventually be reclaimed; PostgreSQL, for example, relies on periodic vacuuming to remove dead tuples, and a long-running transaction can prevent that cleanup from reclaiming space. The approach trades lock waits for storage management and version bookkeeping, which is why it is widely used in transactional systems that serve many concurrent readers.

Indexes, Query Planning, and Optimization

An index is an auxiliary data structure that maps column values to the locations of matching rows, so the engine can avoid reading an entire table. The most common structure in relational systems is the B-tree, a balanced tree whose lookups, insertions, and deletions take time logarithmic in the number of rows. Because leaf entries are kept in key order, a B-tree can serve equality predicates, range predicates such as BETWEEN or <, and ORDER BY on its leading columns. A hash index instead hashes the key, which gives near-constant lookup time for equality tests but cannot answer range queries or return rows in order. Many systems also offer bitmap, GiST, GIN, and full-text indexes for specialized workloads.

Indexes differ in how they relate to the stored rows. A clustered index determines the physical order in which the table's rows are stored, so the table is effectively organized around that key; a table can have only one such ordering. A non-clustered index is a separate structure whose entries hold the indexed key and a pointer to the row, such as a physical address or a primary-key value. A lookup through a non-clustered index may need a second access to the base table, unless the index is covering, meaning it contains every column the query requires. The trade-off is consistent across designs: an index speeds up reads that match it, but every INSERT, UPDATE, and DELETE that touches an indexed column must also update that index. This adds I/O, page splits, lock contention, and storage overhead. Indexes on low-selectivity columns, such as a boolean flag, often yield little benefit.

A SQL statement is declarative: it specifies the wanted result, not the procedure for computing it. The engine parses and rewrites the query, and then a query optimizer enumerates candidate execution plans. These choices include the access path for each table (a full scan or a particular index), the order of joins, the algorithm for each join, and whether sorting or aggregation is needed. Each candidate receives an estimated cost. Early relational work relied partly on rules, but the cost-based approach was established in the System R project; Selinger and colleagues described it in 1979, including dynamic programming over join orders. Costing depends on cardinality estimates, which predict how many rows each predicate and join will produce. These estimates come from statistics kept in the system catalog: row counts, numbers of distinct values, null fractions, and histograms of value distribution. Optimizers typically assume that predicates are independent and that values are spread evenly, and these assumptions often fail. Because errors compound across joins, a misestimate of several orders of magnitude can turn a query that should take seconds into one that takes hours. Statistics therefore must be refreshed as data changes, using commands such as ANALYZE in PostgreSQL or UPDATE STATISTICS in SQL Server.

The main join algorithms suit different inputs. A nested loop join scans the outer input and, for each row, probes the inner input, usually through an index. It is efficient when the outer input is small and the inner side is indexed, but it performs poorly when both sides are large. A hash join builds an in-memory hash table from the smaller input and then probes it with rows from the other input. It handles only equality conditions, and it needs enough memory; when memory runs short, partitions spill to disk. A sort-merge join sorts both inputs on the join key, or uses an existing index order, and then merges them in one pass. Its cost is dominated by sorting, which is roughly $O(n \log n)$, but it works well when inputs are already sorted, when memory is limited, and when the result must be ordered. The optimizer chooses among these using estimated sizes, available indexes, sort order, and memory settings.

The plan the optimizer selects can be inspected with EXPLAIN, which displays a tree of operators annotated with estimated row counts and costs. Other systems provide similar tools: Oracle offers EXPLAIN PLAN, and SQL Server exposes actual execution plans. EXPLAIN ANALYZE in PostgreSQL and MySQL executes the statement and reports actual row counts and timings beside the estimates, so the gap between estimated and actual rows is often the most useful diagnostic. Developers commonly look for full scans where an index was expected, nested loops over large inputs, and sorts or hash operations that spill to disk. Typical remedies include adding or redesigning indexes, rewriting predicates so that indexed columns are not wrapped in functions, refreshing statistics, and restructuring the query. Optimizer hints, such as Oracle's index hints, can force a plan, but they freeze assumptions that may stop being true as the data changes. Plans can also shift unexpectedly after statistics updates or changes in parameter values, which is why plan inspection remains part of routine tuning.

Standardization: Process and Versions

Origins in ANSI and ISO

SQL was formally standardized in the United States in 1986. The language became an official standard in October of 1986, with ANSI's release of document X3.135-1986 "Database Language - SQL". The name is somewhat misleading: ANSI does not develop standards, but instead accredits standard developing organizations (SDOs) in the United States, and it is the committees of these SDOs that develop standard documents. The committee responsible was ASC X3, which has since become INCITS, an ANSI-accredited body. The international version followed quickly. The ISO, a worldwide federation of national standards bodies, adopted the ANSI document in 1987. Today the international text is developed by ISO/IEC JTC 1, and the standard is maintained by ISO/IEC JTC 1/SC 32, while INCITS adopts the international editions as American National Standards.

From SQL-86 to SQL:2023

The first edition, often called SQL-86, was a basic specification. Its first revision, SQL-89, appeared in 1989 and was published as "SQL with Integrity Enhancement", adding integrity constraints. SQL-92 was a much larger step. The standard specification itself grew about five times compared to SQL-89, though much of it was due to more precise specifications of existing features. Its new functionality included explicit outer joins, set operations, and the CAST operator (explicit JOIN syntax with outer joins, plus NATURAL JOIN and CROSS JOIN, and set operations).

SQL:1999 changed the way conformance was organized. Starting with SQL:1999, the standard defines a large set of individual features rather than the three levels found in SQL-92. A large subset of these features represents the "Core" features, which every conforming SQL implementation must supply. It also brought major additions, such as recursive queries, triggers, and array types. From SQL:2003 onward, the standard was published as a set of numbered parts. SQL:2006 added Part 14 for XML, and SQL:2019 added Part 15 for multidimensional arrays. The most recent edition, SQL:2023, is the ninth edition of the ISO and ANSI standard and was formally adopted in June 2023. Its headline addition is property graph queries, a graph query language built on top of SQL, alongside new JSON features.

The Parts of ISO/IEC 9075

The standard is titled "Information technology – Database languages – SQL" and is numbered ISO/IEC 9075. Since SQL:1999 the standard has been divided into several parts numbered from 1 through 16 (as of SQL:2023). Not every number is in use: the missing parts were never released. The main active parts are:

• Part 1, Framework, which provides a rough overview and some definitions of commonly used terms.
• Part 2, Foundation, which defines most of the SQL language.
• Part 3, Call-Level Interface (SQL/CLI), which describes the C and COBOL APIs for accessing databases.
• Part 4, Persistent Stored Modules (SQL/PSM), which defines the server-side procedural language.
• Part 9, Management of External Data (SQL/MED), for accessing data stored outside the database.
• Part 10, Object Language Bindings (SQL/OLB), for embedding SQL in Java, which is not JDBC, which treats SQL statements as strings.
• Part 11, Information and Definition Schemas (SQL/Schemata), which covers the information schema.
• Part 14, XML-Related Specifications, and Part 15, Multidimensional Arrays.
• Part 16, Property Graph Queries (SQL/PGQ), which defines ways for the SQL language to represent property graphs and to interact with them.

Sources do not agree on how many parts are current. One count says there are now 11 active parts of SQL, and the part most people know as the core language is part 2. Another describes the standard as 10 parts: 1-4, 9-11, and 13-16, and a third lists nine parts for the current edition. The differences come from which parts are counted, since unreleased or merged parts are treated differently. Only a few parts are widely implemented. Part 2, Part 11, and Part 14 are the ones that have become common in practice.

Conformance and Vendor Adoption

The standard defines a minimum. Parts 1, 2, and 11 encompass the minimum requirements of the language, while other parts define extensions. Because no single implementation can ever implement all features, vendors usually claim conformance to a subset. In practice, most database management systems claiming SQL standard conformance were conforming at only the Entry level. Oracle, for example, has full compliance at the Entry level and only partial compliance at the higher SQL-92 levels. Under the feature-based model, all new functionality in the SQL standard is in the form of optional features, each with a feature code, so a claim is effectively a list of supported features rather than a single label.

Conformance claims are judged against the latest edition. Each version replaces the previous one, so claims of conformance to earlier versions have no official merit. PostgreSQL, for instance, states that its development aims for conformance with the latest official version of the standard where such conformance does not contradict traditional features or common sense. Vendors also tend to diverge from the standard in ways that matter for portability, which is one of the reasons the standard was created in the first place. The goal of introducing a standard was to enable the portability of SQL applications across conforming products.

Dialects, Portability, and Vendor Extensions

The core of SQL is shared across products, but each major database system implements its own dialect, and the differences show up in everyday statements. Every major database system implements its own variation of the standard, adding unique features, syntax quirks, and extensions. The main families are Oracle Database, Microsoft SQL Server, PostgreSQL, MySQL (and its MariaDB fork), and SQLite, and the ones a developer meets most often differ in ways that go beyond cosmetics.

Divergent implementations and syntax

The clearest differences appear in row limiting, identity generation, and built-in functions. The three embedded or open-source engines that most often serve web applications use a trailing LIMIT clause, whereas SQL Server places TOP after SELECT, or the more verbose but ANSI-compliant OFFSET...FETCH syntax. Oracle has historically relied on ROWNUM and has recently adopted more modern approaches with the FETCH FIRST syntax. Surrogate keys show the same pattern: Oracle uses sequence objects, SQL Server uses IDENTITY(1,1), PostgreSQL uses SERIAL, and MySQL uses AUTO_INCREMENT. Scalar functions also vary, with SQL Server using + for string concatenation, PostgreSQL using ||, and most others preferring CONCAT().

Architecture can matter as much as syntax. SQLite is not run as a server; it stores the entire database in a single file on disk. Oracle is also distinctive in requiring a table reference in every SELECT that lacks one, since Oracle requires 'FROM DUAL' in SELECT queries without a table.

Vendor procedural languages

Each major vendor extends SQL with a procedural language that adds variables, loops, conditionals, and exception handling. Oracle's PL/SQL is the best-known example, and it is designed for procedural extension for SQL on the Oracle database. Microsoft's Transact-SQL (T-SQL) is the equivalent for SQL Server. PostgreSQL's PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998. It closely resembles Oracle's PL/SQL language.

These languages differ in detail. For example, in PostgreSQL, transaction management commands can only be used in procedures, not functions, whereas PL/SQL allows these commands in both contexts. Declaration rules also differ: PL/pgSQL requires all variables in a single DECLARE section before the BEGIN, while T-SQL permits declarations at other points in a batch.

Portability problems

Portability problems are usually concentrated in the procedural layer rather than in plain queries. One comparison guide observes that the query layer is close enough that most statements move with light edits, while variables, control flow, error handling, and built-in functions are where code breaks. Type names are a frequent source of friction: common differences include BOOL versus BIT, timestamp versus datetime, now() versus getdate(), and SERIAL versus INT IDENTITY(1,1).

Pagination and row limits cause some of the most visible breakage. SQL Server's OFFSET...FETCH requires an ORDER BY clause to be valid, and the OFFSET clause is required whenever FETCH is present. Query plans can also diverge: a statement that performs well on one engine may be slow on another, not because the SQL is bad, but because the optimizer is different. Identity handling, which varies from sequences to auto-increment keywords, complicates bulk loading and migration as well.

Strategies and trade-offs

The usual strategy is to write to the standard wherever the standard suffices. Standard constructs such as OFFSET...FETCH, CASE expressions, and ANSI joins tend to be accepted by several engines, although even these have version-specific limits. Where a dialect is unavoidable, teams commonly isolate it in a data-access layer, in query-builder or migration tools, or behind database views and stored routines, so that application code does not branch on the database type. Application code that tests the engine name and chooses between different LIMIT or date functions is widely regarded as a fragile pattern.

The trade-off is that vendor features often deliver real gains: procedural logic that runs close to the data, optimizer hints, or specialized types can reduce network round trips and simplify business rules. Those gains come at the cost of lock-in, since code written in PL/SQL or T-SQL cannot run unchanged elsewhere and must be re-authored during any migration. Projects therefore tend to decide deliberately which vendor features are worth their portability cost, and to test against the actual target engine rather than assuming that a query written for one product will behave identically on another.

Stored Routines, Triggers, and Views

Stored routines are procedural code kept inside the database and invoked by name. The SQL standard's procedural layer is specified in ISO/IEC 9075-4, Persistent Stored Modules (SQL/PSM), which adds flow control, variables, assignment, exception handling, and cursors to the language. Procedures are called as statements, while user-defined functions are used inside expressions and return a value. The current edition is ISO/IEC 9075-4:2023, but most products implement only part of the model and extend it in their own directions, so procedural code is one of the least portable parts of SQL. Oracle's PL/SQL, Microsoft's Transact-SQL, and PostgreSQL's PL/pgSQL each follow the general shape of SQL/PSM with different syntax and runtime behavior.

Triggers are routines attached to a table or view that run automatically when rows are inserted, updated, or deleted. Triggers first appeared in SQL:1999. A trigger is defined by its timing (BEFORE, AFTER, or INSTEAD OF), its triggering event, and its granularity: a row-level trigger runs once for each affected row, while a statement-level trigger runs once per statement. Row-level triggers can reference the old and new values of each row, and an optional condition can restrict when the action runs. The standard limits what a BEFORE trigger may change, but implementations differ: Oracle lets a BEFORE row-level trigger alter the incoming new values, and PostgreSQL allows a BEFORE trigger to skip the operation or modify the row. INSTEAD OF triggers give a view write behavior, as described below. Typical uses include enforcing business rules, such as rejecting a salary increase above a set percentage or blocking enrollment beyond a course's capacity, and auditing, where each change is written to a log table together with the old values, the new values, and the acting user. Because one trigger's action can fire other triggers, chains of triggers can become hard to predict, and recursive chains must be bounded by the implementation.

Views are named queries that behave like virtual tables. A simple view over a single base table, without aggregation, grouping, DISTINCT, or set operations, can usually be updated, and the update is passed through to the underlying table. More complex views, such as those built from joins or aggregates, are generally read-only unless an INSTEAD OF trigger supplies the write logic. The WITH CHECK OPTION clause rejects writes that would make a row disappear from the view's own filter. Views abstract data access by hiding columns, renaming fields, and restricting rows, which is why they are also used in access control. A materialized view differs because it stores the result physically. Its contents do not follow the base tables automatically: Materialized views do not update automatically. Systems offer different refresh policies. Oracle can refresh either ON DEMAND or ON COMMIT, and an ON COMMIT view is updated as transactions commit, which keeps it current at a cost to write performance. A complete refresh re-runs the defining query and can be slow on large data, while incremental (fast) refresh applies only the changes since the last refresh, when the defining query meets the system's restrictions. In PostgreSQL, a standard refresh could block other connections which are trying to read from the materialized view, and the CONCURRENTLY option avoids this only if the view has a suitable unique index.

Placing logic inside the database carries recurring risks. Business rules split between application code and triggers or procedures are harder to find, test, and version-control, and a trigger can produce side effects that a developer calling an INSERT never sees. Row-by-row procedural loops and per-row triggers can cause severe slowdowns during bulk loads, and the query optimizer cannot reason about procedural code in the same way it reasons about declarative queries. Dialect-specific routines also raise migration costs, since moving a schema to another engine usually means rewriting its procedures, triggers, and sometimes its views. Materialized views add the risk of stale results, which can surprise users who assume the data is live. Because of these costs, many organizations keep routines limited to integrity enforcement, auditing, or performance-critical operations, and keep most business logic in application or service layers.

Advanced Query Features: Windows, Recursion, and Grouping

Window functions

Window functions were added to the standard in SQL:2003, alongside XML-related features and sequence generators. Most modern databases support core SQL:2003 features, with window functions being nearly universal by 2010. Unlike an aggregate, a window function computes a value for each row from a set of related rows without collapsing those rows. GROUP BY replaces rows with summaries, while window functions add summaries to rows that already exist.

The OVER clause defines the window. The "window" is that set of related rows, defined by PARTITION BY and ORDER BY inside an OVER() clause. PARTITION BY restarts the calculation for each group, so it narrows a window function to groups of rows that share a value. An empty OVER() applies the function to the whole result, while OVER(ORDER BY) creates cumulative calculations following the specified order. For ordered windows, the default frame runs from the start of the partition to the current row, which is why a plain SUM produces a running total: the default frame for an ordered window is "all rows from the start of the partition up to this one," and that is what produces a running total rather than the overall sum on every row.

Ranking and offset functions are the other common family. Using RANK(), employees can be ranked by salary, allowing gaps in ranks when salaries are equal. ROW_NUMBER assigns a unique sequence without such gaps, and DENSE_RANK avoids gaps as well. LAG and LEAD read values from neighbouring rows; in a partitioned query, each category's lag looks back to that category's previous month, not across all categories. A frequent use is selecting the latest record per entity, such as the most recent status for each user, by numbering rows within each partition and keeping the first.

Recursive queries

The evolution culminated in the SQL:1999 standard, which formally introduced the WITH RECURSIVE Common Table Expression. A recursive CTE consists of a non-recursive base term and a recursive term that refers to the CTE's own name, where the base case does not reference R and the recursive case references R. The two parts are combined with UNION or UNION ALL. The engine evaluates the base term, then repeatedly applies the recursive term to the newest rows, and iteration proceeds until a fixed point is reached.

The standard imposes restrictions on this process. The standard imposes linear recursion and restricts where the recursive reference may occur, so the self-reference appears at most once per branch. SQL:1999 also permits mutually recursive definitions. The choice between UNION and UNION ALL matters: with UNION, the process discards duplicate rows from the current table before continuing. That deduplication is why graph traversals usually use UNION: once a node is revisited, no new rows appear and iteration stops, which protects against cycles. Some engines require the keyword explicitly, for example the RECURSIVE keyword must be included if any CTE in the WITH clause is recursive.

Typical applications include organizational hierarchies or Bills of Materials, where the depth is not known in advance. Because an unbounded recursion can run indefinitely, engines impose limits; Databricks, for instance, may override the max level by using the MAX RECURSION LEVEL hint when more than 100 steps are genuinely needed.

Grouping sets, ROLLUP, and CUBE

These constructs are extensions of the GROUP BY clause. They can generate the same result set as using UNION ALL to combine single grouping queries; however, using one of the GROUP BY operators is usually more efficient. GROUPING SETS is the most general form: when the groupings generated by a full ROLLUP or CUBE are not all required, you can use GROUPING SETS to specify only the groupings that you want.

ROLLUP is designed for hierarchies. It creates a result set that includes subtotals and a grand total in addition to the regular grouped results. ROLLUP assumes a hierarchy among the dimension columns and only generates grouping sets based on this hierarchy. Because of this, ROLLUP (A, B) will not be the same as ROLLUP (B, A). By contrast, CUBE creates a result set that includes all possible combinations of aggregations for the specified columns. The output grows exponentially: CUBE with 10 columns produces 1,024 grouping sets, almost never what anyone wanted.

Subtotal rows are represented by NULL in the output, which conflicts with NULLs already present in the data. This inclusion is somewhat surprising in that they introduce a second meaning, 'all', for the NULL value besides 'unknown'. To resolve this, SQL Server provides functions such as GROUPING() / GROUPING_ID() functions that distinguish data-NULL from subtotal-NULL. Regarding the standard's history, these are now standard ANSI SQL from 1999, though WITH CUBE and WITH ROLLUP were first introduced by Microsoft.

Lateral joins and row pattern matching

A LATERAL derived table in the FROM clause may refer to columns of tables listed earlier in the same FROM clause, which lets a subquery run once per outer row and return several rows. It is commonly used for top-N-per-group queries that would otherwise require awkward correlated subqueries. Row pattern matching, standardized in SQL:2016 and implemented in products such as Oracle through the MATCH_RECOGNIZE clause, allows a query to describe sequences of rows with a regular-expression-like pattern, for example to find a run of falling and then rising values in time-ordered data. These are specialized constructs: most analytical work relies on window functions, grouping sets, and recursive CTEs.

Semi-Structured Data: XML, JSON, and Arrays

A relational table assumes that each column holds atomic values and that every row follows one fixed schema. Semi-structured data, in which fields and nesting vary from record to record, fits that model poorly. The standard's response was to add dedicated types, functions, and constructors for these formats instead of changing the table model itself. The main strands are SQL/XML for XML, the SQL/JSON additions for JSON, and collection types for arrays.

SQL/XML

SQL/XML, or XML-Related Specifications, is part 14 of the SQL specification. It introduces a predefined XML data type, together with constructors, several routines, functions, and mappings between XML and SQL types, to support manipulating and storing XML in a SQL database. SQL:2003 defined the basic data type, mappings, predicates, and functions. The SQL:2006, SQL:2008, and SQL:2011 editions extended these earlier parts so that they work more closely with XQuery. XQuery is a query language specified by the W3C's XML Query Working Group, and its data model is an abstraction over XML documents, which may be typed or untyped.

Implementations show how the standard was applied in practice. Microsoft SQL Server 2005 implemented the XML data type defined in ISO SQL-2003, and its column type accepts both complete documents and content fragments with several top-level elements, provided they are well-formed. A column can also be bound to an XML schema collection, which yields typed XML that is validated on insert and checked more precisely during query compilation. Query access is provided through methods: query(), value(), exist(), and nodes(), each of which takes an XQuery expression as its argument, plus modify() for changes.

JSON

JSON support arrived later and started in a more modest form. SQL:2016 only had JSON operations on JSON data stored in character string fields. That version introduced functions such as JSON_VALUE, JSON_QUERY, JSON_OBJECT, JSON_OBJECTAGG, and JSON_TABLE, the last of which turns JSON content into rows and columns. Its path language, which is based on JavaScript syntax, was also introduced in this edition. Because the data was just text, the database could not check it by type. Some systems compensated: SQL Server 2016, for example, had no JSON-specific data type, and continued to store JSON in NVARCHAR columns. Its ISJSON function can be used to create check constraints on NVARCHAR columns that contain JSON text.

SQL:2023, the ninth edition of the standard, was formally adopted in June 2023. Its JSON changes are organized as optional features. The native JSON data type, identified as T801, was added, and many implementations already offered it. T802 adds an optional check for unique keys, and T803 keeps the string-based form from SQL:2016. An implementation may support the older string-based approach, the native type, or both. The edition also added JSON_SERIALIZE, JSON_SCALAR, and the IS JSON predicate, and it brought dot notation for accessing JSON members, which yields shorter queries than the JSON_QUERY and JSON_VALUE functions. Hexadecimal integer literals were permitted in the path language under T840. Vendors have followed these changes at different speeds; most of the functions proposed in SQL:2023 were already available in PostgreSQL, according to one 2023 commentary.

Arrays

The standard also defines an ARRAY collection type, which was introduced in SQL:1999 and allows a column, variable, or routine parameter to hold an ordered sequence of elements of one declared type. Array elements are addressed by position, and the declaration sets a maximum cardinality. Standard arrays are one-dimensional. Multidimensional arrays, which PostgreSQL supports, are generally vendor extensions rather than part of the standard's core model. This distinction matters for portability, because code that depends on nested array semantics does not carry over cleanly between systems.

Arrays differ from relational tables in several ways. A single array-valued column stores a whole collection in one field, which breaks the first normal form that relational design aims for. Elements have positions rather than keys, and their order is part of the value. To return to row-oriented processing, a query must expand the array into rows, and the resulting table is then processed like any other relation. Semi-structured types therefore provide flexibility in storage, while the relational model still governs how the data is queried and constrained.

Schema Design and Normalization

Schema design decides which tables exist, which columns each holds, and how rows in one table point to rows in another. A common starting point is the entity-relationship model, in which real-world things become entities, their properties become attributes, and their associations become relationships. In the relational translation, each entity usually becomes a table with a primary key. A one-to-many relationship is represented by a foreign key on the "many" side that references the "one" side. A many-to-many relationship requires a separate junction table whose composite key combines the keys of the two participants. This gives a first draft of the table structure, which normalization then tests and refines.

Normalization is built on functional dependencies. An attribute depends on another set of attributes when the second determines the first. The normal forms are successive tests on these dependencies. First normal form requires that every cell hold a single atomic value, which rules out lists packed into one column. Second normal form removes partial dependencies, where a non-key column depends on only part of a composite key. Third normal form removes transitive dependencies, where a non-key column depends on another non-key column. 1NF requires atomic (single) values in every cell - no comma-separated lists. 3NF eliminates transitive dependencies - non-key columns must not depend on other non-key columns. Boyce-Codd normal form (BCNF) is a stricter version of 3NF. A table is in BCNF if it is in 3NF and for every functional dependency (X → Y), X should be a super key. Under BCNF, every determinant must be able to serve as a candidate key, which closes gaps that 3NF can leave when a table has several overlapping candidate keys.

The practical motivation is the set of anomalies that redundant data produces. An update anomaly appears when one fact is copied into many rows: a customer's email stored on every order row must be changed everywhere, and a missed row leaves contradictory data. An insertion anomaly blocks recording one fact without an unrelated one, such as a product's price existing only once someone has ordered it. A deletion anomaly loses facts as a side effect, so removing a customer's last order can also erase that customer's details. Splitting the customer data into its own table, linked by a key, removes each of these problems because the fact lives in one place.

Normalization has costs. Splitting data across many tables makes reads depend on joins, and each additional table adds to the design's complexity. Though it may increase the number of tables, it provides a more logically sound and reliable database structure. For that reason, many transactional systems treat 3NF as the practical baseline, and they reach BCNF only when a specific anomaly justifies the extra decomposition.

Denormalization is the deliberate reverse: a designer reintroduces redundancy, for instance by storing a precomputed order total, copying a customer name into an order table, or flattening several tables into one wide table, so that common queries need fewer joins. Denormalization is intentionally used in OLAP data warehouses to boost read-query performance. The trade-off is that every write path must now keep the duplicated values consistent. That work usually moves into application code, triggers, or scheduled jobs, where it is easy to get wrong.

Schema choices therefore shape the long-term cost of a database. A normalized design keeps updates small and predictable, but it makes some queries longer and more expensive to plan. A denormalized design simplifies reads and reporting, but it increases the risk of inconsistent data and raises maintenance costs whenever the business rules change. SQL features such as primary keys, foreign keys, and unique constraints allow the database itself to enforce the chosen design, rather than leaving that enforcement to every application that writes to the tables.

Security and Access Control

SQL's access-control model starts from the principle that a database account should be able to do only what its work requires. The basic unit is the privilege, a grant that lets a user, a role, or the special group PUBLIC perform a specific action on a specific object. Table-level privileges include SELECT, INSERT, UPDATE, and DELETE, and they can be narrowed to individual columns, so a user might be allowed to update a salary column but not read it elsewhere. Routines carry their own EXECUTE privilege, and views can be granted separately from the tables beneath them. Revocation removes a grant, but the model is not always simple. A user can receive the same privilege from several grantors, so revoking one grant may not remove access. The System R prototype's revoke semantics were studied in detail in 1976 for exactly this reason. The GRANT and REVOKE statements themselves are covered in the section on language components.

Privileges and role-based access control

Granting privileges one user at a time becomes unmanageable in a large system, so the standard and most products provide roles, which are named collections of privileges. A role is granted to users, and changing the role changes the access of everyone who holds it. This is the basis of role-based access control (RBAC), in which permissions are attached to job functions such as "analyst" or "application writer" rather than to individuals. The WITH GRANT OPTION clause lets a recipient pass a privilege on to others, which is convenient but makes revocation harder to reason about. Some vendors add features on top of the standard. SQL Server, for example, has DENY, which explicitly blocks access and overrides grants received through other roles. Views and stored routines are also used as access boundaries: a user may be allowed to query a view that exposes only certain columns and rows, and a routine can be defined to run with its owner's rather than the caller's rights. Applying least privilege, meaning each account has only the rights it needs, limits the damage from both mistakes and attacks.

SQL injection

SQL injection is a vulnerability that appears when an application builds SQL text by concatenating untrusted input into it. If a login query is assembled from a typed username, a crafted input can close the string literal and add its own clauses, changing what the database executes. The result can be unauthorized reading, modification, or deletion of data, and in some configurations, access to the host system. The weakness is classified as CWE-89 and has been a standing item in application-security guidance for decades.

The main defense is parameterized queries, also called prepared statements. The application sends the SQL text with placeholders and then supplies the values separately, so the database parses the command's structure before it sees any data. Because values are bound as data, they cannot introduce new syntax. OWASP lists prepared statements first among its defenses, followed by properly constructed stored procedures and allow-list input validation, and it strongly discourages relying on escaping user input. Parameterization has limits: placeholders can carry values but not identifiers such as table or column names, so those must come from a fixed allow-list. ORMs and stored procedures are not automatically safe if they internally build dynamic SQL. Input validation and least-privilege accounts are supporting controls rather than substitutes for parameterization.

Row-level security

Privileges control access to whole tables, but many applications need access limited to some rows. Row-level security (RLS) addresses this by attaching policies to a table that filter which rows a user may read or change. PostgreSQL added the feature in version 9.5. An administrator enables it on a table and then creates policies that name the operations they cover and a condition that must hold for each row. The USING clause governs which existing rows are visible to queries and modifications, while WITH CHECK governs rows that are inserted or updated. Once RLS is enabled, normal access must be allowed by a policy, so a table with no applicable policy yields no rows. Table owners and superusers bypass policies by default unless the table is set to force RLS.

A common use is multi-tenant data isolation. A policy can compare a tenant identifier column to a session setting or to the current database role, so every tenant's queries see only their own rows regardless of which application code issues them. Enforcing the rule in the database means a bug in the application's WHERE clauses cannot leak data across tenants. Views offer a related but weaker mechanism, since they depend on the view definition being correct and on the underlying objects not leaking data through functions. The costs of RLS are complexity as policies multiply and the need to keep policy expressions efficient, because they are evaluated for every candidate row.

Auditing, logging, and encryption

Access control decides who may act, but auditing records what actually happened. Database audit facilities and logs capture logins, privilege changes, and data-modifying statements, which supports incident investigation and compliance reviews. Logs are most useful when stored where the database's own administrators cannot silently alter them, and audit trails based on triggers can be bypassed by users with sufficient privileges. Encryption addresses different threats. Encryption at rest protects stored files and backups, and column-level encryption can protect especially sensitive fields, though it complicates querying and indexing. Encryption in transit, typically through TLS between client and server, prevents interception on the network. Key management is a separate and often difficult problem, since encrypted data is only as safe as the keys that unlock it. These measures complement, rather than replace, privileges, roles, and row-level policies, and together they form the layered approach that database security practice generally recommends. Broader questions about the handling of personal data are addressed in the entries on cybersecurity and data privacy.

Connecting Applications: Interfaces and Drivers

Most SQL reaches a database from inside a program rather than from an interactive terminal. Over time three broad approaches have emerged: statements embedded directly in a host programming language, call-level interfaces that pass SQL text through a fixed set of functions, and higher-level abstraction layers that generate SQL on the application's behalf.

Embedded SQL

The oldest approach is embedded SQL, in which statements are written inline in a host language such as C, COBOL, or Fortran. A vendor-supplied precompiler scans the source, replaces each statement with calls into a runtime library, and passes the result to the ordinary compiler. The standard defines embedded SQL for several host languages, but the static form fixes the statement text at compile time, with host variables carrying values in and out. Dynamic SQL covers cases where the text is assembled at run time. Because each vendor's precompiler differed, embedded programs were hard to move between databases, which motivated the call-level alternative.

SQL/CLI and the call-level model

A call-level interface (CLI) avoids precompilers entirely: the application passes SQL as strings to functions and then reads results and error codes through the same API. The SQL Access Group, a consortium of database vendors, began work on such an interface in the early 1990s. The specification later moved to X/Open, which expanded it in late 1994, and it was incorporated into the ISO/IEC 9075 series as Part 3, SQL/CLI. The standard defines handles for environments, connections, and statements, a diagnostics mechanism, and rules for mapping SQL data types to host-language types. Its current published edition is ISO/IEC 9075-3:2023, and a revision was under development as of 2026. In practice, few applications program against SQL/CLI directly; they use libraries such as ODBC and JDBC, which follow the same design.

ODBC and JDBC

Open Database Connectivity (ODBC) was created by the SQL Access Group and first released in September 1992, with Microsoft shipping an early implementation for Windows that year. Its architecture has four parts: the application, a driver manager that loads drivers on the application's behalf, the driver itself, and the data source. The driver receives ODBC calls, submits SQL to a specific database, and returns results, so an application written against the common API can work with different products by swapping drivers. ODBC has since been revised several times and remains widely supported on Windows, Unix-like systems, and macOS.

Java Database Connectivity (JDBC) followed the same pattern for the Java platform. Sun Microsystems began the project in January 1996, froze the specification that June after consulting database vendors, and shipped the first version with JDK 1.1 in February 1997. Its driver manager can route calls to several drivers. JDBC drivers are classified into four types: Type 1 bridges to ODBC, which adds overhead and requires native libraries on every client machine; Type 2 converts calls into a vendor's native client API; Type 3 sends calls to a middleware server that speaks to the database; and Type 4 is a pure-Java driver that speaks the database's network protocol directly. Type 4 drivers are the common choice today because they need no native code and deploy simply.

Whatever the interface, the driver's core job is translation: it converts API calls into the commands and wire-protocol messages that a particular server understands, which is why the same application code can behave differently when a driver's quality or feature support varies.

Object-relational mapping and the impedance mismatch

Application code usually works with objects, with identity, inheritance, references, and nested collections, while a relational database stores rows in flat tables linked by keys. The gap between these two models is often called the object-relational impedance mismatch. Loading an object graph by hand can require many joins or repeated queries, and the "N+1 query" pattern, in which one query fetches a list and then one more query per item fetches related data, is a frequent performance problem.

Object-relational mapping (ORM) frameworks, such as Hibernate for Java, Entity Framework for .NET, and SQLAlchemy for Python, bridge the gap by mapping classes to tables, tracking changes to objects, and generating SQL for reads and writes. They reduce boilerplate, but the generated SQL is not always efficient, lazy loading can trigger hidden queries, and developers who ignore the emitted statements may not notice poor plans or excessive round trips. Many teams therefore mix ORM use with hand-written SQL for critical queries.

Connection pooling and prepared statements

Opening a database connection involves network setup, authentication, and session initialization, so applications rarely open one per request. Connection pools keep a set of live connections and lend them to threads or requests as needed. Pool size matters: too many connections can overload the server, and pooled sessions must have their state reset so that settings or temporary objects do not leak between users.

A prepared statement separates the SQL text from its parameter values. The server parses and plans the statement once and reuses the plan for later executions with different values, which improves performance when the same query runs repeatedly. Parameter binding also keeps user-supplied data out of the SQL text, which is the main defense against SQL injection. The protection holds only when the driver really sends parameters separately; some drivers emulate preparation on the client, so the security benefit depends on driver behavior and configuration. Pooling and prepared statements interact as well, since many pools cache prepared statements per connection.

Analytics, Data Warehouses, and Big Data

SQL's analytical use grew out of a split between two kinds of workload. Transactional systems (OLTP) handle many small reads and writes against current data. Analytical processing (OLAP) asks aggregate questions, such as revenue by region over several years, that scan large volumes of historical data. Organizations therefore often copy operational data into a separate store, a data warehouse, that is loaded on a schedule and tuned for reporting. Data warehouses are typically organized as dimensional models. A central fact table records measurable events, such as sales lines, with numeric measures and foreign keys to surrounding dimension tables that describe the who, what, where, and when. In a star schema, each dimension is a single, denormalized table, so most questions need only one join per dimension. A snowflake schema normalizes those dimensions into sub-tables, which reduces redundancy and storage but adds joins. Many production warehouses mix the two, normalizing only the large or frequently changing dimensions.

The engines that execute these queries rely heavily on physical layout. A columnar store keeps each column's values together instead of keeping each row's fields together. A query that needs three columns out of fifty reads only those three, and because values of one type sit next to each other they compress well and can be encoded compactly. The trade-off is that retrieving or updating a single complete row is comparatively expensive, which is why columnar systems are poorly suited to transaction processing. Massively parallel processing (MPP) complements this design. Tables are partitioned across many nodes, often with no shared memory or disk between them, and the planner splits each query into fragments that run concurrently on the nodes holding the relevant partitions, then merges the partial results. Products such as Vertica and MariaDB ColumnStore combine these two techniques, and many cloud warehouses, including Amazon Redshift, do likewise.

Before such engines matured, SQL was brought to very large datasets through the Hadoop ecosystem. Apache Hive, developed at Facebook around 2010, provided a SQL-like language, HiveQL, over files in the Hadoop Distributed File System, so analysts did not have to write MapReduce programs by hand. Hive compiled queries into batch jobs, first MapReduce and later Tez or Spark, and its early versions lacked full transactions and offered only limited subquery support. Full insert, update, and delete semantics with ACID guarantees arrived in release 0.14. Spark SQL, first released in 2014, took a different route: it runs inside the Spark engine, lets developers mix SQL with programmatic DataFrame code, and can read tables registered in a Hive metastore. Both systems use schema-on-read and columnar file formats such as Parquet and ORC, and both trade interactive latency for scale and fault tolerance.

Cloud data warehouses generally separate storage from compute, so that query capacity can be scaled up or down independently of the stored data, and customers pay for usage rather than fixed hardware. Their SQL dialects are usually close to the standard, but functions for loading, time travel, semi-structured data, and workload management differ, which affects portability. Streaming SQL addresses a different need: continuous queries over unbounded event streams, such as clicks, sensor readings, or payments. Rather than running once and returning a result set, a streaming query runs indefinitely and emits updated results as events arrive. To make aggregation possible over endless input, streaming dialects add time windows (tumbling, sliding, and session windows) and explicit rules for late-arriving events. Stream processors such as Apache Flink offer SQL interfaces of this kind, and streaming and batch engines increasingly share one query vocabulary so that the same logic can run over history and over live data.

Criticism, NoSQL, and Alternatives

Criticism of SQL's departures from the relational model

Relational purists have objected to several features SQL either inherited or added. Codd's model treats a relation as a set, so it has no duplicate tuples and no meaningful attribute order. SQL tables can contain identical rows, and their columns have a fixed left-to-right order that shows up in SELECT * and in positional INSERT statements. NULL adds further friction. By introducing a third truth value, it departs from the two-valued logic on which the relational algebra rests, and it produces results many users find counterintuitive, such as comparisons that never evaluate to true and aggregates that silently skip missing values. Critics including C. J. Date have argued that these departures make queries harder to reason about and make correctness harder to guarantee. Defenders reply that duplicates are cheap to retain and sometimes needed for correct aggregates, and that NULL is a practical way to represent missing information.

NoSQL: origins and families

The term NoSQL was used from 2009 to label gatherings of advocates of non-relational databases. In contrast to RDBMSs, most NoSQL databases are designed to scale well in the horizontal direction and not rely on highly available hardware. The expansion "not only SQL" later became common, since many NoSQL databases support SQL-like query syntax alongside their native query models.

Four families are usually distinguished. Document stores keep data as self-contained documents, typically in JSON or BSON format. Structure is flexible and varies from document to document. Key-value stores save data as pairs: a unique key and its associated value. This simple structure allows very fast lookups when you know the key. Column-family (wide-column) stores hold rows identified by a row key, with many columns attached to each row, and wide-column data stores are not to be confused with column-oriented databases used for analytics. A graph database focuses on the relationship between data elements. Each element is contained as a node. Connections are first-class elements of the database, stored directly.

The main trade-off is scale-out against strict transactions. Most NoSQL databases prioritize availability and partition tolerance over strong consistency (CAP theorem). They also tend to drop features that are hard to distribute. Cassandra, for example, does not support joins or subqueries. Designers therefore denormalize data so that each query can be served from a single table. Flexible schemas speed up early development, but they also move integrity checks out of the database and into application code.

NewSQL

The term NewSQL was coined by 451 Research analyst Matt Aslett in 2011. NewSQL systems aim to keep the relational model and SQL while gaining horizontal scalability. They provide the familiar SQL interface and ACID guarantees of traditional RDBMS alongside the horizontal scalability and fault tolerance of NoSQL systems. To get there, they use techniques such as synchronous replication and sharding to ensure data consistency across nodes. Many also rely on distributed consensus protocols like Raft or Paxos to keep replicas in agreement. CockroachDB and TiDB are commonly cited examples. Some critics note that many NewSQL products still rest on the foundations of traditional relational engines, which can limit how well they behave in large, truly distributed deployments.

SQL-like languages in other systems

Several NoSQL products provide their own query languages that look like SQL. Cassandra Query Language (CQL) is the native interface to Apache Cassandra. It uses the same abstract idea of tables made of rows and columns as SQL, but its semantics differ. Queries generally need to specify the partition key, and the language omits joins and subqueries. Couchbase's N1QL, a query language for JSON documents, extends SELECT-style syntax to nested document structures. These languages lower the learning curve for developers who already know SQL, while exposing the constraints of the underlying storage model.

Continuing dominance in enterprise use

Despite the growth of alternatives, SQL remains the default for much enterprise data. Relational and NewSQL systems are generally chosen for banking, accounting, and ERP workloads that require guaranteed transactional consistency. Relational databases use SQL, a standardized language for querying structured data. Standardization, mature tooling, and a large pool of trained developers mean that many NoSQL and NewSQL vendors also offer SQL or SQL-like interfaces. In practice, organizations often combine several database types rather than replacing relational systems outright.

SQL and Large Language Models

Text-to-SQL is the task of turning a natural-language question into an executable SQL query against a known database. Work on it predates the current generation of large language models, but LLMs have made it practical enough for routine analytics, and the task now drives most evaluation of LLM-based data access.

Benchmarks and evaluation

Most published results come from a few public benchmarks. Spider comprises 10,181 questions and 5,693 distinct queries across 200 databases, each with multiple tables, and BIRD is a large-scale, cross-domain benchmark of 12,751 unique question-SQL pairs across 95 large real-world databases. BIRD is harder in part because it requires reasoning using external knowledge, such as synonym knowledge and value illustrations.

The main metric is execution accuracy, which checks whether the predicted query returns the same result as the reference query. It has a known weakness: an incorrect prediction can coincidentally produce the same output as the correct query. Test-suite accuracy addresses this by requiring the query to pass across multiple database instances, which are derived from automated database augmentations. BIRD additionally reports a valid efficiency score that compares runtime against the reference query. Robustness variants probe generalization directly: Spider-Syn replaces schema-related words with synonyms, while Spider-Realistic removes explicitly mentioned column names from the questions. Even so, execution-based scoring has limits; one study concluded that an execution-based accuracy cannot capture all correct SQL queries, so human review remains part of serious evaluation.

Typical errors and mitigations

The most common failure is in schema linking, the step of identifying which tables and columns a question refers to. In one error analysis of open-source models, the most common error was schema linking, which occurred when the model changed the name of some columns or hallucinated columns and table-column associations. A larger study of four systems on Spider and BIRD found that schema errors cause over 80% of execution failures. This is a hallucination in a structural sense: the model invents references that look plausible but do not exist in the database. Join errors follow a similar pattern, becoming more frequent as more tables are added to the prompt, because the model has no precedence rules for resolving implicit join paths across similarly named tables.

The most dangerous errors are the silent ones. A query with a wrong column can run without complaint and return plausible numbers. Filter values are another source of quiet failure: a query may guess the casing of a categorical value and return an empty set without explaining why.

Several mitigations have become standard. The first is schema retrieval: since it is impractical to prompt all the table descriptions into the LLM for a large database, systems pass only the relevant subset. One practitioner report found that restricting the schema to the relevant columns was the single largest improvement in its pipeline. The second is execution-based validation: candidate queries are run against the database, and results are compared or voted on. One high-performing approach selects the SQL with the highest consistency in execution results through a majority vote. Generated queries can also be checked for parse validity and retried, with failed queries fed back to the model for repair.

Natural-language interfaces and review

Natural-language interfaces let business users query data without knowing SQL, but they make it easy to trust answers that are wrong. Accuracy depends heavily on documentation. Without a semantic metadata layer that maps cryptic column names and encoded status values to business concepts, a model is forced to guess at opaque column names, hallucinate business logic, and misinterpret encoded status values. Practical guidance therefore favors human sign-off for high-stakes queries and keeping schema and glossary metadata current, rather than treating a one-time schema dump as sufficient.

Security of generated SQL

Generated SQL should be treated as untrusted input, not as a trusted artifact. The threat is not only a confused model: adversaries can craft inputs that induce the generation and subsequent execution of malicious SQL, which makes this a variant of injection attacks. The exposure is worsened by configuration. Many LLM-to-SQL integrations in practice grant broad read access and, in some cases, write access.

The standard defenses are layered. A statement filter can accept only SELECT statements and reject data-changing or multi-statement input before execution. The application should connect through a dedicated read-only database account instead of an administrator account, which keeps even a successful injection from modifying data. Row-level security and views can limit which rows and columns that account can reach. OWASP guidance similarly recommends using read-only database accounts where possible. Read-only sandboxes or replicas add a further boundary, so that generated queries never run against the primary production system. None of these measures makes generated SQL correct; they only bound the damage when it is wrong or malicious.

References

1. Important Papers: Codd and the Relational Modeltwobithistory.org
2. The History of SQLlearnsql.com
3. Raymond F. Boyceen.wikipedia.org
4. SQL his. SQL was initially developed at IBM by…medium.com
5. History of SQL: The Evolution of a Database Languagemedium.com
6. Donald Chamberlin & Raymond Boycehistoryofinformation.com
7. SQLen.wikipedia.org
8. SQL History and Evolution: From Origins to Modern Variantssoftwarepatternslexicon.com
9. SQL History - W3Schoolsw3schools.in
10. [PDF] A relational model of data for large shared data bankssemanticscholar.org
11. The Evolution of the Computerized Databasearxiv.org
12. DISTINCT — Use DISTINCT and understand when it changes...varsitytutors.com
13. Codd's theoremen.wikipedia.org
14. Codd's Theorem for Databases over Semiringsarxiv.org
15. A Compiler for Operations on Relations with Bag Semanticsarxiv.org
16. Information Retrieval P. BAXENDALE, Editor A Relational Model of Data forengineering.upenn.edu
17. 1 Relational Database Model Introduced by E.F. Codd (1970)ovid.cs.depaul.edu
18. Relational Model: Keys, Constraints & Integrity (2026)perfectnotes.org
19. • CHAPTER 23 • Serious Flaws in SQLdl.acm.org
20. Projection Operation: Relational Algebra Theory vs. The Reality of Queries - Informaticsemramadhan.com
21. SQL Commands: DQL, DDL, DML, DCL & TCL with Examplesguru99.com
22. SQL Sub-Languages: DDL DML DCL TCL DQL Explained with Examplescodersnexus.com
23. SQL Commands: DDL, DML, DCL, TCL and DQL Explainedcodewithsql.com
24. SQL Commands (DDL, DML, DQL, DCL, TCL) with Examplesmygreatlearning.com
25. Commit, Rollback, Savepoint in MySQL Baeldung on SQLbaeldung.com
26. PostgreSQL: Documentation: 18: ROLLBACK TO SAVEPOINTpostgresql.org
27. SQL - Transactionstutorialspoint.com
28. Understanding SQL Query Order of Executionbuiltin.com
29. SQL Order of Operations: The Logical Execution Order Behind Every Query Trapprachub.com
30. SQL Execution Order Explained: Why WHERE Runs Before SELECTsqlism.com
31. PostgreSQL UNION, INTERSECT and EXCEPT Explainedchat2db.ai
32. UNION EXCEPT INTERSECT keywordsquestdb.com
33. The Set Operatorsdocs.oracle.com
34. INTERSECT, UNION, EXCEPT, and MINUS - Alibaba Cloudalibabacloud.com
35. UNION, INTERSECT, and EXCEPT clausesdocs.cloudera.com
36. Set operations (SQL)en.wikipedia.org
37. Achieving Data Privacy through Secrecy Views and Null-Based Virtual Updatesarxiv.org
38. T-SQL three-valued logic and ANSI_NULLS setting!medium.com
39. Issue #011: NULL and Three-Valued Logic: The SQL Traps That Cost You Silentlybytesbtrees.substack.com
40. NULL and UNKNOWN (Transact-SQL) - SQL Serverlearn.microsoft.com
41. SQL Aggregate Functions: COUNT, SUM, AVG, MIN, and MAXboot.dev
42. Null (SQL)en.wikipedia.org
43. Codd's Twelve Rulesred-gate.com
44. No More Nulls!arxiv.org
45. Database Research needs an Abstract Relational Query Languagearxiv.org
46. Comprehending nullsarxiv.org
47. Relational Algebra and Calculus with SQL Null Valuesarxiv.org
48. Do NULL markers in SQL cause any harm?lemire.me
49. Cascading Referential Integrity Constraintslearn.microsoft.com
50. CREATEdocs.snowflake.com
51. Referential Integrity: Definition and Examplesseedfa.st
52. Transaction Isolation Levels (ODBC) - ODBC API Referencelearn.microsoft.com
53. Multiversion concurrency controlen.wikipedia.org
54. Snapshot isolationen.wikipedia.org
55. Two-phase lockingen.wikipedia.org
56. Lecture #17: Two-Phase Locking 15-445/645 Database Systems (Spring 2024)15445.courses.cs.cmu.edu
57. Two-Phase Lockingmaxnilz.com
58. All about Two-Phase Locking and a little bit MVCCdzone.com
59. Multiversion Concurrency Control (MVCC)dev.to
60. Deep Dive into Postgres Multi-Version Concurrency Control: Architecture, Snapshot Isolation, and Performance Optimizationmartinuke0.github.io
61. Chapter 1sql-99.readthedocs.io
62. SQL: American National Standard Adoptions by INCITS - The ANSI Blogblog.ansi.org
63. IEC 9075en.wikipedia.org
64. SQL-92en.wikipedia.org
65. The History of SQL Standardslearnsql.com
66. PostgreSQL: Documentation: 13: Appendix D. SQL Conformancepostgresql.org
67. SQL:2023en.wikipedia.org
68. The 16 Parts of the SQL Standard ISO/IEC 9075modern-sql.com
69. Announcing the General Availability of the SQL:2023 Standardblogs.oracle.com
70. SQL:2023 is finished: Here is what’s newpeter.eisentraut.org
71. ISO/IEC 9075-1:2008 - Information technology — Database languages — SQL — Part 1: Framework (SQL/Framework)iso.org
72. Conformance Levels in SQL-92modern-sql.com
73. Oracle and Standard SQLdocs.oracle.com
74. The SQL Standard - ISO/IEC 9075:2023 (ANSI X3.135) - The ANSI Blogblog.ansi.org
75. Standard ANSI SQL: What It Is and Why It Mattersdbvis.com
76. SQL Dialects: MySQL vs PostgreSQL vs SQL Server vs SQLitetutorialreference.com
77. SQL Dialectsstructuredanalytic.com
78. Understanding SQL Dialects: How Different Databases Speak Their Own SQL Languageblog.incubyte.co
79. SQL Dialects Explained: How to Translate Between Different Database Systems - Data With Sarahdatawithsarah.com
80. 0% found this document useful (0 votes)scribd.com
81. Difference between T-SQL and PL-SQL - GeeksforGeeksgeeksforgeeks.org
82. pgSQLen.wikipedia.org
83. Transitioning from Oracle to Postgres: PL/SQL vs PL/pgSQLstormatics.tech
84. T-SQL vs PL/pgSQL — Reference for SQL Server → PostgreSQLjamsql.com
85. What is TSQL and how is it different from SQL, PL/SQL, and PL/pgSQL?tableplus.com
86. Effective SQL Server Paging Strategies Beyond Basic OFFSET FETCHsqlpey.com
87. SQL Dialects Differences — Standards vs Extensionsadhdecode.com
88. SQL Server, PostgreSQL, MySQL: What's the Difference?datacamp.com
89. SQL Dialect Variations (Syntax, Data Types, Procedural Extensions)relationaldbdesign.com
90. SQL vs PL-SQL vs T-SQLguru99.com
91. kindatechnical()kindatechnical.com
92. 7 Refreshing Materialized Viewsdocs.oracle.com
93. PostgreSQL: Documentation: 18: REFRESH MATERIALIZED VIEWpostgresql.org
94. PostgreSQL: Documentation: 18: CREATE TRIGGERpostgresql.org
95. Oracle Row-level Triggersoracletutorial.com
96. Triggers Overviewjcsites.juniata.edu
97. Chapter 10. Triggershsqldb.org
98. ISO/IEC 9075-4:2011 - Information technology — Database languages — SQL — Part 4: Persistent Stored Modules (SQL/PSM)iso.org
99. ISO/IEC 9075-4:2008 - Information technology — Database languages — SQL — Part 4: Persistent Stored Modules (SQL/PSM)iso.org
100. Triggers Exercisescs.unibg.it
101. Refreshing a materialized view - Amazon Redshiftdocs.aws.amazon.com
102. SQL:2003en.wikipedia.org
103. SQL:2003sqlpedia.org
104. SQL Window Functions Explained: The Examples That Actually Made Them Clickmedium.com
105. SQL Window Functionsthoughtspot.com
106. SQL Window Functions and PARTITION BY in Practicemarmelab.com
107. Window Functions in SQL - GeeksforGeeksgeeksforgeeks.org
108. Recursive SQL: A Complete Guidepuppygraph.com
109. Compiling Linear Datalog to SQL for Program Analysisarxiv.org
110. Recursive aggregatesimage-ppubs.uspto.gov
111. MySQL :: MySQL 8.4 Reference Manual :: 15.2.20 WITH (Common Table Expressions)dev.mysql.com
112. Introducing Recursive Common Table Expressions: Making Databricks SQL Turing Completedatabricks.com
113. Using GROUP BY with ROLLUP, CUBE, and GROUPING SETSlearn.microsoft.com
114. Grouping Sets, Rollup and Cube in SQL - by Avi Chawlablog.dailydoseofds.com
115. SQL Server ROLLUPsqlservertutorial.net
116. GROUPING SETS & CUBE · Intermediate & Advanced SQLdatavidhya.com
117. Questions About CUBE, ROLLUP and GROUPING SETs That You Were Too Shy to Askred-gate.com
118. SQL Grouping Sets, ROLLUP & CUBE: Multi-Dimensional Aggregation Deep Divepipecode.ai
119. Window Functions in SQL Serverred-gate.com
120. 3. Recursive Common Table Expressionssqlite.org
121. Group By in SQL Server with CUBE, ROLLUP and GROUPING SETS Examplesmssqltips.com
122. XMLen.wikipedia.org
123. Introduction to XQuery in SQL Server 2005learn.microsoft.com
124. XML Support in Microsoft SQL Server 2005learn.microsoft.com
125. JSON support in SQL Server 2016red-gate.com
126. SQL Server 2016: JSON integrationlearn.microsoft.com
127. The SQL:2023 standard has already been published, get to know itblog.desdelinux.net
128. SQL/JSON Data Model and JSON in SQLtigerdata.com
129. Getting Started with JSON Support in SQL Server 2016databasejournal.com
130. Database Normalization Explained: 1NF, 2NF, 3NF, BCNF ...perfectnotes.org
131. Normalization (1NF, 2NF, 3NF, BCNF)theintactone.com
132. Normalization in DBMS: 1NF, 2NF, 3NF, BCNF Explainedtutorsbot.com
133. DBMS Normalization: 1NF, 2NF, 3NF Database Exampleguru99.com
134. Database Normalization: 1NF, 2NF, 3NF & BCNF Explained (2026)generalistprogrammer.com
135. PostgreSQL: Documentation: 18: 5.9. Row Security Policiespostgresql.org
136. SQL Injection Prevention - OWASP Cheat Sheet Seriescheatsheetseries.owasp.org
137. Top 5 Best Practices for Preventing SQL Injection Attackskiuwan.com
138. PostgreSQL Row Level Security (RLS): Basics and Examplessatoricyber.com
139. Multi-tenant data isolation with PostgreSQL Row Level Securityaws.amazon.com
140. SQL GRANT and REVOKE Permissionstutorialreference.com
141. Revoke Grant SQLperlego.com
142. ODBC on Linux and UNIX platformseasysoft.com
143. What is Open Database Connectivity (ODBC)?techtarget.com
144. Call Level Interfaceen.wikipedia.org
145. SP 96: SQL Access Group's Call-Level Interfacejacobfilipp.com
146. ISO/IEC 9075-3:2023 - Information technology — Database languages SQL — Part 3: Call-Level Interface (SQL/CLI)iso.org
147. ISO/IEC CD 9075-3 - Information technology — Database languages SQL — Part 3: Call-Level Interface (SQL/CLI)iso.org
148. Introduction to JDBCcondor.depaul.edu
149. Main Components : 1. The JDBC APIpvpsiddhartha.ac.in
150. JDBC Drivers (About Database Adapters)docs.oracle.com
151. Data warehouse with a domain fact tableimage-ppubs.uspto.gov
152. Star Schema vs Snowflake Schema: 10 Key Differencesintegrate.io
153. Star and Snowflake Schemasoracle.com
154. Verticaen.wikipedia.org
155. ColumnStore Storage Architecturemariadb.com
156. Columnar Databases: Why They're Faster for Analyticshackernoon.com
157. 7 best columnar database tools for 2026 - Guideflow Blogguideflow.com
158. Apache Hiveen.wikipedia.org
159. Apache Hive : Hive on Sparkhive.apache.org
160. Comparing Foundational Features of Trino, Hive & Sparkstarburst.io
161. Apache Hive vs Spark SQL: Feature wise comparison - DataFlairdata-flair.training
162. Intensional RDB Manifesto: a Unifying NewSQL Model for Flexible Big Dataarxiv.org
163. Graph Database vs NoSQL: Where Graph Fitstigergraph.com
164. NoSQL database types (document, key-value, column, graph) in DBMS Theory - Explained with Diagrams & Examplesleyaa.ai
165. 4 Types of NoSQL Databasesblazeclan.com
166. Types Of Databasesmongodb.com
167. Distributed SQL vs. NewSQL: Navigating the Evolution of Scalable Database - Liam ERDliambx.com
168. About Apache Cassandra - DataStax Docsdocs.datastax.com
169. What is NewSQL? |NewSQL Defineddremio.com
170. NewSQL Databases: Distributed SQL for Real-Time Applicationsconduktor.io
171. 0% found this document useful (0 votes)scribd.com
172. Data Modeling In NoSQLmeegle.com
173. CQLdocs.datastax.com
174. SEC-NoSQL: Towards Implementing High Performance Security-as-a-Service for NoSQL Databasesarxiv.org
175. MCS-SQL: Leveraging Multiple Prompts and Multiple-Choice Selection For Text-to-SQL Generationarxiv.org
176. CodeS: Towards Building Open-source Language Models for Text-to-SQLarxiv.org
177. ReEx-SQL: Reasoning with Execution-Aware Reinforcement Learning for Text-to-SQLarxiv.org
178. Evaluating Cross-Domain Text-to-SQL Models and Benchmarks Mohammadreza Pourrezaarxiv.org
179. Blar-SQL: Faster, Stronger, Smaller NL2SQLarxiv.org
180. Semantic Layers for Reliable LLM-Powered Data Analytics: A Paired Benchmark of Accuracy and Hallucination Across Three Frontier Modelsarxiv.org
181. Text-to-SQL AI: How the Pipeline Works and Breaks [2026]atlan.com
182. A Survey on Employing Large Language Models for Text-to-SQL Tasksarxiv.org
183. Enhancing Text-to-SQL through SQL Parsing and Reasoningaclanthology.org
184. Why Text-to-SQL Fails on Real Enterprise Schemas (A 10% Accuracy Case Study)medium.com
185. (PDF) SQL Injection in LLM-Generated Queries: Systematic Analysis of Detection Gaps and Security Risksresearchgate.net
186. LLM Prompt Injection Prevention - OWASP Cheat Sheet Seriescheatsheetseries.owasp.org
187. From 68% to ~100%: How We Built a Text-to-SQL System That Gets Smarter Every Day - DEV Communitydev.to
188. When Text Becomes Code: Defending LLM–Database Integrations from Prompt Injection - DEV Communitydev.to
189. Rethinking Schema Linking: A Context-Aware Bidirectional Retrieval Approach for Text-to-SQLarxiv.org