Oracle SQL key terms

A two-dimensional structure of rows and columns that stores data about one kind of entity. Each column holds one attribute, and each row holds one complete record about one thing.

A column or combination of columns that uniquely identifies every row in a table. It cannot contain null values, and no two rows may share the same key value.

A column or set of columns whose values must match a primary or unique key value in another table, or be null. It enforces referential integrity between related tables.

A missing or unknown value in a column, distinct from zero or a blank space. Arithmetic involving null yields null, and nulls must be tested with IS NULL rather than equals.

A one-row, one-column table owned by Oracle, used to evaluate expressions or call functions when no real table data is needed, for example SELECT SYSDATE FROM DUAL.

A temporary name given to a column in a query result set. It changes only the displayed heading, not the table definition, and may be quoted to keep case or spaces.

A clause that filters individual rows before they are returned, keeping only those for which the condition is true. Rows whose condition is false or unknown are discarded.

A comparison operator that tests whether a value falls inside an inclusive range defined by two limits, so both the lower and the upper boundary values satisfy the condition.

A comparison operator that tests whether a value matches any member of a list of literals or a subquery result, acting as a compact alternative to chaining OR conditions.

A pattern-matching operator that compares character values using percent for any number of characters and underscore for exactly one character, with ESCAPE to match literal wildcards.

The clause that sorts the rows of a result set by one or more columns, aliases or expressions. It is applied after filtering and is normally the last clause of a query.

A function that works on one row at a time and returns exactly one result for each row processed, so the number of rows returned by the query does not change.

A single-row function that manipulates text, such as UPPER, LOWER, INITCAP, SUBSTR, LENGTH, INSTR, LPAD, TRIM and REPLACE, returning a modified value for each row.

A function such as TO_CHAR, TO_DATE or TO_NUMBER that explicitly changes a value from one data type to another, optionally using a format model and locale settings.

Automatic type conversion Oracle performs when an expression mixes incompatible data types, such as comparing a character column with a number. It can block index use and raise errors.

An expression that evaluates a list of conditions and returns the result of the first one that is true, allowing if-then-else logic inside SQL without procedural code.

An Oracle function that compares an expression against a series of search values and returns the matching result, otherwise a default value. It treats two nulls as equal.

A function that returns the first non-null argument in a list. It requires at least two arguments and is a standard, more flexible alternative to NVL for handling nulls.

An aggregate function such as COUNT, SUM, AVG, MIN or MAX that takes many rows and returns one summary value, ignoring nulls except when counting with COUNT(*).

A clause that collects rows into groups sharing the same values in the listed columns or expressions, so group functions can be applied to each group separately.

A clause that filters groups after grouping and aggregation have taken place, so it can test group function results, unlike WHERE, which filters individual rows beforehand.

A query operation that combines rows from two or more tables based on a related column or condition, producing one result set containing columns from each source table.

A join that returns only row combinations where the join condition is satisfied in both tables, discarding rows from either side that have no match.

A join that also returns unmatched rows from one or both tables, filling columns of the missing side with nulls. LEFT, RIGHT and FULL state which side is preserved.

A join in which a table is joined to itself. Two different aliases are required so the query can distinguish the separate roles the same table is playing.

A join with no join condition that pairs every row of one table with every row of another, producing a Cartesian product whose size is the product of the two row counts.

A SELECT statement nested inside another SQL statement that returns a value or set of values used by the outer query, typically within a WHERE or HAVING clause.

A subquery that references a column from the outer query, so it cannot be executed on its own. Oracle normally re-evaluates it for each candidate row of the outer query.

A subquery that returns more than one row. It must be compared using operators built for lists, such as IN, ANY, ALL or EXISTS, rather than a simple equals sign.

An operator such as UNION, INTERSECT or MINUS that combines the result sets of two or more independent queries. The queries must have matching column counts and compatible data types.

A set operator that appends the rows of one query result to another without removing duplicates, so it runs faster than UNION but may return repeated rows.

A DML statement that adds new rows to a table, either one row at a time using a VALUES clause or in bulk by copying the result of a subquery.

A DML statement that changes existing column values in rows that satisfy the WHERE clause. If no WHERE clause is supplied, every row in the table is modified.

A DML statement that removes rows satisfying the WHERE clause from a table. Unlike TRUNCATE it can be rolled back and fires triggers, but it does not release space immediately.

Transaction control statements. COMMIT makes all pending changes permanent and visible to other sessions, while ROLLBACK discards them and restores the state from before the transaction started.

A DDL statement that defines a new table along with its columns, data types and constraints. It takes effect immediately and is not part of a transaction that can be rolled back.

A named, stored query that presents data from one or more tables as though it were a table. It holds no data itself and can limit the rows and columns a user sees.

A schema object that generates a series of unique numbers on demand, commonly used to supply primary key values through the NEXTVAL and CURRVAL pseudocolumns.

An alternative name for a table, view, sequence or other schema object that lets users reference it without qualifying the owner name or the full object name.

An optional schema object that keeps an ordered list of key values with pointers to table rows, allowing Oracle to locate rows quickly instead of scanning the whole table.