Kathuko › SQL › A–Z

A–Z of Oracle SQL

Every statement, clause and function the guide covers. 140 of 141 have their pages; anything still marked coming needs a database feature we can't test yet.

A

  • AFTER MATCH SKIP 19c+where the next match starts · Clause6.6
  • ALTER TABLE 19c+change a table · Statement10.0 10.2
  • Annotations 26ai onlyname/value labels on objects · Clause12.7
  • ANY_VALUE 19c+any one value from a group · Function3.3
  • APPROX_COUNT_DISTINCT 19c+fast approximate distinct count · Function1.5
  • AVG 19c+average · Function3.0 4.0 5.6

B

  • BETWEEN 19c+a range, ends included · Operator1.1
  • BOOLEAN 26ai onlyTRUE, FALSE and boolean columns · Type12.1

C

  • CASE 19c+if/then in SQL · Function1.4 3.3 7.0 12.1
  • CEIL / FLOOR for dates 26ai onlyround a date up or down · Function12.8
  • CLASSIFIER / MATCH_NUMBER 19c+labels for matched rows · Function6.3
  • COALESCE 19c+first non-null · Function1.2
  • COMMENT ON 19c+describe a table or column · Statement12.7
  • COMMIT / ROLLBACK / SAVEPOINT 19c+transactions · Statement9.0
  • CONNECT BY / START WITH 19c+Oracle's hierarchical queries · Clause4.1 13.0
  • Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) 19c+rules on data · Clause10.0
  • Correlated subquery 19c+a subquery that refers to the outer row · Clause4.0 5.1 5.7 9.2
  • COUNT 19c+count rows or values · Function1.5 3.0 3.3 4.2
  • CREATE INDEX 19c+faster lookups · Statement10.3 14.1
  • CREATE SEQUENCE 19c+number generators · Statement10.1
  • CREATE TABLE 19c+make a table · Statement10.0 10.1 10.4
  • CREATE TABLE AS SELECT 19c+make a table from a query · Statement10.2 10.3
  • CREATE VIEW 19c+a saved query · Statement10.3
  • CROSS APPLY / OUTER APPLY 19c+LATERAL, SQL Server style · Clause2.4 5.3 8.3
  • CROSS JOIN 19c+every combination · Clause2.4 7.1 13.3
  • CUBE 19c+every combination of subtotals · Clause3.1

D

  • DECODE 19c+Oracle's older CASE · Function1.4
  • DEFINE 19c+what each pattern variable means · Clause6.0 6.1
  • DELETE 19c+remove rows · Statement9.4
  • DISTINCT 19c+remove duplicate rows · Clause1.5 3.2
  • Domains 26ai onlyreusable column types (coming: needs a database that allows them) · Clause12.7
  • DROP TABLE / PURGE 19c+remove a table · Statement10.2
  • DUAL 19c+the one-row table12.1

E

  • EXCEPT 26ai onlythe standard name for MINUS · Operator12.5
  • EXCLUDE 26ai onlyleave rows out of a frame · Clause12.4
  • EXISTS / NOT EXISTS 19c+semi and anti joins · Operator1.2 2.3 4.2 9.3 9.4 9.5 13.2
  • EXPLAIN PLAN / DBMS_XPLAN 19c+how Oracle runs a query · Statement14.0 14.1 14.2

F

  • FETCH FIRST / OFFSET 19c+top-n and paging · Clause2.4 6.5 8.0 8.1 8.2
  • FIRST_VALUE / LAST_VALUE / NTH_VALUE 19c+a value from the window · Function5.4
  • FULL OUTER JOIN 19c+keep unmatched rows from both · Clause2.1

G

  • GRAPH_TABLE / property graphs 26ai onlygraph queries in SQL (coming) · ClausePart 12, coming
  • GROUP BY 19c+one row per group · Clause1.4 1.5 2.2 3.0
  • GROUP BY alias / position 26ai onlygroup by a SELECT alias or column number · Clause12.2
  • GROUPING / GROUPING_ID 19c+which rows are subtotals · Function3.1
  • GROUPING SETS 19c+exactly the subtotals you list · Clause3.1
  • GROUPS frame 26ai onlyframes counted in groups of equal values · Clause12.4

H

  • HAVING 19c+keep some groups · Clause3.0 13.2

I

  • Identity columns 19c+auto-numbered keys · Clause10.0 10.1
  • IF [NOT] EXISTS 19c+DDL that doesn't fail (19.28 and later) · Clause12.7
  • IN / NOT IN 19c+a list of values · Operator1.1 1.2 2.3
  • INNER JOIN 19c+matching rows from both · Clause1.1 2.0 2.2 14.2
  • INSERT 19c+add rows · Statement9.0 9.1 9.3 9.5
  • INSERT ALL / INSERT FIRST 19c+one statement, several inserts · Statement9.1 12.3
  • INTERSECT / MINUS 19c+common rows, rows in one not the other · Operator1.2 2.3 4.2 12.5 13.2
  • IS JSON 19c+check that text is valid JSON · Operator11.1
  • IS NULL / IS NOT NULL 19c+test for null · Operator1.2
  • IS TRUE / IS NOT TRUE 26ai onlyboolean tests that handle null · Operator12.1

J

  • JSON data type 26ai onlynative JSON storage · Type11.2
  • JSON dot notation 19c+doc.innings[0].runs · Operator11.1 11.2
  • JSON Relational Duality views 26ai onlytables and documents at once · Statement11.2
  • JSON_OBJECT / JSON_ARRAYAGG 19c+build JSON · Function11.0
  • JSON_TABLE 19c+JSON to rows · Function11.1
  • JSON_VALUE / JSON_QUERY / JSON_EXISTS 19c+read JSON · Function11.1

K

  • KEEP (DENSE_RANK FIRST / LAST) 19c+the value from the first or last row of a group · Function3.3 5.4 6.1 6.8 12.2

L

  • LAG / LEAD 19c+the row before or after · Function5.3 6.3 6.4 6.6 13.1
  • LATERAL 19c+a subquery that sees the row before it · Clause2.4 7.1 8.3
  • LEAST / GREATEST 19c+smallest or largest of a list · Function13.3
  • LEFT / RIGHT OUTER JOIN 19c+keep unmatched rows from one side · Clause0.3 1.3 2.0 2.1 2.3
  • LIKE 19c+simple patterns in text · Operator1.1
  • LISTAGG 19c+join values into a string · Function3.2 7.2 11.0
  • LNNVL 19c+true when a condition is false or unknown · Function12.1
  • LOG ERRORS 19c+keep going past bad rows · Clause9.5

M

  • MATCH_RECOGNIZE 19c+regular expressions over rows · Clause6.0 6.1 6.8 6.11 13.1
  • MEASURES 19c+the columns for each match · Clause6.0 6.1 6.2
  • MERGE 19c+insert or update in one statement · Statement9.2 9.3 12.6
  • MIN / MAX 19c+smallest, largest · Function3.3
  • MINUS ALL / INTERSECT ALL 26ai onlyset operators that count duplicates · Operator12.5
  • Multi-row INSERT ... VALUES 26ai onlyseveral rows in one VALUES · Statement9.0 12.3

N

  • NATURAL JOIN / USING 19c+join on same-named columns · Clause2.0
  • NEXT_DAY / ADD_MONTHS / LAST_DAY 19c+date arithmetic · Function12.8
  • NTILE 19c+split into buckets · Function5.5
  • NULLIF 19c+null when equal · Function1.2
  • NULLS FIRST / NULLS LAST 19c+where nulls sort · Clause1.3
  • NUMTODSINTERVAL / EXTRACT 19c+intervals and their parts · Function12.8
  • NVL 19c+replace null · Function1.2
  • NVL2 19c+one value if null, another if not · Function1.2

O

  • ONE ROW / ALL ROWS PER MATCH 19c+summary or detail · Clause6.2 6.3
  • ORDER BY 19c+sort the result · Clause1.0 1.3
  • (+) outer join 19c+Oracle's old outer-join syntax · Operator2.1
  • OVER (PARTITION BY ... ORDER BY ...) 19c+window functions · Clause4.0 5.0

P

  • Partitioning 19c+split big tables · Clause10.4
  • PATTERN 19c+the row pattern · Clause6.0 6.1 6.4
  • Pattern exclusion {- -} 19c+match rows without returning them · Operator6.9
  • PERCENT_RANK / CUME_DIST 19c+relative position · Function5.5 8.2
  • PERMUTE 19c+variables in any order · Operator6.7
  • PIVOT 19c+rows to columns · Clause7.0 7.2
  • PREV / NEXT / FIRST / LAST 19c+navigation inside a match · Function6.5

Q

  • QUALIFY 26ai onlyfilter on a window function · Clause5.7 12.2

R

  • RANK / DENSE_RANK 19c+positions with ties · Function5.1 5.7 8.0 12.2
  • RATIO_TO_REPORT 19c+share of the total · Function5.0
  • Recursive WITH 19c+a query that refers to itself · Clause4.1 13.0
  • REGEXP_SUBSTR / REGEXP_LIKE / REGEXP_INSTR / REGEXP_COUNT 19c+regular expressions on text · Function1.6 11.3
  • RETURNING 19c+get values back from DML · Clause9.0 12.6
  • RETURNING OLD / NEW 26ai onlyvalues before and after the change · Clause12.6
  • ROLLUP 19c+subtotals and a grand total · Clause3.1
  • ROWID 19c+a row's physical address9.4
  • ROWNUM 19c+the old top-n8.0 8.1
  • ROWS / RANGE frames 19c+which rows a window covers · Clause5.2 5.4 5.6 12.4
  • ROW_NUMBER 19c+1, 2, 3 in order · Function5.1 5.3 5.7 6.1 8.0 8.1 8.2 9.4 12.2 13.1
  • RUNNING / FINAL 19c+measures as of each row or the whole match · Clause6.10
  • Running totals 19c+SUM() OVER (ORDER BY ...)4.1 5.2 6.1 6.5 6.8

S

  • SELECT 19c+read rows · Statement1.0
  • SELECT ... FOR UPDATE 19c+lock rows you're about to change · Clause9.2 12.6
  • SELECT without FROM 26ai onlyno more FROM dual · Statement12.1
  • Self join 19c+a table joined to itself · Clause13.3
  • SQL macros 19c+reusable query templates · Clause4.4
  • Subquery (scalar, inline view) 19c+a query inside a query · Clause2.1 4.0
  • SUBSET 19c+one name for several variables · Clause6.7
  • SUBSTR / INSTR / REPLACE / TRIM / LPAD 19c+everyday text functions · Function1.6
  • SUM 19c+add up · Function3.0 3.3 5.0 5.2 7.0
  • SUM / AVG of INTERVAL 26ai onlyadd up durations · Function12.8

T

  • TIME_BUCKET 26ai onlyfixed-width date buckets · Function12.8
  • TO_CHAR / TO_DATE / TO_NUMBER 19c+convert types · Function1.0 12.9
  • TRUNC / ROUND for dates 19c+round a date down · Function12.8
  • TRUNCATE 19c+empty a table fast · Statement9.4 10.2

U

  • UNION / UNION ALL 19c+stack two results · Operator3.1 4.2 5.3 6.11 7.1 12.3
  • UNPIVOT 19c+columns to rows · Clause7.1 7.2
  • UPDATE 19c+change rows · Statement9.2 9.3
  • UPDATE ... FROM / DELETE ... FROM 26ai onlyjoins in UPDATE and DELETE · Statement12.6

V

  • VALUES (table value constructor) 26ai onlyrows written into the query · Clause12.3
  • VECTOR 26ai onlyembeddings and similarity · Type12.9
  • VECTOR_DISTANCE / TO_VECTOR 26ai onlycompare vectors · Function12.9
  • Virtual columns 19c+computed columns · Clause10.1

W

  • WHERE 19c+keep some rows · Clause1.1
  • WINDOW clause 26ai onlyname a window once · Clause6.3 12.4
  • WITH (common table expression) 19c+name a subquery · Clause2.2 4.1 4.3 12.3
  • WITH FUNCTION 19c+PL/SQL inside a query · Clause4.3 4.4
  • WITH TIES 19c+keep rows tied with the last one · Clause8.0 8.3
  • WITH UNMATCHED ROWS 19c+show rows outside any match · Clause6.9

X

  • XMLELEMENT / XMLAGG / XMLFOREST 19c+build XML · Function11.3
  • XMLTABLE 19c+XML to rows · Function11.3