Oracle SQL SELECT Fundamentals: Columns, Aliases, DISTINCT and Expressions

Oracle SQL SELECT Statement Fundamentals: Complete Guide

The SELECT statement is the foundation of every SQL query in Oracle. Whether you are retrieving data, performing calculations, or transforming results, understanding SELECT mechanics is essential for writing clear, efficient, and maintainable queries.

Learning Objectives
By the end of this guide you will master SELECT syntax, understand column aliases, DISTINCT behavior, arithmetic expressions, string concatenation, operator precedence, and best practices that professionals use every day.

1. SELECT Statement Structure and Mandatory Clauses

Only two clauses are mandatory. The remaining clauses are optional and must appear in a fixed order.

Clause	Required?	Purpose
SELECT	Yes	Columns or expressions to retrieve
FROM	Yes	Table(s) to retrieve from
WHERE	No	Filter rows before grouping
GROUP BY	No	Collapse rows into groups
HAVING	No	Filter groups after grouping
ORDER BY	No	Sort the final result

-- Minimum valid SELECT statement
SELECT last_name, salary
FROM   employees;

-- Full clause order (must appear exactly in this sequence)
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    salary > 5000
GROUP BY department_id
HAVING   AVG(salary) > 8000
ORDER BY department_id;

Important: Clause order is fixed. You cannot rearrange them (for example, putting ORDER BY before WHERE). This is one of the most common syntax mistakes.

2. Selecting All Columns vs Specific Columns

-- All columns
SELECT * FROM employees;

-- Specific columns in any order you prefer
SELECT last_name, employee_id, salary
FROM   employees;

-- Same column can appear multiple times
SELECT last_name, salary, salary * 12 AS annual_salary
FROM   employees;

3. Column Aliases — AS keyword is optional

-- With AS (recommended for clarity)
SELECT last_name        AS name,
       salary * 12      AS annual_salary
FROM   employees;

-- Without AS — space is enough
SELECT last_name        name,
       salary * 12      annual_salary
FROM   employees;

Key Rule: Column aliases defined in the SELECT clause cannot be used in WHERE, GROUP BY, or HAVING. They can only be referenced in ORDER BY.

4. Aliases with Spaces or Special Characters

-- Alias with space or special characters — use double quotes
SELECT salary * 12 AS "Annual Salary" FROM employees;

-- Preserving exact case or using special symbols
SELECT salary AS "Salary ($)" FROM employees;

Double quotes vs Single quotes
• Double quotes (" ") → for column aliases and identifiers
• Single quotes (' ') → for string literals only

5. Arithmetic Expressions and NULL Behavior

SELECT last_name,
       salary,
       salary * 12                          AS annual_sal,
       salary * 12 + NVL(commission_pct, 0) * salary * 12 AS total_annual
FROM   employees;

Important NULL Rule: Any arithmetic operation involving NULL returns NULL. Use NVL or COALESCE to provide default values when needed.

6. String Concatenation with ||

SELECT first_name || ' ' || last_name AS full_name
FROM   employees;

-- Oracle treats NULL as an empty string in concatenation
SELECT 'Hello' || NULL || ' World' FROM dual;  -- Returns: Hello World

7. DISTINCT — Removing Duplicate Rows

SELECT DISTINCT department_id FROM employees;

-- UNIQUE is a synonym for DISTINCT in Oracle
SELECT UNIQUE department_id FROM employees;

8. DISTINCT with Multiple Columns

SELECT DISTINCT department_id, job_id
FROM   employees;

Important Behavior: DISTINCT applies to the entire combination of selected columns, not to each column individually.

9. Operator Precedence

SELECT 2 + 3 * 4 FROM dual;     -- Returns 14  (multiplication first)
SELECT (2 + 3) * 4 FROM dual;   -- Returns 20  (parentheses override)

10. General SQL Statement Rules

• SQL keywords and identifiers are case-insensitive
• String literals inside single quotes are case-sensitive
• Extra whitespace, tabs, and newlines are ignored
• Use a semicolon to terminate statements in tools like SQL*Plus

11. Interactive Practice Quiz

Q1. What is wrong with this query?

A. Nothing — it is valid B. Alias "annual_sal" cannot be used in the WHERE clause C. AS cannot be used with expressions D. salary * 12 must be wrapped in a function

Q2. How many rows does this return?
SELECT DISTINCT department_id, job_id FROM employees;

A. One row per department B. One row per job type C. Number of unique (department_id, job_id) combinations D. All rows from the table

Q3. Which alias definition is invalid?

A. salary * 12 AS annual_salary B. salary * 12 annual_salary C. salary * 12 AS "Annual Salary" D. salary * 12 AS 'Annual Salary'

Conclusion

You now have a solid understanding of Oracle SQL SELECT statement fundamentals. These concepts form the foundation for writing powerful, readable, and efficient queries in any Oracle environment.

Practice these patterns regularly, pay attention to clause order, alias usage, and DISTINCT behavior, and you will write cleaner SQL with confidence.