Testing for a Matching String
Retrieving Data with Queries
2-10 Oracle Database 2 Day Developer's Guide
uses the
WHERE
clause to return the six rows where the last name starts
with
Ma
: Mallin, Markle, Marlow, Marvins, Matos, and Mavris. If you use a matching
expression '
%ma%
' instead (the text
ma
could appear anywhere in the column), your
results would contain only three rows, for Kumar, Urman, and Vollman.
Example 27 Testing for a Matching String
SELECT first_name "First", last_name "Last"
FROM employees
WHERE last_name LIKE 'Ma%';
The results of the query appear.
First Last
-------------------- -------------------------
Jason Mallin
Steven Markle
James Marlow
...
6 rows selected
shows you how to use the
WHERE ... IN
clause to find employees who
work in several different departments, matching the
DEPARTMENT_ID
to a list of
values
100
,
110
,
120
. The result will contain eight rows, with four rows matching the
first value in the list, and the other two rows matching the second value in the list;
there are no matches for
120
.
Example 28 Testing for a Match in a List of Values
SELECT first_name "First", last_name "Last", department_id "Department"
FROM employees
WHERE department_id IN (100, 110, 120);
The results of the query appear.
First Last Department
-------------------- ------------------------- ----------------------
John Chen 100
Daniel Faviet 100
William Gietz 110
...
8 rows selected
If you want to find employees who work in a particular department, but do not know
the corresponding
department_id
value, you must look in both the
employees
and
departments
tables. Looking at an intersection of two tables is a
JOIN
operation.
Fully qualified column names, such as
employees.employee_id
, are optional.
However, when queries use two or more tables that have the same column name, you
must identify these columns with the table. For example, the
employees.department_id
and
departments.department_id
could be used
together to determine the name of a department in which an employee works.
Note that when using fully qualified column names, the query is more readable if you
use an alias for the name of the table, such as
d
for
departments
. The column
departments.department_id
then becomes
d.department_id
, and
employees.department_id
becomes
e.department_id
. You must create these
table aliases in the
FROM
clause of the query.
shows the result set containing columns from two separate tables.
Because the column names in the report are unique, they did not need to be qualified
