Oracle SQL cheat sheet

Logical clause evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY; ORDER BY is always applied last.

Arithmetic precedence: multiplication and division before addition and subtraction; parentheses override the default order.

The concatenation operator || joins character strings; when a NULL is concatenated it is treated as an empty string.

DISTINCT removes duplicate rows from the whole result set, not from a single column of the select list.

A column alias may be written with the optional AS keyword; an alias in double quotes preserves case and allows spaces.

Character literals and date literals must be enclosed in single quotation marks; double quotes are reserved for identifiers.

Arithmetic involving a NULL value evaluates to NULL, and NULL is never equal to anything, including another NULL.

The DESCRIBE (DESC) command lists a table's column names, data types, and nullability without returning any rows.

WHERE filters individual rows before any grouping; column aliases defined in SELECT cannot be referenced in WHERE.

BETWEEN a AND b is inclusive of both endpoints and is equivalent to col >= a AND col <= b.

IN (list) is shorthand for a chain of OR conditions; NOT IN returns no rows when the list contains a NULL.

LIKE uses % for any sequence of characters including none and _ for exactly one character; the ESCAPE clause treats wildcards literally.

Use IS NULL or IS NOT NULL to test for nulls; a comparison with = NULL is never true and returns no rows.

ORDER BY sorts ascending by default; Oracle places NULLs last for ascending order and first for descending order unless NULLS FIRST or NULLS LAST is specified.

ORDER BY accepts a column name, a column alias, an ordinal position number, or an expression.

AND has higher precedence than OR, so mixing them without parentheses can produce unintended filtering logic.

A single-row function returns one result per row processed and can be nested inside another single-row function.

Character functions include UPPER, LOWER, INITCAP, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, REPLACE, and CONCAT.

SUBSTR(char, position, length) uses a 1-based position; a negative position counts backward from the end of the string.

Numeric functions include ROUND(n, d), TRUNC(n, d), MOD(m, n), CEIL, FLOOR, and ABS.

Date functions include SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC.

Date arithmetic: date + number yields a date, date - date yields the number of days between the two dates.

NVL(expr, replacement) substitutes a value when the expression is NULL and returns the original expression otherwise.

MONTHS_BETWEEN returns a fractional number of months; the result is negative when the first date precedes the second.

TO_CHAR(date, 'fmt') and TO_CHAR(number, 'fmt') convert to character; TO_DATE(char, 'fmt') and TO_NUMBER(char) convert to date and number.

Number format elements include 9 (width), 0 (leading or trailing zero), $, L (local currency), and the group separator comma.

Date format elements include YYYY, MM, DD, MON, DY, and HH24:MI:SS; the format must match the input string for TO_DATE.

NVL2(expr, value_if_not_null, value_if_null) returns the second argument when expr is not NULL and the third when it is NULL.

NULLIF(a, b) returns NULL when a equals b, otherwise returns a; it requires at least two arguments.

COALESCE returns the first non-NULL argument in the list; it handles more than two alternatives and is ANSI standard.

A searched CASE evaluates conditions in order and returns the result of the first TRUE condition; a simple CASE compares one expression to listed values.

DECODE compares by equality only and treats two NULLs as equal, whereas CASE uses standard NULL comparison semantics.

Group functions (AVG, SUM, MIN, MAX, COUNT, STDDEV, VARIANCE) return one result per group and ignore NULL values.

COUNT(*) counts every row in the group; COUNT(column) counts only rows where that column is not NULL.

Every column in the SELECT list that is not inside a group function must appear in the GROUP BY clause, otherwise ORA-00979 is raised.

WHERE filters rows before grouping; HAVING filters groups after aggregation and may reference group functions.

Group functions cannot be used in a WHERE clause; nesting is allowed only when the inner function is also a group function.

A column alias cannot be referenced in WHERE or GROUP BY but can be referenced in ORDER BY.

Grouping by multiple columns creates one group for each distinct combination of the listed column values.

COUNT(DISTINCT col) counts distinct non-NULL values of the column within each group.

A NATURAL JOIN joins automatically on all columns having the same name and compatible data types, and those columns appear once.

A join with USING(col) requires the column name to be identical in both tables, and the column cannot be qualified with a table alias.

An ON clause permits arbitrary join conditions; if it is omitted or invalid, the result is a Cartesian product of m x n rows.

A LEFT OUTER JOIN returns all rows of the left table, with NULLs in right-table columns when no match exists.

A self join requires two different table aliases for the same table so that the two roles can be distinguished.

A non-equijoin uses comparison operators such as BETWEEN or < rather than equality to relate the two tables.

Oracle's legacy outer-join syntax places the (+) operator on the side of the table that supplies NULL-extended rows.

A FULL OUTER JOIN returns unmatched rows from both tables, with NULLs in each side where no match is found.

A single-row subquery returns one row and is compared with =, >, <, >=, <=, or <>; multiple returned rows raise ORA-01427.

A multi-row subquery must be used with IN, ANY, or ALL rather than a single-row comparison operator.

col > ANY (subquery) means greater than the minimum returned value; col > ALL (subquery) means greater than the maximum.

Subqueries may appear in the SELECT list, FROM clause, WHERE clause, and HAVING clause of the outer query.

A correlated subquery references a column from the outer query and is re-evaluated for each candidate row of the outer query.

EXISTS returns TRUE when the subquery produces at least one row; NOT EXISTS avoids the NULL pitfalls of NOT IN.

A subquery in the FROM clause is called an inline view and must be given an alias.

If a subquery returns a NULL, NOT IN against it produces no rows because each comparison evaluates to UNKNOWN.

UNION returns all distinct rows from both queries; UNION ALL returns all rows including duplicates and does not sort.

INTERSECT returns rows common to both queries; MINUS returns distinct rows in the first query that are absent from the second.

Each SELECT in a compound query must return the same number of columns with compatible data types.

Column headings of the compound result come from the first SELECT statement only.

Only one ORDER BY clause is permitted and it must appear at the end of the whole compound query.

INTERSECT has higher precedence than UNION and MINUS; parentheses override the default precedence.

NULL values are treated as equal when set operators compare rows, unlike ordinary comparison predicates.

The number of rows returned by MINUS depends only on rows in the first query that do not appear in the second.

DML statements are INSERT, UPDATE, DELETE, and MERGE; their effects persist only after COMMIT and can be undone with ROLLBACK.

An UPDATE with no WHERE clause modifies every row in the table; always test the WHERE clause with a SELECT first.

INSERT INTO t (columns) VALUES (...) inserts one row; INSERT INTO t SELECT ... copies rows from another query.

DELETE FROM t WHERE condition removes matching rows; TRUNCATE is DDL, removes all rows, and cannot be rolled back.

A transaction begins with the first DML statement and ends with COMMIT, ROLLBACK, or an implicit commit caused by DDL.

ROLLBACK TO savepoint undoes only the statements issued after the named savepoint, keeping earlier changes pending.

Rows changed by DML remain locked until the transaction ends, blocking conflicting DML from other sessions.

A MERGE statement performs conditional INSERT, UPDATE, or DELETE on a target table using rows from a source.

CREATE TABLE defines columns and data types; ALTER TABLE supports ADD, MODIFY, DROP COLUMN, and SET UNUSED.

Common data types: NUMBER(p,s), VARCHAR2(n), CHAR(n), DATE, TIMESTAMP, CLOB, and BLOB.

CREATE VIEW or CREATE OR REPLACE VIEW stores a query; views containing group functions, DISTINCT, or GROUP BY are not directly updatable.

Sequence pseudo-columns are NEXTVAL and CURRVAL; CURRVAL is valid only after NEXTVAL has been referenced in the current session.

CREATE PUBLIC SYNONYM name FOR object provides a global alias; without PUBLIC the synonym is private to the schema.

Oracle creates a unique index automatically for PRIMARY KEY and UNIQUE constraints; indexes speed queries but slow DML.

All DDL statements commit implicitly, and TRUNCATE TABLE removes all rows without generating rollback data.

DROP TABLE ... PURGE bypasses the recycle bin; SET UNUSED makes a column invisible to SQL without physically removing it in the same operation.