MCS-274: Queries with Nested Tables

For the purposes of this course, we will limit ourselves primarily to what are known as "unnesting" queries. In particular, we won't make any use of the cursor feature mentioned in your textbook.

All the examples on this web page use the movies database you will be working with in lab 2. You can create that database using the script ORmovies.sql.

The first thing to know is that whenever you use a nested table as a source to select from, you need to wrap it with table(...). For example, suppose we want a list of the movies, showing for each one the title of the movie and the number of actors who appear in it. We could use the following query:

select m.title, (select count(*) from table(m.actors))
from movies m;

Note in particular that in order to select a count of rows from the movie's nested table of actors, we selected from table(m.actors).

The same principle holds even if we move the subselect into a where clause; as long as the subselect is selecting from a nested table, we need table(...). For example, suppose we just want to know which movies have no actors at all in them. We could test whether the count of actors is zero:

select m.title
from movies m
where (select count(*) from table(m.actors)) = 0;

or we could equivalently check that no actor exists for the movie:

select m.title
from movies m
where not exists (select * from table(m.actors));

Both variants use table(...) because a selection from the nested table is occurring. As it turns out, Oracle provides features that would allow us to more directly formulate our queries by applying functions to the nested tables, rather than by selecting from them. In these versions, we do not use table(...):

select m.title, cardinality(m.actors)
from movies m;

select m.title
from movies m
where m.actors is empty;

Returning to the use of table(...) to indicate selection from a nested table, you can also use this feature in the main select, rather than only in subselects as shown thus far. For example, let's consider queries of the following form:

select ...
from movies m, table(m.actors) a
where ...;

In this query, the selection is joining together each row from the movies table with the rows from that particular movie's nested table of actors. This does not form a full cross product of every movie with every actor. Rather, even without any explicit restriction in the where clause, the combinations are limited to sensible ones, where the actor and movie go together. This may be a bit surprising to some students, but really is just an extension of what we saw in the earlier queries. For example, we only counted the actors who appeared in a particular movie, rather than counting all actors whatsoever.

Now we can turn to filling in the ... parts of the prior query to get a real example. This brings us face to face with the second big thing you need to know about working with nested tables. Namely, you cannot access an attribute of a particular actor, such as the last name, by using an expression like a.lname. This is because the nested table does not contain columns called lname and fnames. Instead, it contains only a single column, which is a reference to a person object (in the separate persons table), which has the lname and fnames attributes. Thus, we need to explicitly navigate from a (the row in the nested table) to the lname attribute by way of that intervening column. Because that column was never given an explicit name, Oracle automatically gives it the pseudoname column_value. Don't worry if this is very mysterious to you; it has mystified a lot of far more experienced computer scientists as well. All you need to remember is to use an expression like a.column_value.lname rather than the incorrect a.lname.

As a concrete example, if what we want to produce is a table of movie titles and the corresponding last names of actors who appeared in those movies, we would use the following query:

select m.title, a.column_value.lname
from movies m, table(m.actors) a;

That query would produce a rather long result table, so a better example might use a where clause to limit the rows produced. For example, consider the following trivia question. What cases exist where a movie title has the same length as the last name of one of the actors in that movie? Given that there is an SQL function length(...) for finding the length of a string, this trivia question can be answered with the following query:

select m.title, a.column_value.lname
from movies m, table(m.actors) a
where length(m.title) = length(a.column_value.lname);