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
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;
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
2. Retrieving Only Necessary Columns
Retrieves only the “ENAME” and “JOB” columns. Specifying columns explicitly improves performance.
SELECT ENAME, JOB FROM EMP;
SQL> SELECT ENAME, JOB FROM EMP;
ENAME JOB
---------- ---------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 rows selected.
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';
SQL> SELECT ENAME, JOB, SAL FROM SCOTT.EMP WHERE DEPTNO = 10;
ENAME JOB SAL
---------- --------- ----------
CLARK MANAGER 2450
KING PRESIDENT 5000
MILLER CLERK 1300
SQL> SELECT ENAME, JOB, SAL FROM SCOTT.EMP WHERE SAL > 2000;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
6 rows selected.
SQL> SELECT ENAME, JOB FROM SCOTT.EMP WHERE JOB != 'MANAGER';
ENAME JOB
---------- ---------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
11 rows selected.
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;
SQL> SELECT ENAME, SAL FROM SCOTT.EMP
2 WHERE SAL BETWEEN 1000 AND 2000;
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
MILLER 1300
6 rows selected.
SQL> SELECT ENAME, SAL FROM SCOTT.EMP
2 WHERE SAL >= 1000 AND SAL <= 2000;
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
MILLER 1300
6 rows selected.
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);
SQL> SELECT ENAME, DEPTNO FROM SCOTT.EMP
2 WHERE DEPTNO IN (10, 30);
ENAME DEPTNO
---------- ----------
ALLEN 30
WARD 30
MARTIN 30
BLAKE 30
CLARK 10
KING 10
TURNER 30
JAMES 30
MILLER 10
9 rows selected.
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%';
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE 'S%';
ENAME
----------
SMITH
SCOTT
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;
SQL> SELECT ENAME, JOB, DEPTNO FROM EMP;
ENAME JOB DEPTNO
---------- --------- ----------
SMITH CLERK 20
ALLEN SALESMAN 30
WARD SALESMAN 30
JONES MANAGER 20
MARTIN SALESMAN 30
BLAKE MANAGER 30
CLARK MANAGER 10
SCOTT ANALYST 20
KING PRESIDENT 10
TURNER SALESMAN 30
ADAMS CLERK 20
JAMES CLERK 30
FORD ANALYST 20
MILLER CLERK 10
14 rows selected.
SQL> SELECT ENAME, JOB, DEPTNO FROM EMP
2 ORDER BY DEPTNO ASC;
ENAME JOB DEPTNO
---------- --------- ----------
CLARK MANAGER 10
KING PRESIDENT 10
MILLER CLERK 10
JONES MANAGER 20
FORD ANALYST 20
ADAMS CLERK 20
SMITH CLERK 20
SCOTT ANALYST 20
WARD SALESMAN 30
TURNER SALESMAN 30
ALLEN SALESMAN 30
JAMES CLERK 30
BLAKE MANAGER 30
MARTIN SALESMAN 30
14 rows selected.
5. Removing Duplicates Using DISTINCT
Retrieves department numbers (DEPTNO) with duplicates removed.
SELECT DISTINCT DEPTNO FROM EMP;
SQL> SELECT DEPTNO FROM EMP;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
14 rows selected.
SQL> SELECT DISTINCT DEPTNO FROM EMP;
DEPTNO
----------
30
10
20
6. Changing Column Names Using Aliases (ALIAS)
Changes column names for clearer display.
SELECT ENAME AS "Employee name", JOB AS "Occupation" FROM EMP;
SQL> SELECT ENAME AS "Employee name", JOB AS "Occupation" FROM EMP;
Employee n Occupatio
---------- ---------
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK
14 rows selected.
Advanced Examples of the SELECT Statement
1. Summarizing Data Using Aggregate Functions
Retrieves the number of employees in the “EMP” table.
SELECT COUNT(*) AS "Number of employees" FROM EMP;
SQL> SELECT COUNT(*) AS "Number of employees" FROM EMP;
Number of employees
-------------------
14
2. Grouping Using the GROUP BY Clause
Calculates the average salary for each department.
SELECT DEPTNO, AVG(SAL) AS "AVERAGE SALARY" FROM EMP GROUP BY DEPTNO;
SQL> SELECT DEPTNO, AVG(SAL) AS "AVERAGE SALARY" FROM EMP GROUP BY DEPTNO;
DEPTNO AVERAGE SALARY
---------- --------------
30 1566.66667
10 2916.66667
20 2175
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;
SQL> SELECT DEPTNO, AVG(SAL) FROM EMP
2 GROUP BY DEPTNO HAVING AVG(SAL) >= 2000;
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175
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);
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL = (SELECT MAX(SAL) FROM EMP);
ENAME SAL
---------- ----------
KING 5000
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;SQL> SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E
2 JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
ENAME JOB DNAME
---------- --------- --------------
SMITH CLERK RESEARCH
ALLEN SALESMAN SALES
WARD SALESMAN SALES
JONES MANAGER RESEARCH
MARTIN SALESMAN SALES
BLAKE MANAGER SALES
CLARK MANAGER ACCOUNTING
SCOTT ANALYST RESEARCH
KING PRESIDENT ACCOUNTING
TURNER SALESMAN SALES
ADAMS CLERK RESEARCH
JAMES CLERK SALES
FORD ANALYST RESEARCH
MILLER CLERK ACCOUNTING
14 rows selected.
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
WHEREclause andJOINconditions. - Check the Execution Plan Use
EXPLAIN PLANto 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
SELECTstatement.
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.
コメント