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