Mastering SQL SELECT Statement in Oracle: Retrieve, Restrict & Sort Data Efficiently

Introduction

If you’re working with databases, one command you’ll use more than any other is the SQL SELECT statement.

Whether you’re building reports, analyzing data, or debugging issues, SELECT is your gateway to retrieving data from the database.

In this guide, we’ll walk through everything you need to know about the SQL SELECT statement in Oracle—from basics to practical tips—so you can write clean, efficient, and professional queries.

---

What Is the SQL SELECT Statement?

The SELECT statement is used to:

• Retrieve data from a database
• Display specific columns or all columns
• Perform calculations
• Format output
• Combine and manipulate data

In simple terms:

👉 SELECT tells the database what data you want to see

---

Real-World Scenario

Imagine Alex needs a report:

“Show me all employees working in the Accounting department.”

Behind the scenes, SQL executes a SELECT query to fetch that data from the database.

That’s the power of SELECT—it drives every data retrieval operation.

---

Basic Syntax of SELECT

A simple SELECT statement looks like this:

SELECT column1, column2
FROM table_name;

Or to retrieve everything:

SELECT *
FROM table_name;

---

Key Rules When Writing SQL

Before diving deeper, here are some important rules:

• SQL is not case-sensitive
• Keywords should be written in UPPERCASE (best practice)
• Statements can span multiple lines
• Keywords cannot be abbreviated
• Do not split keywords across lines
• Use indentation for readability

These practices make your SQL cleaner and easier to maintain.

---

Retrieving All Columns

If you want all data from a table:

SELECT *
FROM departments;

What Happens?

• Retrieves all columns
• Retrieves all rows
• Displays full dataset

This is useful for exploration—but not ideal for production queries.

---

Retrieving Specific Columns

Instead of selecting everything, you can target only what you need:

SELECT department_id, department_name
FROM departments;

Benefits

• Faster performance
• Cleaner output
• Reduced data load

⚠️ Avoid adding a comma after the last column—it will cause an error.

---

Understanding SQL Execution in Tools

When using tools like SQL Developer:

• Run Statement → Executes one query
• Run Script → Executes multiple queries

If you’re running multiple statements:

👉 Always end each statement with a semicolon (;)

---

Working with Output

Different tools display results differently:

• SQL Developer → Interactive, sortable output
• SQL*Plus → Text-based output

Pro Tip

You can:

• Resize columns
• Export results
• Format queries for better readability

---

Column Formatting & Alignment

In SQL Developer:

• Column headings are uppercase
• Data is left-aligned

In SQL*Plus:

• Text → Left-aligned
• Numbers → Right-aligned

👉 You can identify data types visually based on alignment.

---

Using the DUAL Table

Oracle provides a special table called DUAL.

What Is DUAL?

• Contains one column (DUMMY)
• Contains one row (value = ‘X’)

Why Use It?

To:

• Perform calculations
• Retrieve system values
• Execute expressions without a real table

---

Example: Using DUAL

Get Current Date

SELECT SYSDATE FROM dual;

Perform Calculation

SELECT 5982 * 1.2 FROM dual;

---

Why Not Use Other Tables?

If you run:

SELECT SYSDATE FROM employees;

And you have 20 employees:

👉 You’ll get 20 rows of the same date

That’s why DUAL is useful—it returns only one row.

---

New Feature: SELECT Without FROM (Oracle 23c+)

Oracle introduced a powerful feature:

👉 You can now run SELECT without a FROM clause

---

Example

SELECT SYSDATE;

or

SELECT 5280 * 1.5;

Result

• No need for DUAL
• Cleaner syntax
• Faster execution

This is a big improvement for quick queries and calculations.

---

Using Arithmetic Expressions

You can perform calculations directly in SELECT:

SELECT salary * 12 AS annual_salary
FROM employees;

Benefits

• No need for external calculations
• Real-time computed values
• Useful for reporting

---

Handling NULL Values

When working with data:

• NULL represents missing or unknown values
• Arithmetic with NULL results in NULL

Example:

SELECT salary + NULL FROM employees;

👉 Result = NULL

Always handle NULLs carefully in calculations.

---

Column Aliases

Aliases make your output more readable.

Example

SELECT salary AS monthly_salary
FROM employees;

Or without AS:

SELECT salary monthly_salary
FROM employees;

Why Use Aliases?

• Improve readability
• Customize column names
• Make reports user-friendly

---

Using DISTINCT

To remove duplicate values:

SELECT DISTINCT department_id
FROM employees;

Use Case

• Unique lists
• Data analysis
• Reporting

---

Concatenation Operator

You can combine columns using ||:

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

---

DESCRIBE Command

To view table structure:

DESC employees;

This shows:

• Column names
• Data types
• Constraints

---

Best Practices

✔ Select Only What You Need

Avoid SELECT * in production.

✔ Format Your Queries

Use indentation and uppercase keywords.

✔ Use Aliases

Improve readability.

✔ Test Queries Incrementally

Start simple, then build complexity.

✔ Use DUAL or No-FROM Smartly

For calculations and system values.

---

Common Mistakes to Avoid

• ❌ Missing commas between columns
• ❌ Adding comma after last column
• ❌ Forgetting semicolon in scripts
• ❌ Using SELECT * unnecessarily
• ❌ Ignoring NULL behavior

---

Conclusion

The SQL SELECT statement is the foundation of data retrieval in Oracle.

From simple queries to advanced data manipulation, mastering SELECT helps you:

• Write efficient queries
• Improve performance
• Build better reports
• Debug data issues faster

---

Final Thought 🚀

If you master SELECT, you master SQL.

Because every insight, every report, and every analysis begins with one simple command:

👉 SELECT