level up

«previous next»

SELECT
DML
DDL
Quick Start

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';

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';

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');

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.

dml