The Internet Encyclopedia (Volume 3)

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

SQL ̇OLE WL040/Bidgolio-Vol I WL040-Sample.cls June 20, 2003 13:16 Char Count= 0

358 STRUCTUREDQUERYLANGUAGE(SQL)

value of inventory by product line) as opposed to running

them on an entire result set (such as an entire table’s con-

tents).

Examples

How many movies of each rating are there?

select count(*), MOVIE_RATING

from MOVIE

group by MOVIE_RATING;

Results:

COUNT(*) MOVIERATING

3PG

6 PG-13

11 R

The various functions can be used without thegroup

byclause; however, the value is computed for the entire

table. The COUNT(*) function (without thewhereclause)

is useful to determine the number of rows that a table

contains.

Reducing the Segregated Data

Now that our data are partitioned and we have some cal-

culated information about them, we may want to reduce

the number of rows that are output. The proper way to

accomplish this is to use thehavingclause. If we continue

with the above example, and now only want to view the

data associated with the PG and PG-13 grouped values,

we could add ahavingclause like this:

select count(*), MOVIE_RATING

from MOVIE

group by MOVIE_RATING

having MOVIE_RATING like 'PG%';

Note: thehavingclause must be used in conjunction

with agroup byclause, though agroup byclause can exist

without ahavingclause.

Handling Null Values

Remember that null is a special value that can be assigned

to an attribute. Most functions, like COUNT(), will ignore

a column with a null value. Other calculations, like math-

ematical operations, treat null as infinity, so that when we

add a number to a null value, the result is also a null—

that is the result is unknown. There may be times when

we want to search out these unknown or not applicable

values to fulfill some business query. To search for an at-

tribute with a null value we cannot use the “=” com-

parison operator; we must use the keywordis, yielding

an expression of the form “MOVIERATING is null.” Of

course, the converse to “is null” exists: it is “is not null.”

Many RDBMSs also provide replacement mechanisms for

generating reports, so that the null value does not inter-

fere with calculations and the like. Refer to an RDBMS’s

user guide for the exact syntax.

Joins

So far the various options have been demonstrated against

a single table, but if we are using a relational database,

then the relationships between tables are equally impor-

tant. To retrieve result sets with data from multiple tables,

we need to use a join. There are several types of joins de-

fined: equi, natural, Cartesian, inner, outer, and self. The

equi and natural joins are the most common; the inner,

outer, self, and Cartesian are special cases. In all cases, all

tables that participate in a join must be listed in thefrom

clause.

Equi-joins

An equijoin combines the data from two or more tables

based on a match defined in thewhereclause. For exam-

ple, if we wanted to count the number of tickets sold for

a particular movie, we would enter:

select count(*)

from TICKETS_SOLD, MOVIE

where TICKETS_SOLD.MOVIE_ID = MOVIE.

MOVIE_ID and

MOVIE_TITLE = 'DRIVEN';

Natural

A natural join is a specific instance of an equijoin. The join

columns must have the same name. The fields must be the

same length and data type. When the result set is gener-

ated, only one instance of the join column is included.

Cartesian

The product of a Cartesian join is all possible combina-

tions of data combined in one result set. If we have a table

A with three rows and a table B with two rows, the Carte-

sian product result will have six rows:

A

A1

A2

A3

B

B1

B2

select * from A,B

A1 B1

A1 B2

A2 B1

A2 B2

A3 B1

A3 B2

Union

The union operation combines the result sets of two or

more queries together into a new result set. This corre-

sponds directly with the union operation in set theory. The

only restriction is that the result sets being joined must be

union-compatible. That is, the numbers of columns in the

result sets to be joined must be the same. Additionally, ev-

ery corresponding column must be of the same datatype

(that is, column 1 of result set 1must be the same datatype

as all of the other column 1s from all of the other result

sets being so joined). It is up to the developer to ensure

that the meaning of the columns is consistent between re-

sult sets. Unions are useful when combining related data