The SELECT Statement - Oracle

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.

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus