The SELECT Statement

SELECT { column | expression | * 
	[ , column | expression | * ... ] }
FROM [schema.]{table | view};

Using Aliases

SELECT
{
	[alias.]column | expression | [alias.]*
	[ , [alias.]column | expression | [alias.]* ... ]
}
FROM [schema.]{table | view} [alias];

The alias assigns a different name to a table or view retrieved from.

Adding the WHERE Clause

SELECT { [alias.]column | expression | [alias.]* [ , … ] }
FROM [schema.]table [alias]
[ WHERE [ [schema.]table.|alias.] { column | expression }
   comparison { … } 
      [ { AND | OR } [ NOT ] … ]
];

Adding the ORDER BY Clause

SELECT { [alias.]column | expression | [alias.]* [ , … ] }
FROM [schema.]table [alias]
[ WHERE [ [schema.]table.|alias.] { column | expression }
   comparison { … } 
      [ { AND | OR } [ NOT ] … ]
]
[ ORDER BY
   { { column | expression | position }
      [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
    [, … ] }
];

The GROUP BY Clause and the HAVING Clause

SELECT { [ [schema.] table.|alias.] { column | expression } [ , … ] | * }
FROM [schema.]table [alias]
[ WHERE ... ]
[ GROUP BY 
   { expression | rollup-cube | grouping-sets }
      [,  { expression | rollup-cube | grouping-sets } ... ]
   [ HAVING condition ]
]
[ ORDER BY ... ];

rollup-cube = ROLLUP | CUBE ( expression [, expression ... ] )
grouping-sets = ( rollup-cube | expression [, expression ... ] )

SELECT Statement Types in Oracle

Simple Query

SELECT * FROM author;

Filtered Query

SELECT * FROM author
WHERE name LIKE 'A%';

Sorted Query

SELECT * FROM author
ORDER BY name;

Join

SELECT a.name, p.title, e.ISBN 
FROM author a, publication p, edition e
WHERE p.author_id = a.author_id
AND e.publication_id = p.publication_id;

The New ANSI Format

SELECT a.name, p.title, e.ISBN 
FROM author a JOIN publication p USING(author_id)
	JOIN edition e USING (publication_id);

Subquery

Subqueries can be used as follows:

• In a SELECT clause.
• In a WHERE clause (filtering).
• In an ORDER BY clause (sorting).
• In a GROUP BY clause (grouping and summarizing).
• In a HAVING clause (filtering groups).
• In the FROM clause.
• An INSERT statement VALUES clause.
• An UPDATE statement SET = (subquery).
• A CASE statement expression.
• As a function parameter.

Some Example Subqueries

Column Replacement Subquery

SELECT p.title,
   (SELECT name FROM author
   WHERE author_id = p.author_id)
FROM publication p;

WHERE Clause Subquery

SELECT title FROM publication
WHERE author_id IN
   (SELECT author_id FROM author);

FROM Clause Subquery

SELECT a.author_id
FROM (SELECT author_id from author) a;

A Nested Subquery

SELECT ISBN FROM edition
WHERE publication_id IN
   (SELECT publication_id FROM publication
   WHERE author_id IN
      (SELECT author_id FROM author));

Table Creation

CREATE TABLE ISBNNumbers AS
	SELECT ISBN FROM edition
	WHERE publication_id IN
		(SELECT publication_id FROM publication
		WHERE author_id IN
			(SELECT author_id FROM author));

View Creation

CREATE View ISBNNumbersView AS
	SELECT ISBN FROM edition
	WHERE publication_id IN
		(SELECT publication_id FROM publication
		WHERE author_id IN
			(SELECT author_id FROM author));

Hierarchical Queries

SELECT name, subject_id, parent_id FROM subject
   CONNECT BY subject_id = parent_id;

Here is a more interesting example of a hierarchical query using a table containing a hierarchical family tree as the source data. The PRIOR keyword applies to the CATEGORY_ID column.

SELECT name, father, LEVEL
FROM familyTree
START WITH name = 'William Ford I'
CONNECT BY PRIOR name = father;

This is an picture of the family tree with the rows resulting from the preceeding query.

Family Tree Script

This is the script used to create the family tree table. The CATEGORY_ID of the parent equals the PARENT_ID of the child row.

DROP TABLE familyTree;
CREATE TABLE familyTree(
	 name VARCHAR2(32) PRIMARY KEY
	,birth NUMBER(4)
	,death NUMBER(4)
	,father VARCHAR2(32) REFERENCES familyTree(name)
	,mother VARCHAR2(32) 
);

INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Rebecca Jennings',1776,1851,NULL,NULL);
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('William Ford I',1775,NULL,NULL,NULL);
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Thomasine Smith',1803,NULL,NULL,NULL);
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('John Ford',1799,1842,'William Ford I','Rebecca Jennings');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Mary Litogot',1839,1876,NULL,NULL);
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('William Ford II',1826,1905,'John Ford','Thomasine Smith');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Clara Bryant',1866,1950,NULL,NULL);
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Henry Ford',1863,1947,'William Ford II','Mary Litogot');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Edsel Ford',1893,1943,'Henry Ford','Clara Bryant');
COMMIT;

Here is another larger example family tree.

TRUNCATE TABLE familyTree;
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('George V',1865,1936,NULL,NULL);
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Edward VIII',1896,1986,'George V','Princess Mary');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('George IV',1895,1952,'George V','Princess Mary');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Mary Princess Royal',1897,1965,'George V','Princess Mary');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Henry Duke of Gloucester',1900,1974,'George V','Princess Mary');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('George Duke of Kent',1902,1942,'George V','Princess Mary');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Prince John',1905,1919,'George V','Princess Mary');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Queen Elizabeth II',1926,NULL,'George IV','Lady Elizabeth Bowes-Lyon');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Princess Margaret',1930,NULL,'George IV','Lady Elizabeth Bowes-Lyon');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('David Viscount Linley',1961,NULL,NULL,NULL);
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Lady Sarah Armstrong-Jones',1964,NULL,NULL,NULL);
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Charles Prince of Wales',1948,NULL,'Queen Elizabeth II','Phillip Duke of Edinburgh');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Anne Proncess Royal',1950,NULL,'Queen Elizabeth II','Phillip Duke of Edinburgh');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Andrew Duke of York',1960,NULL,'Queen Elizabeth II','Phillip Duke of Edinburgh');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Edward Earl of Wessex',1964,NULL,'Queen Elizabeth II','Phillip Duke of Edinburgh');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Edward Duke of Kent',1935,NULL,'George Duke of Kent','Princess Marina of Greece');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Princess Alexandra',1936,NULL,'George Duke of Kent','Princess Marina of Greece');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Prince Michael',1942,NULL,'George Duke of Kent','Princess Marina of Greece');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Richard Duke of Gloucester',1944,NULL,'Henry Duke of Gloucester','Lady Alice Montagu');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Prince William of Wales',1982,NULL,'Charles Prince of Wales','Lady Diana Spencer');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Prince Henry of Wales',1984,NULL,'Charles Prince of Wales','Lady Diana Spencer');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Anne Princess Royal',1950,NULL,'Queen Elizabeth II','Phillip Duke of Edinburgh');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Peter Phillips',1977,NULL,'Anne Princess Royal','Captain Mark Phillips');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Zara Phillips',1981,NULL,'Anne Princess Royal','Captain Mark Phillips');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Princess Beatrice of York',1988,NULL,'Andrew Duke of York','Sarah Ferguson');
INSERT INTO familyTree(name,birth,death,father,mother) VALUES('Princess Eugenie of York',1990,NULL,'Andrew Duke of York','Sarah Ferguson');
COMMIT;

Set Operators and Composite Queries

Set operators allow the merging of the results of multiple queries. Set operators can be used to substitute for other types of queries under certain circumstances. The four set operators are as follows.

• UNION ALL includes all rows from both queries including duplicates.

  SELECT author_id, name FROM author
  UNION ALL
  SELECT co.coauthor_id, a.name FROM coauthor co
  	JOIN author a ON (co.coauthor_id = a.author_id);
  
   AUTHOR_ID NAME
  ---------- --------------------------------
           1 Orson Scott Card
           2 James Blish
           3 Isaac Azimov
           4 Larry Niven
           5 Jerry Pournelle
           6 William Shakespeare
           5 Jerry Pournelle
           5 Jerry Pournelle
  
  8 rows selected.
  

• UNION includes all distinct rows from both queries.

  SELECT author_id, name FROM author
  UNION
  SELECT co.coauthor_id, a.name FROM coauthor co
  	JOIN author a ON (co.coauthor_id = a.author_id);
  
   AUTHOR_ID NAME
  ---------- --------------------------------
           1 Orson Scott Card
           2 James Blish
           3 Isaac Azimov
           4 Larry Niven
           5 Jerry Pournelle
           6 William Shakespeare
  
  6 rows selected.
  

• INTERSECT returns the intersection of two queries, rows the same in both.

  SELECT author_id, name FROM author
  INTERSECT
  SELECT co.coauthor_id, a.name FROM coauthor co
  	JOIN author a ON (co.coauthor_id = a.author_id);
  
   AUTHOR_ID NAME
  ---------- --------------------------------
           5 Jerry Pournelle
  

• MINUS returns only the distinct rows in the first of two queries.

  SELECT author_id, name FROM author
  MINUS
  SELECT co.coauthor_id, a.name FROM coauthor co
  	JOIN author a ON (co.coauthor_id = a.author_id);
  
   AUTHOR_ID NAME
  ---------- --------------------------------
           1 Orson Scott Card
           2 James Blish
           3 Isaac Azimov
           4 Larry Niven
           6 William Shakespeare
  

Selecting all Columns and Rows

The Author table has only two columns.

set linesize 40;
desc author;

 Name              Null?    Type
 ----------------- -------- ------------
 AUTHOR_ID         NOT NULL NUMBER
 NAME                       VARCHAR2(32)

set linesize 132;

The four following queries are simple. Notice how the second and third queries produce the same result; using the asterisk (*) retrieves all columns. The fourth query is different since the sequence of the columns is reversed.

SELECT name FROM author;

NAME
--------------------------------
Orson Scott Card
James Blish
Isaac Azimov
Larry Niven
Jerry Pournelle
William Shakespeare

6 rows selected.

SELECT * FROM author;

 AUTHOR_ID NAME
---------- --------------------------------
         1 Orson Scott Card
         2 James Blish
         3 Isaac Azimov
         4 Larry Niven
         5 Jerry Pournelle
         6 William Shakespeare

6 rows selected.

SELECT author_id, name FROM author;

 AUTHOR_ID NAME
---------- --------------------------------
         1 Orson Scott Card
         2 James Blish
         3 Isaac Azimov
         4 Larry Niven
         5 Jerry Pournelle
         6 William Shakespeare

6 rows selected.

SELECT name, author_id FROM author;

NAME                              AUTHOR_ID
-------------------------------- ----------
Orson Scott Card                          1
James Blish                               2
Isaac Azimov                              3
Larry Niven                               4
Jerry Pournelle                           5
William Shakespeare                       6

6 rows selected.

Other Points of Interest

Points of interest are as follows.

• NULL values.
• The DUAL table.
• Constant value returning functions.
• Pseudeo columns.
• The DISTINCT function.

What is NULL?

• NULL represents nothing. This query retrieves NULL.

  SELECT NULL FROM DUAL;
  
  N
  -
  
  

• Not even a space character is NULL. This query will return no rows at all. The subquery retrieves a space character, named as COL1. The calling query attempts to return all NULL results, since there is a space returned in a single row, no rows are returned.

  SELECT ' ' AS col1 FROM DUAL;
  
  C
  -
  
  
  SELECT col1 FROM (SELECT ' ' AS col1 FROM DUAL) WHERE col1 IS NULL;
  
  no rows selected
  

• NULL is not the same as zero. Again these queries demonstrate NULL being NULL and not zero valued or a space character.

  SELECT 0 AS col1 FROM DUAL;
  
        COL1
  ----------
           0
  
  SELECT col1 FROM (SELECT 0 AS col1 FROM DUAL) WHERE col1 IS NULL;
  
  no rows selected
  

• Most functions return NULL when passed NULL.

  SELECT SUM(NULL) FROM author;
  
   SUM(NULL)
  ----------
  
  

• An expression containing a NULL returns NULL.

  SELECT 5+NULL FROM DUAL;
  
      5+NULL
  ----------
  
  

• NULL values sort as the highest value. This first example sorts the NULL values to the end of the list.

  SELECT print_date FROM edition ORDER BY 1;
  
  PRINT_DAT
  ---------
  31-JAN-51
  31-MAY-79
  28-FEB-83
  28-APR-83
  31-MAY-85
  31-DEC-85
  31-JUL-86
  30-NOV-90
  31-JUL-96
  31-JAN-20
  
  
  
  
  
  
  
  
  17 rows selected.
  

  This second example sorts in descending order and places NULL values at the beginning of the list.

  SELECT print_date FROM edition ORDER BY 1 DESC;
  
  PRINT_DAT
  ---------
  
  
  
  
  
  
  
  31-JAN-20
  31-JUL-96
  30-NOV-90
  31-JUL-86
  31-DEC-85
  31-MAY-85
  28-APR-83
  28-FEB-83
  31-MAY-79
  31-JAN-51
  
  17 rows selected.
  

Some Other Things About NULL

The NVL Function

The NVL function can be used to replace NULL values with other values.

SELECT NVL(pages, 0) FROM edition;

NVL(PAGES,0)
------------
         590
         256
         304
         320
         480
         480
        1232
         435
           0
         285
           0
           0
         234
           0
         640
         608
         352

17 rows selected.

Testing for NULL Values

The IS [NOT] NULL conditional can be used to test for NULL values. In this case we get 13 instead of the previous 17 rows; the NULL values have been left out.

SELECT pages FROM edition WHERE pages IS NOT NULL;

     PAGES
----------
       590
       256
       304
       320
       480
       480
      1232
       435
       285
       234
       640
       608
       352

13 rows selected.

Why is there a DUAL Table?

• Every SELECT statement creates an implicit cursor.
• A cursor is an area in memory allocated for the results of an SQL statement.
• SELECT statements require a source table for the implicit cursor to operate on.
• Some SELECT statements do not retrieve from any particular table.
• The DUAL table is a repository for an expression result applied to a single value.
• The DUAL table acts as a temporary repository for the result of an expression.
• The result of the expression is selected from the DUAL table.

The DUAL Table has Multiple Uses

Selecting from the DUAL table retrieves the X character from the DUMMY column in the DUAL table.

set linesize 40;
desc dual;

 Name              Null?    Type
 ----------------- -------- ------------
 DUMMY                      VARCHAR2(1)

set linesize 132;

SELECT * FROM DUAL;

D
-
X

The next command concatenates two queries, one from the DUAL table, namely "X". In this case a constant value is applied to every row.

SELECT a.*, b.* FROM DUAL a, author b;

D  AUTHOR_ID NAME
- ---------- --------------------------------
X          1 Orson Scott Card
X          2 James Blish
X          3 Isaac Azimov
X          4 Larry Niven
X          5 Jerry Pournelle
X          6 William Shakespeare

6 rows selected.

This statement simply returns a string.

SELECT 'This is a string' FROM DUAL;

'THISISASTRING'
----------------
This is a string

The DUAL table can be used to retrieve Oracle constants.

SELECT USER,UID,SYSDATE,SYSTIMESTAMP FROM DUAL;

USER                                  UID SYSDATE   SYSTIMESTAMP
------------------------------ ---------- --------- -------------------------------------
BOOKS                                  32 25-SEP-03 25-SEP-03 02.00.22.397000 PM -07:00

Constant Value Returning Functions

As seen in the previous section on the DUAL table these values are constants.

• SYSDATE
• SYSTIMESTAMP
• USER
• UID

Pseudeo-Columns

Pseudo-Columns Uses

• Non-constant Values.
• Dynamic values not stored in a database.
• Values are generated on request.

Some Example Pseudo-Columns

Sequences

• <sequence>.CURRVAL finds the current value of a sequence assuming activated in the current session.
• <sequence>.NEXTVAL finds the next sequential value for a sequence.

ROWID Pointer

A ROWID gives a logical pointer to a row in a database made up of the following values.

• Tablespace identifier
• Datafile block
• Data block row
• Tablespace datafile number

SELECT ROWID, name FROM author;

ROWID              NAME
------------------ --------------------------------
AAACAAAADAAAEQcAAA Orson Scott Card
AAACAAAADAAAEQcAAB James Blish
AAACAAAADAAAEQcAAC Isaac Azimov
AAACAAAADAAAEQcAAD Larry Niven
AAACAAAADAAAEQcAAE Jerry Pournelle
AAACAAAADAAAEQcAAF William Shakespeare

6 rows selected.

ROWNUM

ROWNUM gives the sequence number of a row retrieved in a SELECT statement result set. ROWNUM returns the number of the row retrieved.

SELECT ROWNUM,name FROM author;

    ROWNUM NAME
---------- --------------------------------
         1 Orson Scott Card
         2 James Blish
         3 Isaac Azimov
         4 Larry Niven
         5 Jerry Pournelle
         6 William Shakespeare

6 rows selected.

In this case the rows are resorted, resulting in the ROWNUM being jumbled up. This is because sorting with the ORDER BY clause occurs after retrieval from the database.

SELECT ROWNUM,name FROM author ORDER BY name;

    ROWNUM NAME
---------- --------------------------------
         3 Isaac Azimov
         2 James Blish
         5 Jerry Pournelle
         4 Larry Niven
         1 Orson Scott Card
         6 William Shakespeare

6 rows selected.

The next two queries show an interesting difference. In the first the WHERE clause is applied before the ORDER BY clause and thus incorrect rows are returned. The second shows ROWNUM values of 2 and 3, somewhat misleading, since those ROWNUM values stem from the subquery, not the calling query.

SELECT ROWNUM,name FROM author WHERE ROWNUM < 3 ORDER BY name;

    ROWNUM NAME
---------- --------------------------------
         2 James Blish
         1 Orson Scott Card

SELECT * FROM(SELECT ROWNUM,name FROM author ORDER BY name) WHERE ROWNUM < 3;

    ROWNUM NAME
---------- --------------------------------
         3 Isaac Azimov
         2 James Blish

LEVEL

LEVEL gives the level of a row in a hierarchy. The following query executes as a hierarchical query executing against the PLAN_TABLE table using the LEVEL pseudo column.

COL Query FORMAT a44;
COL Pos FORMAT 990;
COL Cost FORMAT 999990;
COL Rows FORMAT 9999990;
COL Bytes FORMAT 99999990;
COL Sort FORMAT 99999990;
COL IO FORMAT 9999990;
COL CPU FORMAT 99999990;

SELECT	TRIM(LEVEL)||'. '||LPAD (' ', LEVEL - 1)||operation||' '||options||' on '||object_name "Query"
	,cost "Cost"
	,cardinality "Rows"
	,bytes "Bytes"
	,search_columns "SC"
	,decode(level,1,0,position) "Pos"
	,temp_space "Sort"
	,io_cost "IO"
	,cpu_cost "CPU"
FROM	plan_table
WHERE	statement_id = 'TEST'
CONNECT BY prior id = parent_id AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = 'TEST'
ORDER BY id;

XMLDATA

XMLDATA presents an XML data structure.

The DISTINCT Function

DISTINCT is used to retrieve unique values from a list of values, perhaps containing duplicates, thereby removing duplicate values. Let's start with the following query, containing a few duplicates.

SELECT publisher_id FROM edition;

PUBLISHER_ID
------------
           1
           2
           3
           4
           4
           4
           5
           7
           8
           2
           9
           9
          10
           8
          11
           8
           2

17 rows selected.

Now remove the duplicates.

SELECT DISTINCT(publisher_id) FROM edition;

PUBLISHER_ID
------------
           1
           2
           3
           4
           5
           7
           8
           9
          10
          11

10 rows selected.

DISTINCT can be used in a number of ways.

SELECT COUNT(publisher_id) FROM edition;
SELECT COUNT(DISTINCT(publisher_id)) FROM edition;

DISTINCT can operate on individual or multiple columns where strings are compared for uniqueness.

SELECT DISTINCT publisher_id, ISBN FROM edition;
SELECT DISTINCT(TO_CHAR(publisher_id)||', '||TO_CHAR(ISBN)) FROM edition;