lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



Oracle SELECT Statement


The SELECT statement is used to retrieve data from database tables.

We use this COUNTRY table to demonstrate the various features of this command:

COUNTRY
CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 Germany 137882 82046000 30-nov-08 Euro
48 Ghana 92100 23837000 null Cedi
53 Australia 2969907 21884000 4-sep-09 Australian Dollar
73 Greece 50949 11257285 1-Jan-09 Euro
122 Georgia 26900 4382100 1-Jan-09 Lari
123 New Zealand 104428 4320300 4-sep-09 New Zealand Dollar
147 Gambia 4361 1705000 null Dalasi
149 Gabon 103347 1475000 null CFA franc

Several abbreviations are used to condense the size of the column names.
  • CTRY = country
  • POP = population
  • UPD = updated
  • ON = a suffix which implies a date
POP_UPD_ON contains the date that the population was last updated (if known).
A unique numeric identifier (CTRY_ID) has also been included. We will see why later.

Selection and Projection

A SELECT statement looks like this:
SELECT columns
FROM tables
WHERE conditions
ORDER BY columns;
SELECT and FROM are mandatory, WHERE and ORDER BY are optional, and statements are terminated by a semicolon.

Let's start with the simplest of queries. To retrieve all of the columns and all of the rows in a table, use this SQL:
SELECT *
FROM country;
The asterisk (*) is a quick way to select all columns.

CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 Germany 137882 82046000 30-nov-08 Euro
48 Ghana 92100 23837000 null Cedi
53 Australia 2969907 21884000 4-sep-09 Australian Dollar
73 Greece 50949 11257285 1-Jan-09 Euro
122 Georgia 26900 4382100 1-Jan-09 Lari
123 New Zealand 104428 4320300 4-sep-09 New Zealand Dollar
147 Gambia 4361 1705000 null Dalasi
149 Gabon 103347 1475000 null CFA franc

We can return specific columns by naming them in a comma separated list. This is called projection.
SELECT ctry_name, ctry_area, currency
FROM country;
CTRY_NAME CTRY_AREA CURRENCY
Germany 137882 Euro
Ghana 92100 Cedi
Australia 2969907 Australian Dollar
Greece 50949 Euro
Georgia 26900 Lari
New Zealand 104428 New Zealand Dollar
Gambia 4361 Dalasi
Gabon 103347 CFA franc

To return a subset of rows we include the WHERE clause. This is known as selection or restriction.
SELECT *
FROM country
WHERE currency = 'Euro';
CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 Germany 137882 82046000 30-nov-08 Euro
73 Greece 50949 11257285 1-Jan-09 Euro

And we combine both techniques to target just the values we are interested in.
SELECT ctry_name, ctry_area, currency
FROM country
WHERE currency = 'Euro';
Info String literals are enclosed in single quotes.
CTRY_NAME CTRY_AREA CURRENCY
Germany 137882 Euro
Greece 50949 Euro

Incidentally, SQL is case insensitive so the previous statement could have been written like this:
select CTRY_NAME, CTRY_AREA
from COUNTRY
where CTRY_ID = 53;

Logical Operators

We can combine the results of multiple conditions using AND and OR. For example, suppose we wish to find large countries which use the Euro as their currency:
SELECT ctry_name, ctry_area, ctry_pop, currency
FROM country
WHERE ctry_area >= 100000
  AND currency = 'Euro';
Info The following comparison conditions are available: =, <, >, <=, >=.

Not equals can be != or <>.
CTRY_NAME CTRY_AREA CTRY_POP CURRENCY
Germany 137882 82046000 Euro

Care must be taken with the operator precedence when using AND and OR. Suppose we wish to extend our definition of a large country to include countries with a large population.

We may be tempted to write this SQL:
SELECT ctry_name, ctry_area, ctry_pop, currency
FROM country
WHERE ctry_area >= 100000
  OR ctry_pop >= 10000000
  AND currency = 'Euro';
But these incorrect results would be produced:

CTRY_NAME CTRY_AREA CTRY_POP CURRENCY
Germany 137882 82046000 Euro
Australia 2969907 21884000 Australian Dollar
Greece 50949 11257285 Euro
New Zealand 104428 4320300 New Zealand Dollar
Gabon 103347 1475000 CFA franc

Several countries have been selected which do not use the Euro. This is because the AND condition has greater precedence than OR, so we must use parentheses to override the default.
SELECT ctry_name, ctry_area, ctry_pop, currency
FROM country
WHERE (ctry_area >= 100000
  OR ctry_pop >= 10000000)
  AND currency = 'Euro';
CTRY_NAME CTRY_AREA CTRY_POP CURRENCY
Germany 137882 82046000 Euro
Greece 50949 11257285 Euro

Pattern Matching

Use the LIKE condition to perform pattern matching.
SELECT currency
FROM country
WHERE currency LIKE '%Dollar%';
CURRENCY
Australian Dollar
New Zealand Dollar

% matches zero or more characters.
_ matches exactly one character.
SELECT ctry_name
FROM country
WHERE ctry_name LIKE '_e%';
CTRY_ID
Germany
Georgia
New Zealand

For more sophisticated pattern matching use REGEXP_LIKE.
SELECT currency
FROM country
WHERE REGEXP_LIKE (currency, '.*[Dd]ollar.*');
CURRENCY
Australian Dollar
New Zealand Dollar

Range Condition

BETWEEN provides a range condition. These two statements are equivalent.
SELECT ctry_name, ctry_pop
FROM country
WHERE ctry_pop BETWEEN 500000 AND 10000000;

SELECT ctry_name, ctry_pop
FROM country
WHERE ctry_pop >= 500000
  AND ctry_pop <= 10000000;
Both produce these results:

CTRY_NAME CTRY_POP
Georgia 4382100
New Zealand 4320300
Gambia 1705000
Gabon 1475000

BETWEEN can also be useful when checking date ranges.
SELECT ctry_name, ctry_pop, pop_upd_on
FROM country
WHERE pop_upd_on BETWEEN '30-nov-08' AND '1-jan-09';
CTRY_NAME CTRY_POP POP_UPD_ON
Germany 82046000 30-nov-08
Greece 11257285 1-Jan-09
Georgia 4382100 1-Jan-09

Note that the specified values are inclusive.

Membership Condition

Suppose we wish to test if a column is equal to one of several values in a list. We could use the OR condition like this:
SELECT ctry_name, ctry_area
FROM country
WHERE ctry_name = 'Ghana'
  OR ctry_name = 'Australia'
  OR ctry_name = 'Greece';
But a more convenient solution is to use the IN condition.
SELECT ctry_name, ctry_area
FROM country
WHERE ctry_name IN ('Ghana', 'Australia', 'Greece');
CTRY_NAME CTRY_AREA
Ghana 92100
Australia 2969907
Greece 50949

This is known as a membership condition. Use NOT IN to negate the result.
SELECT ctry_name, ctry_area
FROM country
WHERE ctry_name NOT IN ('Ghana', 'Australia', 'Greece');
This time, all countries not present in the list are returned.

CTRY_NAME CTRY_AREA
Germany 137882
Georgia 26900
New Zealand 104428
Gambia 4361
Gabon 103347

Multi-value expression lists are also possible in Oracle SQL. We can compare both the CURRENCY and POP_UPD_ON columns to values in a list, like this:
SELECT ctry_name, pop_upd_on, currency
FROM country
WHERE (currency, pop_upd_on) IN (
        ('Euro', '30-NOV-08'),
        ('Lari', '01-JAN-09') );
CTRY_NAME POP_UPD_ON CURRENCY
Germany 30-nov-08 Euro
Georgia 1-Jan-09 Lari

Distinct

Suppose we need a list of the currencies used by Germany, Greece and Georgia.
SELECT currency
FROM country
WHERE ctry_name IN ('Germany', 'Greece', 'Georgia');
CURRENCY
Euro
Euro
Lari

The problem here is that the Euro is included twice, once for Germany and once for Greece. However, we can remove duplicates using the DISTINCT keyword.
SELECT DISTINCT currency
FROM country
WHERE ctry_name IN ('Germany', 'Greece', 'Georgia');
Info The UNIQUE keyword is synonymous with DISTINCT
CURRENCY
Euro
Lari

Null Comparison

If a column in a row has no value, then the column is said to contain null.
SELECT ctry_name, pop_upd_on
FROM country;
CTRY_NAME POP_UPD_ON
Germany 30-nov-08
Ghana (null)
Australia 4-sep-09
Greece 1-Jan-09
Georgia 1-Jan-09
New Zealand 4-sep-09
Gambia (null)
Gabon (null)

The nulls in the POP_UPD_ON column indicate that we don't know when the population was updated. There is no meaningful date that we can enter for that row.

We use IS NULL or IS NOT NULL when performing a null comparision.
SELECT ctry_name, pop_upd_on
FROM country
WHERE pop_upd_on IS NULL;
CTRY_NAME POP_UPD_ON
Ghana (null)
Gambia (null)
Gabon (null)
SELECT ctry_name, pop_upd_on
FROM country
WHERE pop_upd_on IS NOT NULL;
CTRY_NAME POP_UPD_ON
Germany 30-nov-08
Australia 4-sep-09
Greece 1-Jan-09
Georgia 1-Jan-09
New Zealand 4-sep-09

Other operators such as = or != will not work with null. All of these SQL statements will return NO rows.
SELECT * FROM country WHERE pop_upd_on = null;
-- 0 rows selected

SELECT * FROM country WHERE pop_upd_on != null;
-- 0 rows selected

SELECT * FROM country WHERE null=null;
-- 0 rows selected

SELECT * FROM country WHERE null!=null;
-- 0 rows selected

Ordering Results

Use the ORDER BY clause to order the results of a query. This example will order the countries by size, largest first:
SELECT ctry_name, ctry_area
FROM country
ORDER BY ctry_area DESC;
CTRY_NAME CTRY_AREA
Australia 2969907
Germany 137882
New Zealand 104428
Gabon 103347
Ghana 92100
Greece 50949
Georgia 26900
Gambia 4361

Append ASC or DESC to the column name to indicate ascending or descending sequence. ASC is the default.

Multiple column names can be specified in the ORDER BY clause and the columns will be ordered with left to right precedence.

This query will order the rows into descending CTRY_NAME within ascending CURRENCY.
SELECT currency, ctry_name
FROM country
ORDER BY currency, ctry_name DESC;
The ordering on CTRY_NAME only comes into play when there are duplicate CURRENCY values such as the Euro.

CURRENCY CTRY_NAME
Australian Dollar Australia
Cedi Ghana
CFA franc Gabon
Dalasi Gambia
Euro Greece
Euro Germany
Lari Georgia
New Zealand Dollar New Zealand

ORDER BY is the only way to guarantee ordering of the result set. Without this clause, rows could be returned in a different sequence from one execution to the next.

Scalar Functions

Oracle includes many useful scalar funtions. For example:
SELECT
  ctry_name || ' has an area of ' || ctry_area || ' sq miles' AS "Country",
  to_char(ctry_pop, '999,999,999') AS "Population",
  nvl(to_char(pop_upd_on, 'ddspth Monthyyyy'), 'Unknown') AS "Updated On",
  trunc(ctry_pop / ctry_area, 2) AS "Density"
FROM country
WHERE soundex(ctry_name) IN (
        soundex('grease'),
        soundex('jorjia'),
        soundex('ganah'));
Country Population Updated On Density
Ghana has an area of 92098 sq miles 23,837,000 Unknown 258.82
Greece has an area of 50949 sq miles 11,257,285 first January 2009 220.95
Georgia has an area of 26900 sq miles 4,382,100 first January 2009 162.9

Double pipe (||) concatenates two strings (CTRY_AREA is converted to a string in the first column).

to_char() uses a number format model (999,999,999) or a datetime format model (ddspth Monthyyyy) to describe the format of the display.

nvl() replaces a null with a specified string (the 'Unknown' string in this case).

trunc() truncates a number to the specified number of decimal places.

soundex() lets you compare words that are spelled differently, but sound alike in English.

We have also introduced a column alias using the AS keyword. Oracle will use this alias as the column heading of the result set. Without this, the first column would be called ctry_name || ' has an area of ' || ctry_area || ' sq miles'

Enclose alias names in double quotes if they contain any special characters such as space or punctuation, or to preserve the case.
»