Oracle:SELECT

Main Page >> Oracle and SQL >> Workbook >> 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:

SELECT EMPNO, ENAME, JOB, SAL;
FROM EMP 
WHERE SAL BETWEEN 1200 AND 1400

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.

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;

To list the same details in descending order by salary:

SELECT  ENAME, JOB, SAL FROM  EMP 
WHERE  DEPTNO  =  30  
ORDER  BY  SAL  DESC;

To sort employees by job and in descending order by name:

SELECT  ENAME, JOB, DEPTNO  FROM  EMP 
WHERE  DEPTNO  =  30  
ORDER  BY JOB, ENAME DESC;

Next Step

[Oracle:UPDATE|Updating tables]].