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)

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';