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. |
Next: Ordering your results >>
More Oracle Articles
More By O'Reilly Media
