Here is an Oracle SQL Dictionary, describing the concepts and with reference to names in other dialects and database languages if applicable. It is sortable, searchable and filterable with the help of keywords.
| Category | Concept / Query | Other Names / Databases | Plain English Description | Use Case | Keywords |
|---|---|---|---|---|---|
| Data Retrieval | SELECT | Same in all SQL | Choose which columns to display from a table | Getting specific data from a table | select, query, retrieve, columns, display |
| Data Retrieval | FROM | Same in all SQL | Specify which table to query | Identifying the source table for your data | from, table, source |
| Data Retrieval | WHERE | Same in all SQL | Filter rows based on conditions | Narrowing down results to specific criteria | where, filter, condition, criteria |
| Data Retrieval | DISTINCT | Same in all SQL | Remove duplicate rows from results | Getting unique values only | distinct, unique, duplicates, remove |
| Data Retrieval | FETCH FIRST | LIMIT (PostgreSQL, MySQL), TOP (SQL Server) | Restrict number of rows returned | Getting top N results | fetch, limit, top, restrict, top n |
| Data Retrieval | ROWNUM | ROW_NUMBER() (Standard SQL) | Oracle-specific row numbering for limiting results | Legacy way to limit rows in Oracle | rownum, limit, row number, legacy |
| Sorting | ORDER BY | Same in all SQL | Sort results in ascending or descending order | Organizing data by specific column values | order, sort, asc, desc, ascending, descending |
| Sorting | ASC | Same in all SQL | Sort in ascending order (A-Z, 1-10, oldest-newest) | Default sorting from lowest to highest | asc, ascending, low to high, a to z |
| Sorting | DESC | Same in all SQL | Sort in descending order (Z-A, 10-1, newest-oldest) | Sorting from highest to lowest | desc, descending, high to low, z to a |
| Filtering | Comparison Operators | Same in all SQL | Use =, !=, <, >, <=, >= to compare values | Filtering based on numeric or text comparisons | equals, not equal, greater, less, comparison |
| Filtering | BETWEEN | Same in all SQL | Filter values within a range (inclusive) | Finding values between two endpoints | between, range, from to, inclusive |
| Filtering | IN | Same in all SQL | Filter rows matching any value in a list | Checking if value exists in specified set | in, list, match any, multiple values |
| Filtering | LIKE | Same in all SQL | Pattern matching with wildcards (% and _) | Searching for text patterns | like, pattern, wildcard, percent, contains |
| Filtering | IS NULL | Same in all SQL | Check if value is NULL (missing/unknown) | Finding rows with missing data | null, missing, empty, is null |
| Filtering | IS NOT NULL | Same in all SQL | Check if value has data (not NULL) | Finding rows with populated data | not null, has value, populated, exists |
| Logical Operators | AND | Same in all SQL | Combine conditions (all must be true) | Requiring multiple conditions to be met | and, both, all conditions, combine |
| Logical Operators | OR | Same in all SQL | Combine conditions (at least one must be true) | Requiring any one of several conditions | or, either, any condition, alternative |
| Logical Operators | NOT | Same in all SQL | Negate a condition | Excluding specific criteria | not, negate, exclude, opposite |
| NULL Handling | NVL | IFNULL (MySQL), ISNULL (SQL Server), COALESCE (Standard) | Replace NULL with default value (2 args) | Converting NULL to usable value | nvl, null value, replace null, default |
| NULL Handling | NVL2 | Oracle-specific | Return different values for NULL vs NOT NULL (3 args) | Conditional handling based on NULL status | nvl2, null if, conditional null |
| NULL Handling | COALESCE | Same in all SQL | Return first non-NULL value from list | Checking multiple columns for first available value | coalesce, first non-null, fallback, multiple |
| NULL Handling | NULLIF | Same in all SQL | Return NULL if two values are equal | Converting specific values to NULL | nullif, convert to null, equal to null |
| Aggregation | COUNT | Same in all SQL | Count number of rows | Counting total records or non-NULL values | count, total, number, rows |
| Aggregation | SUM | Same in all SQL | Add up numeric values | Calculating totals | sum, total, add, aggregate |
| Aggregation | AVG | Same in all SQL | Calculate average of numeric values | Finding mean value | avg, average, mean |
| Aggregation | MIN | Same in all SQL | Find minimum (smallest) value | Getting lowest value | min, minimum, smallest, lowest |
| Aggregation | MAX | Same in all SQL | Find maximum (largest) value | Getting highest value | max, maximum, largest, highest |
| Aggregation | COUNT(DISTINCT) | Same in all SQL | Count unique values only | Counting how many different values exist | count distinct, unique count, distinct |
| Grouping | GROUP BY | Same in all SQL | Organize rows into groups for aggregation | Summarizing data by category | group, group by, summarize, category |
| Grouping | HAVING | Same in all SQL | Filter groups after aggregation | Filtering aggregated results | having, filter groups, post-aggregate filter |
| Grouping | ROLLUP | Same in most SQL | Create subtotals and grand totals | Hierarchical summary reports | rollup, subtotals, grand total, hierarchy |
| Grouping | CUBE | Same in most SQL | Create all possible grouping combinations | Multi-dimensional analysis | cube, all combinations, cross-tab |
| Grouping | GROUPING SETS | Same in most SQL | Specify exact grouping combinations | Custom aggregation groupings | grouping sets, custom groups, specific combinations |
| Joins | INNER JOIN | JOIN (Standard SQL) | Return only matching rows from both tables | Combining related data from multiple tables | inner join, join, matching, intersection |
| Joins | LEFT JOIN | LEFT OUTER JOIN (Standard) | Return all from left table plus matches from right | Keeping all records from primary table | left join, left outer join, keep left, all from left |
| Joins | RIGHT JOIN | RIGHT OUTER JOIN (Standard) | Return all from right table plus matches from left | Keeping all records from secondary table | right join, right outer join, keep right, all from right |
| Joins | FULL OUTER JOIN | FULL JOIN (Standard) | Return all rows from both tables | Seeing everything from both tables | full outer join, full join, all from both, everything |
| Joins | CROSS JOIN | Same in all SQL | Cartesian product of two tables | Creating all possible combinations | cross join, cartesian product, all combinations |
| Joins | SELF JOIN | Same technique in all SQL | Join table to itself | Comparing rows within same table | self join, self-reference, employee-manager, hierarchy |
| Joins | ON | Same in all SQL | Specify join condition | Defining how tables relate | on, join condition, match on, relationship |
| Subqueries | Subquery | Nested Query, Inner Query (all SQL) | Query within another query | Using calculated results in main query | subquery, nested, inner query, sub-select |
| Subqueries | Scalar Subquery | Same in all SQL | Subquery returning single value | Adding calculated column to results | scalar, single value, calculated column |
| Subqueries | Inline View | Derived Table (Standard SQL) | Subquery in FROM clause | Creating temporary result set | inline view, derived table, subquery from, temporary |
| Subqueries | Correlated Subquery | Same in all SQL | Subquery referencing outer query | Comparing each row to its group | correlated, references outer, row-by-row |
| Subqueries | EXISTS | Same in all SQL | Check if subquery returns any rows | Testing for presence of related data | exists, check presence, any rows |
| Subqueries | NOT EXISTS | Same in all SQL | Check if subquery returns no rows | Testing for absence of related data | not exists, check absence, no rows |
| Subqueries | IN with subquery | Same in all SQL | Check if value matches any in subquery list | Filtering against multiple values | in, match any, list, multiple |
| Subqueries | NOT IN | Same in all SQL | Check if value matches none in subquery list | Excluding multiple values | not in, exclude, not match, none |
| Set Operators | UNION | Same in all SQL | Combine results, remove duplicates | Merging similar datasets into one unique list | union, combine, merge, remove duplicates |
| Set Operators | UNION ALL | Same in all SQL | Combine results, keep duplicates | Merging datasets faster without deduplication | union all, combine all, keep duplicates, fast |
| Set Operators | INTERSECT | Same in all SQL | Return only rows in both queries | Finding common elements between datasets | intersect, overlap, common, both, intersection |
| Set Operators | MINUS | EXCEPT (PostgreSQL, SQL Server) | Return rows in first query not in second | Finding differences between datasets | minus, except, difference, not in second |
| Window Functions | ROW_NUMBER | Same in all SQL | Assign unique sequential number to rows | Ranking with unique numbers, pagination | row_number, sequence, unique rank, number rows |
| Window Functions | RANK | Same in all SQL | Assign rank with gaps for ties | Ranking where ties get same rank | rank, ranking, gaps, ties |
| Window Functions | DENSE_RANK | Same in all SQL | Assign rank without gaps for ties | Ranking without gaps in sequence | dense_rank, ranking, no gaps, consecutive |
| Window Functions | NTILE | Same in all SQL | Divide rows into N buckets | Creating quartiles, percentiles, or groups | ntile, buckets, quartiles, percentiles, divide |
| Window Functions | LAG | Same in all SQL | Access previous row value | Comparing to previous record | lag, previous, prior row, look back |
| Window Functions | LEAD | Same in all SQL | Access next row value | Comparing to next record | lead, next, following row, look ahead |
| Window Functions | FIRST_VALUE | Same in all SQL | Get first value in window | Finding first value in partition | first_value, first, earliest in group |
| Window Functions | LAST_VALUE | Same in all SQL | Get last value in window | Finding last value in partition | last_value, last, latest in group |
| Window Functions | OVER | Same in all SQL | Define window for window function | Creating window for calculations | over, window, partition |
| Window Functions | PARTITION BY | Same in all SQL | Divide rows into groups for window function | Resetting calculations per group | partition, partition by, group window, reset |
| Window Functions | ROWS BETWEEN | Same in all SQL | Define exact window frame | Specifying moving or sliding windows | rows between, window frame, range, sliding |
| Window Functions | RANGE BETWEEN | Same in all SQL | Define value-based window frame | Creating windows based on value ranges | range between, value window, logical range |
| Conditional Logic | CASE | Same in all SQL | IF-THEN-ELSE logic in SQL | Creating conditional values | case, case when, if-then, conditional |
| Conditional Logic | CASE WHEN | Same in all SQL | Specify conditions in CASE statement | Defining IF conditions | case when, when, condition, if |
| Conditional Logic | THEN | Same in all SQL | Specify result when condition is true | Defining result for true condition | then, result, return |
| Conditional Logic | ELSE | Same in all SQL | Specify default result when no conditions match | Defining default/fallback value | else, default, otherwise, fallback |
| Conditional Logic | END | Same in all SQL | Close CASE statement | Completing CASE logic | end, close case, terminate |
| String Functions | CONCAT | Pipe Pipe (Oracle), CONCAT (Standard), Plus (SQL Server) | Combine strings together | Joining text values | concat, concatenate, combine, join strings |
| String Functions | SUBSTR | SUBSTRING (Standard SQL) | Extract portion of string | Getting part of text | substr, substring, extract, part of string |
| String Functions | LENGTH | LEN (SQL Server), LENGTH (PostgreSQL) | Get number of characters in string | Counting characters | length, len, count characters, string length |
| String Functions | UPPER | Same in all SQL | Convert text to uppercase | Making text all caps | upper, uppercase, caps, ucase |
| String Functions | LOWER | Same in all SQL | Convert text to lowercase | Making text all lowercase | lower, lowercase, lcase |
| String Functions | TRIM | Same in all SQL | Remove leading/trailing spaces | Cleaning up whitespace | trim, remove spaces, strip, whitespace |
| String Functions | LTRIM | Same in all SQL | Remove leading (left) spaces | Cleaning left whitespace | ltrim, left trim, remove left spaces |
| String Functions | RTRIM | Same in all SQL | Remove trailing (right) spaces | Cleaning right whitespace | rtrim, right trim, remove right spaces |
| String Functions | REPLACE | Same in all SQL | Replace text with different text | Substituting text values | replace, substitute, swap, change text |
| String Functions | INSTR | CHARINDEX (SQL Server), POSITION (PostgreSQL) | Find position of substring | Locating text within string | instr, position, find, locate, charindex |
| String Functions | LPAD | Oracle-specific | Pad string on left to specified length | Adding characters to left | lpad, left pad, pad left, fill left |
| String Functions | RPAD | Oracle-specific | Pad string on right to specified length | Adding characters to right | rpad, right pad, pad right, fill right |
| Date Functions | SYSDATE | GETDATE (SQL Server), NOW (MySQL), CURRENT_TIMESTAMP (Standard) | Get current date and time | Getting current system date/time | sysdate, now, current date, today, getdate |
| Date Functions | TO_DATE | STR_TO_DATE (MySQL), CONVERT (SQL Server) | Convert string to date | Parsing text into date format | to_date, parse date, convert to date, string to date |
| Date Functions | TO_CHAR | FORMAT (SQL Server), TO_CHAR (PostgreSQL) | Convert date to formatted string | Formatting dates for display | to_char, format date, date to string |
| Date Functions | ADD_MONTHS | DATEADD (SQL Server), DATE_ADD (MySQL) | Add months to date | Calculating future/past dates by months | add_months, add months, date math, future date |
| Date Functions | MONTHS_BETWEEN | DATEDIFF (SQL Server), age (PostgreSQL) | Calculate months between two dates | Finding duration in months | months_between, date difference, months apart, duration |
| Date Functions | TRUNC (date) | DATE_TRUNC (PostgreSQL), CAST (SQL Server) | Truncate date to specified unit | Removing time portion or rounding date | trunc, truncate date, round date, strip time |
| Date Functions | EXTRACT | Same in all SQL | Extract part of date (year, month, day) | Getting specific date component | extract, get year, get month, date part |
| Date Functions | LAST_DAY | Oracle-specific | Get last day of month | Finding month end date | last_day, end of month, month end |
| Date Functions | NEXT_DAY | Oracle-specific | Get next occurrence of day of week | Finding next Monday, Tuesday, etc | next_day, next weekday, day of week |
| Date Functions | ROUND (date) | Oracle date rounding | Round date to specified unit | Rounding dates to nearest day/month/year | round date, round, nearest date |
| Numeric Functions | ROUND | Same in all SQL | Round number to specified decimals | Rounding to decimal places | round, round number, decimals, precision |
| Numeric Functions | TRUNC (numeric) | FLOOR (Standard SQL) | Truncate number (cut off decimals) | Removing decimal portion | trunc, truncate, cut decimals, floor |
| Numeric Functions | CEIL | CEILING (Standard SQL) | Round up to next integer | Always rounding up | ceil, ceiling, round up |
| Numeric Functions | FLOOR | Same in all SQL | Round down to previous integer | Always rounding down | floor, round down |
| Numeric Functions | MOD | Percent (SQL Server), MOD (PostgreSQL) | Get remainder after division | Finding modulo/remainder | mod, modulo, remainder, division remainder |
| Numeric Functions | ABS | Same in all SQL | Get absolute value (remove negative sign) | Converting negative to positive | abs, absolute, positive, magnitude |
| Numeric Functions | SIGN | Same in all SQL | Get sign of number (-1, 0, 1) | Determining if positive or negative | sign, positive, negative, direction |
| Numeric Functions | POWER | Same in all SQL | Raise number to power | Calculating exponents | power, exponent, raise to power |
| Numeric Functions | SQRT | Same in all SQL | Calculate square root | Finding square root | sqrt, square root, root |
| Conversion Functions | TO_NUMBER | CAST (Standard), CONVERT (SQL Server) | Convert string to number | Parsing text as numeric | to_number, string to number, parse number |
| Conversion Functions | TO_CHAR | CAST (Standard), CONVERT (SQL Server) | Convert number/date to string | Formatting for display | to_char, number to string, format |
| Conversion Functions | CAST | Same in all SQL | Convert between data types | General type conversion | cast, convert, change type, data type |
| Conversion Functions | DECODE | CASE WHEN (Standard SQL) | Oracle IF-THEN-ELSE function | Conditional value mapping | decode, if-then, conditional, oracle case |
| Data Manipulation | INSERT | Same in all SQL | Add new rows to table | Creating new records | insert, add, create, new row |
| Data Manipulation | INSERT ALL | Oracle-specific | Insert multiple rows in one statement | Bulk inserting with different values | insert all, multi-insert, bulk insert |
| Data Manipulation | UPDATE | Same in all SQL | Modify existing rows | Changing data | update, modify, change, edit |
| Data Manipulation | DELETE | Same in all SQL | Remove rows from table | Deleting records | delete, remove, erase, drop rows |
| Data Manipulation | TRUNCATE | Same in all SQL | Quickly remove all rows (no rollback) | Fast table clearing | truncate, clear table, remove all, fast delete |
| Data Manipulation | MERGE | UPSERT (PostgreSQL), MERGE (SQL Server) | Insert or update in one operation | Handling updates or inserts conditionally | merge, upsert, insert or update, synchronize |
| Transaction Control | COMMIT | Same in all SQL | Permanently save changes | Making changes permanent | commit, save, permanent, finalize |
| Transaction Control | ROLLBACK | Same in all SQL | Undo changes since last commit | Canceling changes | rollback, undo, cancel, revert |
| Transaction Control | SAVEPOINT | Same in all SQL | Create intermediate save point | Partial rollback points | savepoint, checkpoint, partial save |
| Table Definition | CREATE TABLE | Same in all SQL | Define new table structure | Building new table | create table, define table, new table, ddl |
| Table Definition | ALTER TABLE | Same in all SQL | Modify existing table structure | Changing table structure | alter, modify table, change structure |
| Table Definition | DROP TABLE | Same in all SQL | Delete table completely | Removing table permanently | drop, delete table, remove table |
| Table Definition | RENAME | ALTER TABLE…RENAME (Standard SQL) | Change table name | Renaming table | rename, change name, rename table |
| Table Definition | COMMENT | Oracle-specific syntax | Add description to table/column | Documenting database objects | comment, description, documentation |
| Constraints | PRIMARY KEY | Same in all SQL | Unique identifier for each row | Uniquely identifying records | primary key, pk, unique id, identifier |
| Constraints | FOREIGN KEY | Same in all SQL | Link to another table | Creating relationships between tables | foreign key, fk, reference, relationship |
| Constraints | UNIQUE | Same in all SQL | Ensure all values are different | Preventing duplicates | unique, unique constraint, no duplicates, distinct values |
| Constraints | NOT NULL | Same in all SQL | Require value (no NULLs allowed) | Mandating data entry | not null, required, mandatory, must have value |
| Constraints | CHECK | Same in all SQL | Enforce business rules | Validating data | check, validation, business rule, constraint |
| Constraints | DEFAULT | Same in all SQL | Automatic value if none provided | Setting default values | default, default value, automatic value |
| Indexes | CREATE INDEX | Same in all SQL | Build index for faster queries | Speeding up searches | create index, index, performance, speed up |
| Indexes | DROP INDEX | Same in all SQL | Remove index | Deleting index | drop index, remove index, delete index |
| Indexes | UNIQUE INDEX | Same in all SQL | Index ensuring unique values | Enforcing uniqueness with performance | unique index, unique, index constraint |
| Views | CREATE VIEW | Same in all SQL | Create virtual table based on query | Creating reusable query | create view, view, virtual table, saved query |
| Views | CREATE MATERIALIZED VIEW | Oracle/PostgreSQL | Create physical cached result set | Pre-computing expensive queries | materialized view, mview, cached query, pre-compute |
| Views | DROP VIEW | Same in all SQL | Delete view | Removing view | drop view, delete view, remove view |
| Views | REFRESH | Oracle-specific | Update materialized view data | Refreshing cached data | refresh, update view, reload |
| Advanced | WITH | CTE (SQL Server), WITH (PostgreSQL) | Common Table Expression for temp results | Creating named temporary result sets | with, cte, common table expression, temp query |
| Advanced | RECURSIVE | WITH RECURSIVE (Standard SQL) | Self-referencing query | Traversing hierarchies | recursive, hierarchy, self-reference, tree |
| Advanced | CONNECT BY | Oracle-specific | Navigate hierarchical data | Oracle hierarchy traversal | connect by, hierarchy, tree, start with, prior |
| Advanced | START WITH | Oracle-specific | Define root of hierarchy | Starting point for hierarchy | start with, root, hierarchy start |
| Advanced | PRIOR | Oracle-specific | Reference parent in hierarchy | Parent-child relationship | prior, parent, hierarchy reference |
| Advanced | LEVEL | Oracle-specific | Depth in hierarchy | How many levels deep | level, depth, hierarchy level |
| Advanced | PIVOT | Oracle/SQL Server | Transform rows to columns | Creating crosstab reports | pivot, rows to columns, crosstab, spreadsheet |
| Advanced | UNPIVOT | Oracle/SQL Server | Transform columns to rows | Normalizing data | unpivot, columns to rows, normalize |
| Advanced | LISTAGG | STRING_AGG (PostgreSQL, SQL Server) | Concatenate values from multiple rows | Combining rows into delimited string | listagg, string_agg, concatenate rows, aggregate string |
| Advanced | EXPLAIN PLAN | EXPLAIN (PostgreSQL), SHOWPLAN (SQL Server) | Show query execution plan | Analyzing query performance | explain plan, execution plan, query plan, performance |
| Performance | ANALYZE | ANALYZE (PostgreSQL), UPDATE STATISTICS (SQL Server) | Update table statistics | Refreshing optimizer statistics | analyze, statistics, update stats, optimizer |
| Performance | HINT | Query hints in SQL Server | Suggest execution method to optimizer | Forcing specific execution path | hint, optimizer hint, force plan, performance tuning |
| Sequences | CREATE SEQUENCE | IDENTITY (SQL Server), SERIAL (PostgreSQL) | Create auto-incrementing number generator | Generating unique IDs | sequence, auto-increment, identity, serial, generator |
| Sequences | NEXTVAL | NEXT VALUE FOR (SQL Server), nextval (PostgreSQL) | Get next sequence value | Getting next ID | nextval, next value, increment, next id |
| Sequences | CURRVAL | Oracle-specific | Get current sequence value | Getting last generated ID | currval, current value, last id |
| System | DUAL | Not needed in other SQL | Oracle dummy table for SELECT | Running SELECT without real table | dual, dummy table, test query |
| System | USER | CURRENT_USER (Standard SQL) | Get current database username | Identifying logged-in user | user, current_user, username, session user |
| System | SYS_GUID | NEWID (SQL Server), gen_random_uuid (PostgreSQL) | Generate unique identifier | Creating GUIDs/UUIDs | sys_guid, guid, uuid, unique id, newid |