Databases Reference
In-Depth Information
BETWEEN
,
IN
, and
LIKE
The BETWEEN, IN, and LIKE operators provide more ways to trim down the num-
ber of rows returned from a query. BETWEEN gives you an easy way to check for
a value that falls within a certain range. The IN operator can help you find values
in a list. LIKE can help you find character strings that match a certain pattern.
Adding NOT to these will give you just the opposite set of rows.
BETWEEN
a Rock and a Hard Place
The BETWEEN operator in a WHERE clause will limit the rows to a range that is spec-
ified by a beginning value and an ending value; the range is inclusive. The values
can be dates, numbers, or character strings. The column values to be compared will
be converted to the datatypes of the values in the BETWEEN operator as needed.
Each quarter at Scott's widget company, employees are recognized for years
of service to the company. Janice is in charge of generating the report that lists
the employees who have their anniversary within the next three months. Her
query will use one of the functions mentioned in the previous chapter, EXTRACT,
which returns one of the individual components of a DATE datatype.
select employee_id "Emp ID", department_id "Dept ID",
hire_date "Hire Date",
last_name || ', ' || first_name "Name" from employees
/* Oct to Dec */
where extract(month from hire_date) between 10 and 12;
Emp ID Dept ID Hire Date Name
---------- ---------- --------- ----------------------
113 100 07-DEC-99 Popp, Luis
114 30 07-DEC-94 Raphaely, Den
116 30 24-DEC-97 Baida, Shelli
118 30 15-NOV-98 Himuro, Guy
123 50 10-OCT-97 Jasper, Susan Abigail
124 50 16-NOV-99 Mourgos, Kevin
130 50 30-OCT-97 Atkinson, Mozhe
135 50 12-DEC-99 Gee, Ki
138 50 26-OCT-97 Stiles, Stephen
141 50 17-OCT-95 Rajs, Trenna
145 80 01-OCT-96 Russell, John
148 80 15-OCT-99 Cambrault, Gerald
154 80 09-DEC-98 Cambrault, Nanette
155 80 23-NOV-99 Tuvault, Oliver
160 80 15-DEC-97 Doran, Louise
