SQL Glossary · Your Library

Definitions follow the course’s MySQL-oriented examples. SQL behavior and syntax can differ between database products and versions; check the relevant dialect when portability matters.

A

ACID: Four properties associated with reliable transactions: Atomicity, Consistency, Isolation, and Durability. The exact guarantees depend on the database, its configuration, and how the application uses it.
Aggregate function: A function that summarizes values from multiple rows, often per group. Common examples are COUNT, SUM, AVG, MIN, and MAX.
Alias: A temporary name assigned to a column or table in a query with AS or, in many dialects, by placing the name after the expression. For example, SUM(amount) AS total names the result column total.
ALTER TABLE: A DDL statement for changing an existing table’s structure, such as adding, dropping, renaming, or modifying a column or constraint.
Atomicity: The A in ACID: a transaction’s work is treated as one unit, so it is committed together or rolled back according to the database’s transaction guarantees.
Autocommit: A mode in which each successful statement is committed as its own transaction unless an explicit transaction is open. MySQL normally enables autocommit by default.
Authorization: The process of deciding which actions a user or role may perform. In MySQL, privileges are commonly assigned with GRANT and removed with REVOKE.

B

BETWEEN: A range predicate that includes both endpoints: x BETWEEN low AND high is equivalent to x >= low AND x <= high for ordinary non-NULL comparisons.
Buffer pool: In InnoDB, an area of memory used to cache table and index pages. Data changes can be made to pages in memory before those pages are flushed to storage.

C

CASE expression: A SQL expression that returns a value based on the first matching WHEN condition. It can be used inside a SELECT, update, or other expression context.
CHAR_LENGTH: A string function that returns the number of characters in a string. This differs from MySQL LENGTH, which returns the number of bytes.
CHECK constraint: A rule on a table that accepts or rejects rows based on a condition, such as CHECK (age >= 15). Enforcement details can vary by database and version.
Collation: Rules used to compare and sort text, including whether comparisons distinguish case or accents. The chosen collation can affect equality, ordering, and pattern matching.
Column: A named attribute in a table, such as email in a users table. A column has a data type and may also have constraints or a default.
COMMIT: A transaction-control statement that completes the current transaction and makes its changes committed, subject to the database’s durability and isolation guarantees.
Consistency: The C in ACID: committed transactions should preserve the constraints and application invariants that the database enforces. The database does not automatically know every business rule.
Connection: A live communication session between an application or client and a database server. Session state and an open transaction belong to a particular connection.
Connection pool: A managed set of reusable database connections. A request must keep using the same checked-out connection for all statements in one transaction.
Constraint: A rule enforced on table data. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL.
Correlated subquery: A subquery that refers to a column from its outer query. Conceptually, its result is needed for each outer row, although an optimizer may transform the physical execution.
COUNT: An aggregate function. COUNT(*) counts rows; COUNT(expression) counts rows where the expression is not NULL; COUNT(DISTINCT expression) counts distinct non-NULL values.
Cross join: A join that returns every combination of rows from its two inputs. If one input has m rows and the other has n, the result has m × n rows.
CTE (Common Table Expression): A named query introduced with WITH and used by a statement. WITH RECURSIVE supports recursive CTEs where the database dialect allows them.

D

Data Control Language (DCL): A common SQL command category for controlling permissions, especially GRANT and REVOKE.
Data Definition Language (DDL): A common SQL command category for defining or changing database structures. Examples include CREATE, ALTER, DROP, and TRUNCATE.
Data Manipulation Language (DML): A common SQL command category for changing table rows, especially INSERT, UPDATE, and DELETE. Some references classify SELECT separately as DQL.
Data Query Language (DQL): A common learning category for statements that retrieve data, especially SELECT. SQL references do not all classify statements using the same categories.
Data type: A definition of the kind of value a column can store, such as INT, VARCHAR, DATE, BOOLEAN, or DECIMAL. Available types and their behavior vary by database.
Database: An organized collection of data. In everyday speech, “database” can also refer informally to the software that manages the data; the software is more precisely the DBMS.
Database management system (DBMS): Software that defines, stores, retrieves, and protects data, and manages concerns such as access, concurrent work, and recovery.
DELETE: A DML statement that removes selected rows from a table. Without a WHERE clause, it can remove every row while leaving the table itself in place.
Derived table: A subquery used as a table expression, commonly in a FROM clause. MySQL requires a derived table to have an alias.
DISTINCT: A keyword that removes duplicate rows from a query result, or duplicate values from the selected expressions. It applies to the complete selected row, not just one named column unless only that column is selected.
Durability: The D in ACID: after a transaction commits, its changes are intended to survive failures covered by the database’s durability guarantees and configuration.

E

Entity: A kind of real-world thing represented in a data model, such as a student, product, or order. A relational design often gives each entity type its own table.
EXISTS: A predicate that is true when its subquery returns at least one row. The values selected by the inner query are not used; SELECT 1 is conventional.
Execution plan: A database’s chosen strategy for producing a query result, including operations such as scans, index lookups, and joins. EXPLAIN can show a plan in MySQL.

F

Foreign key: A constraint linking columns in one table to a key in another table. It helps enforce referential integrity between related rows.
FULL OUTER JOIN: A join that preserves matching rows and unmatched rows from both inputs, filling missing-side columns with NULL. MySQL does not provide a direct FULL OUTER JOIN syntax; a workaround can combine left and right joins.
Function: A named operation that takes arguments and returns a value. SQL includes aggregate functions, which summarize rows, and single-row functions, which operate on values for individual rows.

G

GROUP BY: A clause that groups rows sharing values in specified expressions, typically so aggregate functions can calculate a result per group.
GRANT: A statement that gives privileges or roles to a MySQL account or role at a chosen scope, such as one table, one database, or globally.

H

HAVING: A clause that filters groups, usually after GROUP BY and aggregate calculations. WHERE filters input rows before grouping.
Host (MySQL account): The host portion of a MySQL account name. 'user'@'localhost' and 'user'@'%' are distinct accounts; the host part affects which connections match the account.

I

Index: A data structure that can help a database find rows without scanning an entire table. Indexes can improve some reads but use storage and add work to data changes.
INNER JOIN: A join that returns rows satisfying the join condition. Rows without a match on either side are excluded.
InnoDB: MySQL’s commonly used transactional storage engine. It supports transactions, foreign keys, row-level locking, and multi-version concurrency control.
INSERT: A DML statement that adds rows to a table. Naming the target columns explicitly makes the intended value-to-column mapping clear.
Isolation: The I in ACID: controls how concurrent transactions interact and what changes they can observe. The behavior depends on the database’s isolation level.

J

JOIN: A clause that combines rows from tables or other query results according to a join condition. Common forms include INNER, LEFT, RIGHT, CROSS, and FULL OUTER JOIN where supported.

K

Key: A column or set of columns used to identify rows or relate tables. A primary key identifies rows in its table; a foreign key references a key in another table.

L

LEFT JOIN: A join that preserves every row from its left input and adds matching right-side values. If no right-side row matches, right-side columns are NULL.
LIKE: A predicate for pattern matching. % matches zero or more characters and _ matches exactly one character; escaping rules can depend on the SQL dialect and settings.
LIMIT: A MySQL clause that restricts how many rows a query returns. Pair it with ORDER BY when the intended top or bottom rows must be deterministic.
Logical query processing order: A conceptual order for understanding clauses: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then LIMIT. It describes logical meaning, not necessarily the optimizer’s physical execution steps.

M

MySQL: A relational database management system that uses SQL with its own syntax and behavior. The course labels MySQL-specific examples where portability matters.
MVCC (Multi-Version Concurrency Control): A concurrency-control technique that keeps multiple row versions so transactions can read a consistent version while other transactions make changes. Implementation details vary.

N

NATURAL JOIN: A join that automatically matches columns with the same names. It can become fragile when schemas change, so explicit join conditions are often clearer.
NOT EXISTS: A predicate that is true when its subquery returns no matching rows. It is commonly used to find rows with no related record and to express “for all” patterns.
NOT NULL: A column constraint that disallows NULL values in stored rows.
NULL: A marker for missing or unknown data, not the number zero or an empty string. Use IS NULL or IS NOT NULL to test for it; ordinary comparisons with NULL evaluate to UNKNOWN.
NULLIF: A function that returns NULL when its two arguments compare equal; otherwise it returns the first argument. It is often used to avoid division by zero, as in x / NULLIF(y, 0).
Normalization: The process of structuring relational tables to reduce problematic redundancy and update anomalies. Normal forms such as 1NF, 2NF, 3NF, and BCNF describe different design rules.

O

OLAP (Online Analytical Processing): A broad workload pattern focused on analysis, often involving scans and aggregations over large datasets.
OLTP (Online Transaction Processing): A broad workload pattern focused on day-to-day operations, often involving many small, concurrent reads and writes.
ORDER BY: A clause that sorts query results by one or more expressions. Without it, row order is not guaranteed.
Optimizer: The DBMS component that chooses a strategy for executing a query. It may choose scans, indexes, join orders, or rewrite subqueries when it estimates an equivalent plan will work well.
Outer query: The query that contains another query, called a subquery. A correlated subquery can refer to values from the outer query.

P

PostgreSQL: An open-source relational database management system with SQL syntax and behavior that differ from MySQL in some areas.
Primary key: A constraint that uniquely identifies each row in a table. A primary key cannot contain NULL; it may consist of one column or multiple columns.
Privilege: Permission to perform an operation on a database object, such as SELECT on a table. Privileges can be granted directly to an account or through a role.
Query: A request to retrieve or manipulate data, commonly expressed as a SQL statement. A SELECT query describes which result rows and columns are wanted.

R

RDBMS (Relational Database Management System): A DBMS based on the relational model, which represents data using relations commonly presented as tables and supports querying with SQL.
REVOKE: A statement that removes previously granted privileges or role assignments.
ROLLBACK: A transaction-control statement that discards uncommitted changes in the current transaction, subject to the database’s transaction behavior.
Role: A named bundle of privileges that can be granted to accounts or other roles, making access management reusable.
Row: A record in a table containing values for that row’s columns. A row is sometimes called a tuple or record.
Row subquery: A subquery that returns one row containing multiple columns, for use where a row value or tuple is expected. Its number and order of columns must fit the comparison context.
RIGHT JOIN: A join that preserves every row from its right input and adds matching left-side values. If no left-side row matches, left-side columns are NULL.

S

SAVEPOINT: A named point inside a transaction. ROLLBACK TO SAVEPOINT undoes work after that point without ending the whole transaction.
Scalar subquery: A subquery used as one value and returning one column with at most one row. If it returns more than one row in a scalar context, the database reports an error.
Schema: The definition or structure of database objects, such as tables, columns, types, and constraints. In some DBMSs, “schema” also names a namespace that contains objects.
SELECT: A SQL statement for retrieving rows and expressions. A query can filter with WHERE, group with GROUP BY, and sort with ORDER BY.
Semi-join: A relational operation that returns rows from one input when a matching row exists in another input, without returning columns from that second input. EXISTS and IN queries can be implemented using semi-join strategies.
Subquery: A SELECT statement nested inside another SQL statement. It can return a scalar value, a row, a column of values, or a table-shaped result.
SUM: An aggregate function that adds non-NULL values in a set or group. If there are no non-NULL values, the result may be NULL depending on the expression and database behavior.

T

Table: A database object organized into columns and rows. Tables commonly represent a type of entity or relationship in a relational design.
Table subquery: A subquery that returns multiple rows and columns for use as a table expression, commonly in FROM. In MySQL, a derived table requires an alias.
TCL (Transaction Control Language): A common learning category for statements that control transactions, such as COMMIT, ROLLBACK, and SAVEPOINT.
Three-valued logic: SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN; comparisons involving NULL commonly produce UNKNOWN. WHERE retains rows only when its condition is TRUE.
Transaction: A logical unit of database work that can be committed or rolled back. Transaction boundaries and guarantees depend on the database and storage engine.
TRUNCATE: A DDL statement that removes all rows from a table while keeping the table structure. Its logging, identity-reset, and transaction behavior are database-specific.
Tuple: A row-like ordered collection of values. In SQL discussions, a table row is often described as a tuple; a row subquery can return a tuple of values.

U

UNION: A set operator that combines compatible SELECT results and removes duplicate rows. The columns must correspond in number and compatible types.
UNION ALL: A set operator that combines compatible SELECT results while retaining duplicate rows. It avoids the duplicate elimination performed by UNION.
UNIQUE constraint: A constraint that prevents duplicate values (or duplicate combinations of values) in the constrained column or columns. Handling of NULL under UNIQUE varies between DBMSs.
UPDATE: A DML statement that changes values in existing rows. A missing WHERE clause can update every row in the table.
UPSERT: A pattern that inserts a row or updates an existing row when a key conflict occurs. Syntax is dialect-specific; MySQL uses ON DUPLICATE KEY UPDATE and PostgreSQL uses ON CONFLICT.
User account: An identity used to authenticate to a database server and receive privileges. In MySQL, an account includes both a user name and a host, written as 'user'@'host'.

V

Value subquery: A subquery used to provide a value or list for comparison, such as a scalar result or the values used by IN.

W

WHERE: A clause that filters input rows according to a condition. It is applied before grouping in the logical query-processing model; conditions that filter groups belong in HAVING.
Window function: A function that calculates a result across related rows while retaining individual rows, using an OVER clause. Examples include ROW_NUMBER, RANK, and LAG.
Write-ahead log (WAL): A logging approach in which information needed for recovery is recorded before corresponding data pages are written. Recovery details vary by DBMS and storage engine.