Introduction to SQL - The SELECT Statement (
Page 3 of 6 )
The SELECT statement is used to retrieve data from a database. The
set of data retrieved via a SELECT statement is referred to as a result
set. Like a table, a result set is comprised of rows and columns, making it
possible to populate a table using the result set of a SELECT statement. The
SELECT statement can be summarized as follows:
SELECT <one
or more things>
FROM <one or more places>
WHERE
<zero, one, or more conditions apply>
While the SELECT and FROM clauses are required, the WHERE
clause is optional (although you will seldom see it omitted). We will therefore
begin with a simple example that retrieves three columns from every row of the
customer table:
SELECT
cust_nbr, name, region_id
FROM customer;
CUST_NBR
NAME
REGION_ID
---------- -----------------------
----------
1 Cooper
Industries
5
2 Emblazon
Corp.
5
3 Ditech
Corp.
5
4 Flowtech
Inc.
5
5 Gentech
Industries 5
6 Spartan
Industries 6
7 Wallace
Labs
6
8 Zantech
Inc.
6
9 Cardinal
Technologies
6
10 Flowrite
Corp.
6
11 Glaven
Technologies 7
12 Johnson
Labs
7
13 Kimball
Corp.
7
14 Madden
Industries 7
15 Turntech
Inc.
7
16 Paulson
Labs
8
17 Evans Supply
Corp. 8
18 Spalding Medical Inc.
8
19 Kendall-Taylor
Corp.
8
20 Malden
Labs
8
21 Crimson Medical
Inc. 9
22 Nichols Industries
9
23 Owens-Baxter
Corp. 9
24 Jackson Medical Inc.
9
25 Worcester
Technologies 9
26 Alpha Technologies
10
27 Phillips
Labs
10
28 Jaztech
Corp.
10
29 Madden-Taylor
Inc. 10
30 Wallace Industries 10
Since we neglected to impose any conditions via a WHERE clause, the query
returns every row from the customer table. If you want to restrict the set of
data returned by the query, you can include a WHERE clause with a single
condition:
SELECT cust_nbr, name,
region_id
FROM customer
WHERE region_id
= 8;
CUST_NBR
NAME
REGION_ID
---------- ----------------------
----------
16 Paulson
Labs
8
17 Evans Supply
Corp.
8
18 Spalding Medical
Inc. 8
19
Kendall-Taylor Corp.
8
20 Malden
Labs
8
The result set now includes only those customers residing in
the region with a region_id
of 8. But what if you want to specify a region by name instead of region_id? You could query the
region table for a
particular name and then query the customer table using the retrieved
region_id. Instead of
issuing two different queries, however, you can produce the same outcome
using a single query by introducing a join, as in:
SELECT
customer.cust_nbr, customer.name, region.name
FROM customer INNER JOIN
region
ON region.region_id
= customer.region_id
WHERE region.name = 'New
England';
CUST_NBR
NAME
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
The FROM clause now contains two tables instead of one and
includes a join condition that 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
called name, you must
specify which table’s name
column you are interested in. This is done in the previous example by using
dot-notation to append the table name in front of each column name. If you would
rather not type full table names, you can assign table aliases to 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 INNER JOIN region
r
ON r.region_id = c.region_id
WHERE r.name = 'New
England';
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 columns 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 (27) or strings (‘abc’)
- Expressions, such as shape.diameter * 3.1415927
- Function calls, such as TO_DATE(‘01-JAN-2004’,‘DD-MON-YYYY’)
- Pseudocolumns, such as ROWID, ROWNUM, or LEVEL
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 element types
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
7 1 cust #
7 hello 17-JUN-2000
8
8 1 cust #
8 hello 22-JUN-2000
9
9 1 cust #
9 hello 25-JUN-2000
10
10 1 cust #
10 hello 01-JUN-2000
11 11
1 cust # 11 hello
05-JUN-2000
12 12
1 cust # 12 hello
07-JUN-2000
13 13
1 cust # 13 hello
07-JUN-2000
14 14
1 cust # 14 hello
05-JUN-2000
15 15
1 cust # 15 hello
01-JUN-2000
16
16 1 cust # 16
hello 31-MAY-2000
17
17 1 cust # 17
hello 28-MAY-2000
18
18 1 cust # 18
hello 23-MAY-2000
19
19 1 cust # 19
hello 16-MAY-2000
20 20
1 cust # 20 hello
01-JUN-2000
21 21
1 cust # 21 hello
26-MAY-2000
22 22
1 cust # 22 hello
18-MAY-2000
23 23
1 cust # 23 hello
08-MAY-2000
24 24
1 cust # 24 hello
26-APR-2000
25 25
1 cust # 25 hello
01-JUN-2000
26 26
1 cust # 26 hello
21-MAY-2000
27 27
1 cust # 27 hello
08-MAY-2000
28 28
1 cust # 28 hello
23-APR-2000
29 29
1 cust # 29 hello
06-APR-2000
30 30
1 cust # 30 hello 01-JUN-2000
Note that the third through sixth columns have been given
column aliases,
which are names that you assign to a column. If you are going to refer to the
columns in your query by name instead of by position, you will want to assign
each column a name that makes sense to you.
Interestingly, a SELECT clause is not required to reference
columns from any of the tables in the FROM clause. For example, the next query’s
result set is composed entirely of literals:
SELECT 1 num,
'abc' str
FROM
customer;
NUM STR
----------
---
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1
abc
1 abc
Since there are 30 rows in the customer table, the query’s result
set includes 30 identical rows of data.
 This article is excerpted from Mastering Oracle
SQL by Sanjay Mishra and Alan Beaulieu (O'Reilly, 2004; ISBN 1590593006). Check it out at your favorite
bookstore today. Buy
this book now. |
