SQL (Structured Query Language) is a domain-specific programming language designed for managing and manipulating relational databases. Developed in the early 1970s by Donald D. Chamberlin and Raymond F. Boyce at IBM, SQL became the standard language for relational database management systems (RDBMS) following its adoption by the American National Standards Institute (ANSI) in 1986 and the International Organization for Standardization (ISO) in 1987. SQL enables users to perform tasks such as querying data, updating records, creating and modifying database schemas, and controlling access to data. It is widely used in applications ranging from small-scale projects to large enterprise systems, and its declarative nature allows users to specify what data they need without specifying how to retrieve it, leaving optimization to the database engine.

1 History

1.1 Origins at IBM

The origins of SQL trace to the early 1970s, when IBM researchers Donald D. Chamberlin and Raymond F. Boyce developed a language called SEQUEL (Structured English Query Language) as part of the System R project. System R was an experimental relational database management system designed to demonstrate the feasibility of the relational model proposed by Edgar F. Codd. The initial design of SEQUEL focused on a natural-language-like syntax that allowed non-programmers to query data. The name was later shortened to SQL due to trademark issues. The first implementation of SQL was used internally at IBM, and the language proved effective for data retrieval and manipulation.

1.2 Commercial adoption and standardization

IBM released its first commercial SQL-based product, SQL/DS (Structured Query Language/Data System), in 1981, followed by DB2 in 1983. The growing popularity of relational databases prompted other vendors to develop SQL implementations, including Oracle (then called Relational Software, Inc.), which released its first commercial SQL-based RDBMS in 1979. By the mid‑1980s, the need for a standardized version of SQL became apparent. In 1986, ANSI adopted SQL as a standard (ANSI X3.135‑1986), and ISO followed in 1987. This initial standard, often referred to as SQL‑86, defined the core language elements, including data definition, data manipulation, and basic query capabilities.

1.3 SQL:1999 and later revisions

Subsequent revisions expanded the language significantly. SQL:1992 (SQL‑92) introduced new data types, enhanced integrity constraints, and standardized the use of joins and outer joins. SQL:1999 added support for object‑relational features (e.g., user‑defined types, methods, and inheritance), triggers, recursive queries, and procedural language constructs. SQL:2003 introduced XML‑related features, window functions, and the MERGE statement. Later versions—SQL:2006, SQL:2011, SQL:2016, and SQL:2023—continued to add capabilities such as temporal tables, JSON support, and polymorphic table functions, keeping SQL relevant in modern data environments.

2 Language components

2.1 Data Definition Language (DDL)

DDL is the subset of SQL used to define, alter, and drop database objects such as tables, indexes, and views.

2.1.1 CREATE, ALTER, DROP

The CREATE statement creates new database objects. For example, CREATE TABLE defines a new table with specified columns and data types. ALTER modifies the structure of existing objects, such as adding, dropping, or renaming columns. DROP removes objects entirely, including their data if applicable.

2.1.2 Constraints (PRIMARY KEY, FOREIGN KEY, etc.)

Constraints enforce rules on data in tables. Common constraints include:

  • PRIMARY KEY: uniquely identifies each row in a table; ensures uniqueness and non‑null values.
  • FOREIGN KEY: enforces referential integrity by linking a column in one table to the primary key of another table.
  • UNIQUE: ensures all values in a column or set of columns are distinct.
  • NOT NULL: prevents null values in a column.
  • CHECK: validates that values satisfy a logical condition.

2.2 Data Manipulation Language (DML)

DML is used to retrieve, insert, update, and delete data in database tables.

2.2.1 SELECT

The SELECT statement retrieves data from one or more tables. It is the most commonly used SQL command, supporting filtering, sorting, grouping, and joining. Example: SELECT name, age FROM employees WHERE department = 'Sales' ORDER BY name;

2.2.2 INSERT, UPDATE, DELETE

  • INSERT adds new rows to a table. It can insert a single row using VALUES or multiple rows using a subquery.
  • UPDATE modifies existing rows based on a condition. For instance, UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering';
  • DELETE removes rows from a table. A WHERE clause specifies which rows to delete; omitting it removes all rows.

2.3 Data Control Language (DCL)

DCL manages permissions and access to database objects.

2.3.1 GRANT, REVOKE

  • GRANT assigns specific privileges (e.g., SELECT, INSERT, UPDATE, DELETE, EXECUTE) to users or roles on database objects.
  • REVOKE removes previously granted privileges. These commands are essential for database security.

2.4 Transaction Control Language (TCL)

TCL manages changes made by DML statements within a transaction, ensuring atomicity, consistency, isolation, and durability (ACID properties).

2.4.1 COMMIT, ROLLBACK, SAVEPOINT

  • COMMIT permanently saves all changes made during the current transaction.
  • ROLLBACK undoes all changes made since the last COMMIT or to a specified SAVEPOINT.
  • SAVEPOINT sets a named point within a transaction to which you can later roll back without affecting earlier changes.

3 SQL syntax and constructs

3.1 Basic query structure

A simple SQL query consists of three main clauses: SELECT, FROM, and optionally WHERE.

3.1.1 SELECT clause

The SELECT clause specifies the columns to be returned. It can include expressions, aggregates, aliases, and functions. Example: `SELECT first_name' 'last_name AS full_name, salary * 12 AS annual_salary`.

3.1.2 FROM clause

The FROM clause identifies the table(s) from which to retrieve data. It can reference a single table, multiple tables (with joins), or subqueries. Table aliases are often used for readability.

3.1.3 WHERE clause

The WHERE clause filters rows based on one or more conditions. Only rows that satisfy the condition are included in the result set. Example: WHERE hire_date >= '2020-01-01' AND department_id = 5.

3.2 Joins

Joins combine rows from two or more tables based on a related column.

3.2.1 INNER JOIN

INNER JOIN returns only rows where the join condition is met in both tables. Rows without a match are excluded.

3.2.2 OUTER JOIN (LEFT, RIGHT, FULL)

  • LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table; non‑matching right‑side columns are filled with NULL.
  • RIGHT JOIN is the symmetrical opposite.
  • FULL JOIN returns all rows from both tables, with NULL where no match exists.

3.2.3 CROSS JOIN and self-join

  • CROSS JOIN produces the Cartesian product of two tables—each row from the first table paired with every row from the second. It is rarely used intentionally.
  • A self‑join joins a table to itself, often using aliases, to compare rows within the same table (e.g., finding employees who report to the same manager).

3.3 Subqueries

A subquery is a SELECT statement nested inside another query. It can appear in the WHERE clause (e.g., WHERE salary > (SELECT AVG(salary) FROM employees)), the FROM clause (as a derived table), or the SELECT clause (as a scalar subquery). Subqueries can be correlated (referencing the outer query) or non‑correlated.

3.4 Set operations (UNION, INTERSECT, EXCEPT)

Set operations combine the results of two or more queries into a single result set, provided the queries have the same number and compatible data types of columns.

  • UNION returns distinct rows from both queries; UNION ALL includes duplicates.
  • INTERSECT returns only rows common to both queries.
  • EXCEPT (or MINUS in Oracle) returns rows from the first query that are not present in the second.

3.5 Predicates and operators

3.5.1 Comparison operators

Standard comparison operators include =, <> (or !=), <, >, <=, and >=.

3.5.2 Logical operators (AND, OR, NOT)

Logical operators combine or negate conditions. AND returns true if both conditions are true; OR returns true if at least one is true; NOT negates a condition.

3.5.3 LIKE, IN, BETWEEN, IS NULL

  • LIKE performs pattern matching using wildcards, e.g., % (any sequence) and _ (single character).
  • IN checks if a value matches any in a list or subquery, e.g., WHERE department_id IN (1, 3, 5).
  • BETWEEN specifies a range (inclusive), e.g., WHERE salary BETWEEN 40000 AND 60000.
  • IS NULL tests whether a value is null; IS NOT NULL tests the opposite.

4 Indexing and performance optimization

4.1 Types of indexes (B-tree, hash, bitmap)

Indexes speed up data retrieval at the cost of additional storage and slower writes.

  • B‑tree (balanced tree) index: the default in most RDBMS; efficient for range scans and equality searches.
  • Hash index: uses a hash function; best for exact‑match lookups but not for range queries.
  • Bitmap index: stores a bitmap for each distinct value; effective on columns with low cardinality (e.g., gender). Mainly used in data‑warehousing systems.

4.2 Query execution plans

A query execution plan is a sequence of steps (e.g., table scans, index scans, joins) that the database engine uses to execute a SQL statement. Analyzing execution plans helps identify performance bottlenecks. Modern databases provide tools (e.g., EXPLAIN in PostgreSQL, EXPLAIN PLAN in Oracle) to display these plans.

4.3 Normalization and denormalization

Normalization is the process of organizing data to reduce redundancy and dependency, typically by dividing tables into smaller, related tables (e.g., achieving Third Normal Form). Denormalization intentionally adds redundancy to improve read performance, often used in data warehouses or reporting systems.

5 SQL implementations and dialects

5.1 Major relational database systems

5.1.1 PostgreSQL

PostgreSQL is an open‑source, object‑relational database known for standards compliance, extensibility, and support for advanced features such as full‑text search, JSON, and custom data types.

5.1.2 MySQL

MySQL is a widely used open‑source RDBMS, popular for web applications. It emphasizes speed and ease of use, with support for storage engines like InnoDB (transactions) and MyISAM (read‑heavy).

5.1.2.1 MariaDB variant

MariaDB is a fork of MySQL created after Oracle’s acquisition of MySQL. It aims to maintain backward compatibility while adding new features, such as additional storage engines and improved performance optimizations.

5.1.3 Microsoft SQL Server

Microsoft SQL Server is a commercial relational database system for Windows and Linux environments. It offers tight integration with Microsoft ecosystem tools, business intelligence features, and the T‑SQL dialect with procedural extensions.

5.1.4 Oracle Database

Oracle Database is a commercial, multi‑model database known for scalability, high availability, and advanced security features. It uses PL/SQL for procedural programming and includes extensive support for data warehousing and analytics.

5.1.5 SQLite

SQLite is a self‑contained, serverless, zero‑configuration SQL engine often embedded in applications (mobile, desktop, browsers). It reads and writes directly to ordinary disk files, making it lightweight and portable.

5.2 Common extensions and proprietary features

Most database systems extend the SQL standard with proprietary features. Examples include Oracle’s CONNECT BY for hierarchical queries, PostgreSQL’s GIN and GiST indexes, SQL Server’s TOP and TRY_CAST, and MySQL’s LIMIT and ON DUPLICATE KEY UPDATE. These dialects, while powerful, can hinder portability between systems.

6 SQL in modern application development

6.1 Object-Relational Mapping (ORM) tools

ORMs such as Hibernate (Java), Entity Framework (.NET), SQLAlchemy (Python), and ActiveRecord (Ruby on Rails) abstract SQL into an object‑oriented paradigm. They generate SQL queries automatically from programming language code, reducing boilerplate and aiding portability. However, they can lead to inefficient queries if misused.

6.2 Embedded SQL and stored procedures

Embedded SQL mixes SQL statements directly into a host programming language (e.g., C, COBOL) via pre‑processing. Stored procedures are SQL code blocks stored in the database, allowing complex business logic to execute on the server side. Both approaches improve performance and security by reducing network round‑trips.

6.3 NoSQL and NewSQL comparisons

NoSQL databases (e.g., MongoDB, Cassandra) offer flexible schemas, horizontal scalability, and eventual consistency for unstructured or semi‑structured data, often at the expense of SQL‑like querying. NewSQL systems (e.g., Google Spanner, CockroachDB) aim to retain SQL and ACID guarantees while achieving the scalability of NoSQL. Despite the rise of non‑relational options, SQL remains the dominant language for structured data management.

7 Standardization and future directions

7.1 ANSI/ISO SQL standards (SQL:2016, SQL:2023)

The latest major standards are SQL:2016 and SQL:2023. SQL:2016 introduced support for JSON (functions, path expressions), row‑pattern recognition, listagg enhancements, and polymorphic table functions. SQL:2023 added further JSON capabilities, improved temporal support, and new property graph query features.

7.2 Emerging features (JSON, temporal tables, polymorphism)

  • JSON: native storage and querying of JSON documents, enabling hybrid relational‑document models.
  • Temporal tables: support for system‑versioned and application‑time periods, allowing easy querying of historical data.
  • Polymorphism: polymorphic table functions (PTFs) accept and return tables with arbitrary schemas, enabling reusable generic logic.

7.3 Cloud database services and managed SQL

Cloud providers offer managed SQL services (e.g., Amazon RDS, Google Cloud SQL, Azure SQL Database) that automate backups, patching, and scaling. Serverless offerings (e.g., Amazon Aurora Serverless, Google Cloud Spanner) further reduce administrative overhead. These services often extend standard SQL with cloud‑native features like automatic sharding and multi‑region replication, while still supporting the core SQL language.