SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-32
BETWEEN Predicate
Smithson Richard 58300.00
--- 4 row(s) selected.
You can also use comparison operators with a subquery that returns a single value
(sometimes called a scalar subquery). Suppose that you want to know the name and
location of the department that James Smith (employee ID 2703) is in. This example
illustrates the use of the = operator with a subquery:
SELECT DEPT_NAME, DEPT_LOC
FROM DEPT
WHERE DEPT_NUM =
(SELECT DEPT_NUM FROM EMPLOYEE
WHERE EMP_ID = 2703) ;
The query returns this result:
DEPT_NAME DEPT_LOC
------------------------ --------
Marketing - USA West 120
--- 1 row(s) selected.
If a key column has a collation, you can use a comparison predicate as a begin or end
key only if you compare the column to a value that has the same collation and the
same length. A begin key establishes an initial row position within a table or index; An
end key establishes a stopping point.
If the comparison predicate compares two character expressions, these guidelines
apply to the use of character sets:
•
The same character set must be associated with each of the two character
expressions. Any character data type is compatible with other character data types
as long as both have the same associated character set.
•
If neither character expression references a column with an associated collation, a
binary comparison is used for all comparisons.
•
If the character expressions have two different lengths, the shorter string is filled on
the right with spaces to match the length of the longer string. Spaces are used
whether or not a single-byte or double-byte character set is associated with the
expression.
BETWEEN Predicate
You can use a BETWEEN predicate to perform a bounded search. A bounded search
is a search within a specific range of values. Predicates that specify bounds on a
search are also called range predicates.
Consider this statement:
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY BETWEEN 50000 AND 72000 ;