Retrieve data from one or more tables, views, or snapshots.
The syntax on this page should be read in conjunction with Analytic Features
Summary of Syntax:
SELECT [hint][DISTINCT] select_list
FROM table_list
[WHERE conditions]
[START WITH] [CONNECT BY]
[GROUP BY group_by_list]
[HAVING search_conditions]
[ORDER BY order_list [ASC | DESC] ]
[FOR UPDATE for_update_options]
Key:
select_list
A comma-separated list of table columns (or expressions) eg:column1, column2, column3 table.column1, table.column2 table.column1 Col_1_Alias, table.column2 Col_2_Alias schema.table.column1 Col_1_Alias, schema.table.column2 Col_2_Alias schema.table.* * expr1, expr2 (subquery [WITH READ ONLY | WITH CHECK OPTION [CONSTRAINT constraint]])In the above, table can be replaced with view or snapshot.
Using the * expression will return all columns.
If a Column Alias is specified this will appear as the column heading in SQL*Plus output.DISTINCT
Supress duplicate rows - display only the unique values.
Duplicate rows have matching values across every column (or expression) in the select_list.FROM table_list
Contains a list of the tables from which the result set data is retrieved.[schema.]{table | view | snapshot}[@dblink] [t_alias]When selecting from a table you can also specify Partition and/or Sample clauses (only for local tables, not remote objects via dblink) e.g.
[schema.]table [PARTITION (partition)] [SAMPLE (sample_percent)]If the SELECT statement involves more than one table, the FROM clause can also contain join specifications (SQL1992 standard).
WHERE search_conditions
A filter that defines the conditions each row in the source table(s) must meet to qualify for the SELECT. Only rows that meet the conditions will be included in the result set. The WHERE clause can also contain inner and outer join specifications (SQL1989 standard). e.g.WHERE tableA.column = tableB.column WHERE tableA.column = tableB.column(+) WHERE tableA.column(+) = tableB.columnGROUP BY group_by_list
The GROUP BY clause partitions the result set into groups.
The group_by_list can be one or more columns or expressions and can optionally include the CUBE / ROLLUP keywords for creating crosstab results.Heirarchical Queries
Any query that does *not* include a GROUP BY clause can include a CONNECT BY hierarchy clause:[START WITH condition] CONNECT BY conditionHAVING search_conditions
An additional filter - the HAVING clause acts as an additional filter to the grouped result rows - as opposed to the WHERE clause that applies to individual rows. The HAVING clause is most commonly used in conjunction with a GROUP BY clause.
ORDER BY order_list [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted ascending (1…9 a…z) or descending (9…1 z…a).You can sort by any column even if that column is not actually in the main SELECT clause. If you do not include an ORDER BY clause then the order of the result set rows will be unpredictable (random or quasi random).
To sort items by their average rating e.g. for product review scores, use the Lower bound of Wilson score confidence interval for a Bernoulli parameter.
FOR UPDATE options
This is often used within SL/SQL routines to lock the selected rows.
Oracle will wait for any locks to be released unless you specify NOWAITFOR UPDATE [OF [ [schema.]{table|view}.] column] [NOWAIT]Undocumented syntax:
SELECT… FOR UPDATE SKIP LOCKEDSkip Locked will return all the 'non-locked' rows and lock them. While this syntax can be used effectively, it is generally not a good idea to use it within an application as undocumented syntax might be removed or changed in a future release.
Writing a SELECT statement
The clauses (SELECT … FROM … WHERE … HAVING … ORDER BY … ) must be in this order.
SELECT Choose the columns to return.
FROM The data table(s) to search. Selecting multiple tables may greatly increase the search space and slow down the query.
WHERE Filter the data rows returned.
GROUP BY aggregate data.
HAVING Filter out aggregated data that doesn’t meet a given criteria.
UNION Merge the selected data into a result set.
ORDER BY Sort the results.
The position of commas and semicolons is not forgiving.
Each expression must be unambiguous. In other words if two columns have the same name, then either prefix the columns with the tablename (or use an alias).
SELECT DISTINCT
customer_id,
oi_ship_date
FROM
customers,
order_items
WHERE
customers.customer_id = order_items.customer_id
AND order_items.oi_ship_date > '01-may-2001';
Table names can also be qualified with the schema name (if you are
working with multiple schema's)
e.g. scott.t_customers.customer_id
SQL statements can be simplified, and made more readable by assigning a table alias (also known as a range variable or correlation name).
With a table alias the fully qualified name has to be specified only in the FROM clause. All other table/view references then use the alias name. e.g.
SELECT DISTINCT
cst.customer_id,
ord.oi_ship_date
FROM
customers cst,
order_items ord
WHERE
cst.customer_id = ord.customer_id
AND ord.oi_ship_date > '01-may-2001';
More complex queries can be written by combining several SELECT commands in a single SQL statement:
SELECT command { UNION | UNION ALL | INTERSECT | MINUS
} SELECT command
“At school, new ideas are thrust at you every day. Out in the world, you’ll have to find the inner motivation to search for new ideas on your own” ~ Bill Watterson
Related Oracle Commands:
Correlated SubQueries
DELETE
EXPLAIN PLAN
INSERT
TRUNCATE
UNION
UPDATE
DBMS_LOCK
DBMS_SQL
Analytic Features (8i and above)
Select Hints
SQL Examples - Oracle Co-Operative FAQ
SQL Reference Books
Related Views:
DBA_SNAPSHOTS ALL_SNAPSHOTS USER_SNAPSHOTS
DBA_ALL_TABLES ALL_ALL_TABLES USER_ALL_TABLES
DBA_TABLES ALL_TABLES USER_TABLES TAB
DBA_VIEWS ALL_VIEWS USER_VIEWS
DICTIONARY
DICT_COLUMNS