Oracle:SELECT
Main Page >> Oracle and SQL >> Workbook >> DML >> SELECT
Introduction to SELECT
You have already seen some simple SELECT statements that return all the rows back from the table. This is fine with a table with few records, but if you are working with large datasets, in most cases only a subset of the data would be required.
The rows returned can be restricted by use of the WHERE clause, e.g. WHERE DEPTNO = 30
The WHERE clause is one of the most important and complex clauses in the SELECT statement. It controls what rows are retrieved in the output, by applying a filter condition called a search condition.
Standard SQL search conditions can involve:
- Simple comparisons
- Compound conditions formed with AND, OR and NOT
- Special SQL predicates such as BETWEEN, IN, LIKE and NULL
- Comparison with the results of a subquery (a SELECT statement within a SELECT statement)
- Additional predicates designed for use with subqueries, such as ALL, ANY and EXISTS
This large range of possibilities makes the WHERE clause so complicated.
WHERE always works in the following manner:
- The search condition is applied to each prospective row in the output.
- If the condition is satisfied, then that row will be included in the output.
- If the condition is not satisfied, then that row is omitted from the output.
Comparison Operators
All search conditions are built from logical expressions, which are always true or false. The simplest form of a logical expression is known as a simple comparison, or relational predicate. Simple comparisons use comparison operators to compare two values.
Relational predicates appear in the WHERE clause and can include references to column names, literal constants, numeric or character expressions and non-aggregate functions.
The main rule to remember is that the two items being compared must of a compatible data type. You cannot mix numeric and character values in a comparison. Numeric with numeric, character with character and date with date are the only allowable forms.
Dates and character data values must also be included in single quotes (').
E.g., ename = SMITH is not allowed (SMITH would be treated as if it was a column name) but ename = 'SMITH' is allowed
The usual format of a simple comparison is:
| Column-Name | Comparison-Operator | Value | Type |
|---|---|---|---|
| ename | <> | 'SMITH' | character value |
| deptno | = | 95 | numeric value |
| hiredate | > | '11-JUL-1996' | date value |
The following are valid comparison operators. NOT can be optionally used with the last four operators for negation:
| = | equal | |
| <> | not equal | |
| > | greater than | |
| >= | greater than, or equal to | |
| < | less than | |
| <= | less than, or equal to | |
| [NOT] | BETWEEN .... AND .... | [not] between one value and another |
| [NOT] | IN ( list ) | [not] in a list of values |
| [NOT] | LIKE | [not] like a value |
| IS [NOT] NULL | value is [not] equal to null |
Example Queries
Find all employees where their commission exceeds their salary:
SELECT ENAME, SAL, COMM FROM EMP WHERE COMM > SAL;
List information about all managers across all departments and clerks in department 10:
SELECT * FROM EMP WHERE JOB = 'MANAGER' OR (JOB = 'CLERK' AND DEPTNO = 10);
List names and departments of employees who are not clerks, analysts or salesmen:
SELECT ENAME, DEPTNO
FROM EMP
WHERE JOB NOT IN ('CLERK', 'ANALYST', 'SALESMAN');
List employees who have not earned any commission:
SELECT * FROM EMP WHERE COMM IS NULL;
List employees and jobs earning between £1,200 and £1,400 (no need to include the pound sign):
SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL BETWEEN 1200 AND 1400;
Note, the currency symbol, or the 1000 separator commas is not stored in the database.
List names, jobs and commission of employees who are not in department 10, 30 or 40:
SELECT ENAME, JOB, COMM FROM EMP WHERE DEPTNO NOT IN ( 10, 30, 40);
List information about employees in department 10 who are not managers or clerks:
SELECT * FROM EMP WHERE DEPTNO = 10 AND NOT (JOB = 'MANAGER' OR JOB = 'CLERK');
The LIKE operator
The LIKE operator is used for pattern matching.
This operator uses two symbols:
- % represents a string of characters
- _ represents a single character (underscore)
LIKE Examples
To list all employees whose name begins with S:
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
To list all employees whose name contains the characters LL:
SELECT * FROM EMP WHERE ENAME LIKE '%LL%';
List all the employees whose name ends in ER:
SELECT * FROM EMP WHERE ENAME LIKE '%ER';
List the employee number and name of employees who have A as the second letter of their name:
SELECT EMPNO, ENAME FROM EMP WHERE ENAME LIKE '_A%';
Preventing the Selection of Duplicate Rows
Some queries will produce results that include duplicate rows, for example, if you list rows without the primary key field(s).
For example, type in the following:
SELECT JOB FROM EMP;
A row will be returned for each record in the table. If you are only interested in seeing what jobs are stored in the table, rather than seeing how many employees there are for a particular job, use the DISTINCT clause.
To list all the distinct jobs in EMP:
SELECT DISTINCT JOB FROM EMP;
DISTINCT can be applied to more than one column:
SELECT DISTINCT JOB, COMM FROM EMP;
Note when using DISTINCT it must follow the SELECT clause and will apply to all the columns listed.
Controlling the order in which rows are displayed
You can not predict what order the results will appear from the database unless you take steps to sort it in some manner. This is why the results you have got so far may not match the examples given. They could be in the order they were inserted, or the database management system (DBMS) may have used some unknown indexes to sort the data.
The ORDER BY Clause
The ORDER BY clause lets you order rows:
- In ascending order
- In descending order
- Using multiple columns
- With null values
Note, an ORDER BY clause is always the last clause in a SELECT statement.
The format is:
- ORDER BY <order_column> [ASC | DESC]
- [, <order_column> [ASC | DESC] .....]
Order by sorts the query results based on the data in one, or more columns. Each order_column must correspond to a column in the query results and can be one of the following:
- A field in a FROM table that is a select item in the main SELECT clause (not in a subquery)
- A numeric expression indicating the location of the column in the result table (The leftmost column is number 1)
SELECT * FROM EMP ORDER BY JOB;
Specify DESC if you want the results in descending order:
SELECT * FROM EMP ORDER BY JOB, SAL DESC;
ASCending is the default and can be omitted. Query results will appear unordered if the ORDER BY clause is missing. If you want all columns to be sorted in descending order, you do have to repeat the DESC for each field:
SELECT * FROM EMP ORDER BY JOB DESC, SAL DESC;
Ordering with null values
When you order by columns that contain null (blank) values, the null entries can produce unpredicable results.
For numerical and character data, the nulls generally appear last (note, in previous versions they appeared first).
To list employees and jobs in department 30, in order of their commission:
SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE DEPTNO = 30 ORDER BY COMM;
Exercise 3.1
3.1.1 Find all salespeople in Department 30 whose salary is greater than or equal to £1,500:
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7499 ALLEN 1600 30
7844 TURNER 1500 30
3.1.2 List information about managers and clerks in department 10:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ---------- --------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10
3.1.3 List the empno, name and jobs of employees whose names begin with M:
EMPNO ENAME JOB
---------- ---------- ---------
7654 MARTIN SALESMAN
7934 MILLER CLERK
3.1.4 List employees whose salaries are not between £1,200 and £1,400:
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
7369 SMITH CLERK 800
7698 BLAKE MANAGER 2850
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
7900 JAMES CLERK 950
7782 CLARK MANAGER 2450
11 rows selected.
3.1.5 List names and departments of employees who are clerks, analysts or salesmen:
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7788 SCOTT ANALYST 20
7876 ADAMS CLERK 20
7902 FORD ANALYST 20
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7654 MARTIN SALESMAN 30
7844 TURNER SALESMAN 30
7900 JAMES CLERK 30
7934 MILLER CLERK 10
10 rows selected.
3.1.6 To list all the distinct Departments in EMP:
DEPTNO
----------
30
20
10
3.1.7 To sort employees by job and then in descending order by salary:
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7369 SMITH CLERK 800
7566 JONES MANAGER 2975
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7839 KING PRESIDENT 5000
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
14 rows selected.
