Explaining the Basics of the Oracle SELECT Statement

The SELECT statement is the most basic and important SQL statement for operating an Oracle database. It allows you to extract necessary information from tables and filter data by specifying conditions. This article explains the SELECT statement’s syntax, basic and advanced examples, as well as best practices and points to note.

This article uses examples based on the SCOTT sample schema. The SCOTT sample schema can be imported and used by executing the following:

SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger

Basic Syntax of the SELECT Statement
1. Details of Each Keyword
Basic Usage Examples of the SELECT Statement
Advanced Examples of the SELECT Statement
Best Practices and Points to Note
Summary

Basic Syntax of the SELECT Statement

The following is the basic syntax of the SELECT statement.

SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC|DESC];

Details of Each Keyword

SELECT: Specifies the columns you want to retrieve. Using * retrieves all columns, but specifying the minimum necessary columns is recommended.
FROM: Specifies the source table to extract data from.
WHERE: Filters data by setting conditions. Setting conditions that consider indexes is important for efficient queries.
ORDER BY: Sorts the results by the specified column. The default is ascending order (ASC).
DISTINCT: Retrieves data with duplicates removed.

Basic Usage Examples of the SELECT Statement

The following are specific examples using the SELECT statement.

1. Retrieving All Data from a Table

Retrieves all data from the “EMP” table. However, in actual operation, it is recommended to avoid * and explicitly specify the necessary columns.

SELECT *
FROM EMP;

2. Retrieving Only Necessary Columns

Retrieves only the “ENAME” and “JOB” columns. Specifying columns explicitly improves performance.

SELECT ENAME, JOB
FROM EMP;

3. Filtering Data Using the WHERE Clause

The SQL WHERE clause is a powerful filtering function used to retrieve (SELECT) only the rows that match specific criteria from a table. While SELECT * FROM SCOTT.EMP; unconditionally retrieves all employee data, the WHERE clause is an essential syntax element for fulfilling specific requests like “I only want this data.”

Data extraction conditions are not limited to = (equals); they can be flexibly specified using various comparison operators.

1. Basic Comparisons (=, >, !=) The most commonly used operators are for comparing numbers or strings.

-- Extract only employees in department number (DEPTNO) 10 from the SCOTT.EMP table
SELECT ENAME, JOB, SAL
FROM SCOTT.EMP
WHERE DEPTNO = 10;

-- Extract employees with salary (SAL) greater than 2000
SELECT ENAME, JOB, SAL
FROM SCOTT.EMP
WHERE SAL > 2000;

-- Extract employees whose job (JOB) is not 'MANAGER'
-- (<> can also be used with the same meaning as !=)
SELECT ENAME, JOB
FROM SCOTT.EMP
WHERE JOB != 'MANAGER';

2. Specifying Ranges (BETWEEN, >=, <=) Extracts data that falls within a specific range.

-- Extract employees with salary (SAL) between 1000 and 2000 (inclusive)
SELECT ENAME, SAL
FROM SCOTT.EMP
WHERE SAL BETWEEN 1000 AND 2000;

-- The above can also be written as follows
SELECT ENAME, SAL
FROM SCOTT.EMP
WHERE SAL >= 1000 AND SAL <= 2000;

3. Specifying Multiple Values (IN) Specifies a condition where a match with any of multiple values is sufficient.

-- Extract employees in department number (DEPTNO) 10 or 30
SELECT ENAME, DEPTNO
FROM SCOTT.EMP
WHERE DEPTNO IN (10, 30);

4. Wildcard Search (LIKE) Searches for data where part of a string matches. % means “any string of 0 or more characters.”

-- Extract employees whose name (ENAME) starts with 'S'
SELECT ENAME
FROM SCOTT.EMP
WHERE ENAME LIKE 'S%';

By mastering the WHERE clause and comparison operators, you can freely and efficiently extract only the necessary information from tables storing vast amounts of data.

4. Sorting Using the ORDER BY Clause

Sorts employee data by department number (ascending order).

SELECT ENAME, JOB, DEPTNO
FROM EMP
ORDER BY DEPTNO ASC;

5. Removing Duplicates Using DISTINCT

Retrieves department numbers (DEPTNO) with duplicates removed.

SELECT DISTINCT DEPTNO
FROM EMP;

6. Changing Column Names Using Aliases (ALIAS)

Changes column names for clearer display.

SELECT ENAME, JOB
FROM EMP;

Advanced Examples of the SELECT Statement

1. Summarizing Data Using Aggregate Functions

Retrieves the number of employees in the “EMP” table.

SELECT COUNT(*)
FROM EMP;

2. Grouping Using the GROUP BY Clause

Calculates the average salary for each department.

SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

3. Setting Grouping Conditions Using the HAVING Clause

Retrieves departments with an average salary of 2000 or more.

SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) >= 2000;

4. Setting Conditions Using a Subquery

Retrieves the employee(s) with the highest salary.

SELECT ENAME, SAL
FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);

5. Joining Multiple Tables Using JOIN

Joins the “EMP” and “DEPT” tables to retrieve department names.

SELECT E.ENAME, E.JOB, D.DNAME
FROM EMP E
JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

Best Practices and Points to Note

Explicitly Specify Columns Avoid SELECT * and specify only the necessary columns to improve query efficiency.
Utilize Indexes Performance improves if you design queries so that indexes are used in the WHERE clause and JOIN conditions.
Check the Execution Plan Use EXPLAIN PLAN to check the query’s execution plan and perform optimizations.
Set Appropriate Privileges Ensure security by granting only the minimum necessary privileges to users who use the SELECT statement.

Summary

The SELECT statement is a fundamental database operation, yet it is a powerful tool capable of various applications. This article has provided a detailed explanation, from basic syntax to advanced examples and best practices. Master these techniques through practice to improve your database operation skills. As a next step, try building complex queries and tackling performance tuning.