oracle | Page 8

NAME
---------- ------------------------------ -----------
1 Cooper Industries New England
2 Emblazon Corp. New England
3 Ditech Corp. New England
4 Flowtech Inc. New England
5 Gentech Industries New England
Our FROM clause now contains two tables instead of one, and the WHERE clause
contains ajoin conditionthat specifies that the customer and region tables are to be
joined using the region_id column found in both tables.Joins and join conditions
will be explored in detail in Chapter 3.
Since both the customer and region tables contain a column calledname, you must
specify which table’s name column you are interested in.This is done in the previ-
ous example by using dot-notation to append the table name in front of each col-
umn name.If you would rather not type the full table names, you can assigntable
aliasesto each table in the FROM clause and use those aliases instead of the table
names in the SELECT and WHERE clauses, as in:
SELECT c.cust_nbr, c.name, r.name
FROM customer c, region r
WHERE r.name = `New England'
AND r.region_id = c.region_id;
In this example, we assigned the alias “c” to the customer table and the alias “r” to
the region table. Thus, we can use “c.” and “r.” instead of “customer.” and “region.”
in the SELECT and WHERE clauses.
SELECT clause elements
In the examples thus far, the result sets generated by our queries have contained col-
umns from one or more tables.While most elements in your SELECT clauses will
typically be simple column references, a SELECT clause may also include:
• Literal values, such as numbers (1) or strings ('abc')
• Expressions, such as shape.diameter * 3.1415927
• Functions, such as TO_DATE('01-JAN-2002','DD-MON-YYYY')
• Pseudocolumns, such as ROWID, ROWNUM, or LEVEL
,ch01.8459 Page 7 Wednesday, March 27, 2002 2:18 PM

This is the Title of the Book, eMatter Edition
Copyright © 2002 O’Reilly & Associates, Inc. All rights reserved. 8|
Chapter 1: Introduction to SQL
While the first three items in this list are fairly straightforward, the last item merits
further discussion.Oracle makes available several phantom columns, known as
pseudocolumns, that do not exist in any tables.Rather, they are values visible during
query execution that can be helpful in certain situations.
For example, the pseudocolumn ROWID represents the physical location of a row.
This information represents the fastest possible access mechanism.It can be useful if
you plan to delete or update a row retrieved via a query.However, you should never
store ROWID values in the database, nor should you reference them outside of the
transaction in which they are retrieved, since a row’s ROWID can change in certain
situations, and ROWIDs can be reused after a row has been deleted.
The next example demonstrates each of the different elements from the previous list:
SELECT rownum,
cust_nbr,
1 multiplier,
'cust # ' || cust_nbr cust_nbr_str,
'hello' greeting,
TO_CHAR(last_order_dt, 'DD-MON-YYYY') last_order
FROM customer;
ROWNUM CUST_NBR MULTIPLIER CUST_NBR_STR GREETING LAST_ORDER
------ -------- ---------- ------------ -------- -----------
1 1 1 cust # 1 hello 15-JUN-2000
2 2 1 cust # 2 hello 27-JUN-2000
3 3 1 cust # 3 hello 07-JUL-2000
4 4 1 cust # 4 hello 15-JUL-2000
5 5 1 cust # 5 hello 01-JUN-2000
6 6 1 cust # 6 hello 10-JUN-2000
7