Oracle SQL SELECT Statement Tutorial - Complete Guide with Examples

Oracle SQL | SELECT Statements

Sample Data Setup
SELECT Statement Structure and Clauses
Query Execution Order

Sample Data Setup

This table and sample data is used in the examples below:

CREATE TABLE table_select
(
    id1 NUMBER(2,0)
,   desc1 VARCHAR2(2)
);

INSERT INTO table_select (id1, desc1) VALUES (11, 'd1');
INSERT INTO table_select (id1, desc1) VALUES (12, 'd1');
INSERT INTO table_select (id1, desc1) VALUES (13, 'd3');
INSERT INTO table_select (id1, desc1) VALUES (21, 'd1');

COMMIT;

The table contains 4 rows with integer IDs and short description codes. This simple structure allows for demonstrating various SELECT operations including filtering, grouping, and ordering.

SELECT Statement Structure and Clauses
The SELECT statement is the primary tool for querying data in Oracle databases. Each clause serves a specific purpose in filtering, organizing, and presenting data. The clauses must appear in a specific order, though not all clauses are required for every query.
1. SELECT Clause
```
SELECT [DISTINCT] column_1, column_2, ..., column_n
```
  The SELECT clause specifies which columns or expressions to retrieve from the database. This clause determines the structure and content of the result set. It must be the first clause in any SELECT statement.
  
  A column in the SELECT clause can be:
  - any column from the tables specified in the FROM clause;
  - an aggregate function (COUNT, SUM, AVG, MIN, MAX, etc.);
  - a scalar subquery (must return at most one row with a single column);
  - any arithmetic or other valid expression (calculations, concatenations, functions);
  - literal values or constants;
  - pseudocolumns (ROWNUM, ROWID, etc.);
  Note that subqueries in the SELECT list can be re-executed for each row returned by the main query, which may impact performance for large result sets.
  
  The "DISTINCT" operator removes duplicate rows from the result set. It applies to the entire combination of all columns listed in the SELECT clause, not individual columns. Use DISTINCT carefully as it requires Oracle to sort or hash the result set, which can affect performance.
```
SELECT
        ROWNUM
,       t1.id1
,       NVL(t1.desc1, ' ') AS desc1
,       (
                SELECT  1
                FROM    DUAL
                WHERE   MOD(t1.id1, 2) = 0
        ) AS "another column"
FROM    table_select t1
```
  This example demonstrates multiple types of SELECT expressions: a pseudocolumn (ROWNUM), table columns (id1, desc1), a function (NVL), and a scalar subquery that returns 1 for even ID values and NULL for odd ones.
  
  Simple SELECT examples:
```
-- Select all columns
SELECT * FROM table_select;

-- Select specific columns
SELECT id1, desc1 FROM table_select;

-- Select with DISTINCT
SELECT DISTINCT desc1 FROM table_select;
```
2. FROM Clause
```
FROM
(
    [table|view|subquery]
    JOIN [table|view|subquery] ON (conditions)
)
```
  The FROM clause specifies the data sources for the query. This can include tables, views, materialized views, or subqueries. The FROM clause also defines how multiple data sources are joined together using various JOIN operations.
  
  Data sources in the FROM clause can be:
  - Physical tables stored in the database
  - Views (stored queries that act like tables)
  - Inline views (subqueries in the FROM clause)
  - Table functions
  - External tables
  When multiple tables are specified, Oracle supports various join types including INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN. Proper join conditions are essential for accurate results and optimal performance.
  
  Simple FROM examples:
```
-- Single table
SELECT * FROM table_select;

-- Table with alias
SELECT t.id1, t.desc1 FROM table_select t;

-- Using DUAL for testing expressions
SELECT SYSDATE FROM DUAL;
```
3. WHERE Clause
```
WHERE
(
    conditions
)
```
  The WHERE clause filters rows before any grouping occurs. It specifies conditions that must be met for a row to be included in the result set.
  
  WHERE conditions can include:
  - Comparison operators (=, !=, <, >, <=, >=)
  - Logical operators (AND, OR, NOT)
  - Pattern matching (LIKE, REGEXP_LIKE)
  - Range tests (BETWEEN, IN)
  - NULL tests (IS NULL, IS NOT NULL)
  - Existence tests (EXISTS with subqueries)
  Proper indexing on columns used in WHERE conditions can significantly improve query performance. Complex conditions should be written to take advantage of available indexes.
  
  Simple WHERE examples:
```
-- Filter by exact value
SELECT * FROM table_select WHERE desc1 = 'd1';

-- Filter by range
SELECT * FROM table_select WHERE id1 > 12;

-- Multiple conditions
SELECT * FROM table_select WHERE id1 >= 12 AND desc1 = 'd1';

-- Pattern matching
SELECT * FROM table_select WHERE desc1 LIKE 'd%';
```
4. GROUP BY Clause
```
GROUP BY
(
    column_1, column_2, ..., column_n
)
```
  The GROUP BY clause groups rows that have the same values in specified columns into summary rows. This clause is essential when using aggregate functions to calculate summary statistics for groups of data.
  
  Columns in the GROUP BY clause can be any column from the tables listed in the FROM clause, including columns not selected in the result set.
  
  It is not necessary for all columns in the GROUP BY clause to appear in the SELECT clause. However, all non-aggregate columns in the SELECT clause must appear in the GROUP BY clause, except for aggregate functions, constants, and subqueries that do not reference any columns from the FROM clause (except for columns already included in the GROUP BY clause).
  
  Oracle also supports advanced GROUP BY extensions including ROLLUP, CUBE, and GROUPING SETS for generating subtotals and cross-tabulations.
```
SELECT
        t1.desc1
,       COUNT(t1.desc1)
,       'CONST'
,       (SELECT 1 FROM DUAL)
FROM    table_select t1
GROUP BY t1.desc1
```
  This example groups rows by the desc1 column, counts occurrences in each group, and includes constant values that are allowed in SELECT with GROUP BY.
5. HAVING Clause
```
HAVING
(
    conditions
)
```
  The HAVING clause filters groups created by the GROUP BY clause. Unlike the WHERE clause which filters individual rows, HAVING filters groups based on aggregate values or group characteristics.
  
  HAVING conditions typically involve:
  - Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
  - Conditions on grouped columns
  - Complex expressions involving group-level calculations
  The HAVING clause is processed after GROUP BY, so it can reference aggregate functions and grouped columns. It cannot be used without a GROUP BY clause, as there would be no groups to filter.
  
  For optimal performance, filter individual rows using WHERE when possible, and use HAVING only for conditions that require group-level analysis.
  
  Simple HAVING examples:
```
-- Filter groups by count
SELECT desc1, COUNT(*) FROM table_select GROUP BY desc1 HAVING COUNT(*) > 1;

-- Filter groups by aggregate values
SELECT desc1, AVG(id1) FROM table_select GROUP BY desc1 HAVING AVG(id1) > 15;

-- Combine WHERE and HAVING
SELECT desc1, COUNT(*) FROM table_select WHERE id1 > 11 GROUP BY desc1 HAVING COUNT(*) >= 2;
```
6. ORDER BY Clause
```
ORDER BY column_1 [ASC|DESC], column_2 [ASC|DESC], ..., column_n [ASC|DESC]
```
  The ORDER BY clause sorts the final result set based on one or more columns or expressions. This clause is processed last and determines the sequence in which rows are returned to the client application.
  
  ORDER BY features include:
  - Multiple sort columns with different sort directions (ASC for ascending, DESC for descending)
  - Sorting by column positions (ORDER BY 1, 2) instead of column names
  - Sorting by expressions or functions
  - NULLS FIRST or NULLS LAST options to control NULL value positioning
  The default sort order is ascending (ASC), and NULL values are treated as the highest possible values by default. ORDER BY can reference any column in the SELECT list, including column aliases, and can also reference columns from the FROM clause that are not in the SELECT list.
  
  Simple ORDER BY examples:
```
-- Sort by single column (ascending by default)
SELECT * FROM table_select ORDER BY id1;

-- Sort by multiple columns
SELECT * FROM table_select ORDER BY desc1, id1 DESC;

-- Sort by column position
SELECT id1, desc1 FROM table_select ORDER BY 2, 1;

-- Sort by expression
SELECT id1, desc1 FROM table_select ORDER BY MOD(id1, 10);
```
Query Execution Order
Oracle processes clauses in a specific sequence, regardless of how they appear in the SQL text.

The logical execution order is:
1. FROM - Tables are identified and joins are performed.
2. WHERE - Individual rows are filtered based on conditions.
3. GROUP BY - Remaining rows are grouped by specified columns.
4. HAVING - Groups are filtered based on aggregate conditions.
5. SELECT - Columns and expressions are evaluated and result set is formed.
6. ORDER BY - Final result set is sorted.
This execution order explains why:
- Column aliases defined in SELECT cannot be used in WHERE, GROUP BY, or HAVING clauses.
- Aggregate functions cannot be used in WHERE clauses (use HAVING instead).
- ORDER BY can reference SELECT clause aliases since it executes after SELECT.
- HAVING can reference aggregate functions since it executes after GROUP BY.