Queries Involving More Than One Relation

Queries Involving More Than One Relation

Much of the power of relational algebra comes from its ability to combine two or more relations through joins, products, unions, intersections, and differences. We get all of these operations in SQL. The set-theoretic operations - union, intersection, and difference - appear directly in SQL, as we shall learn in Union, Intersection, and Difference of Queries. First, we shall learn how the select-from-where statement of SQL allows us to perform products and joins.

Products and Joins in SQL

SQL has a simple way to couple relations in one query: list each relation in the FROM clause. Then, the SELECT and WHERE clauses can refer to the attributes of any of the relations in the FROM clause.

Example 1 : Assume we want to know the name of the producer of Star Wars. To answer this question we need the following two relations from our running example:

Movie(title, year, length, inColor, studioName, producerC#)
MovieExec(name, address, cert#, netWorth)

The producer certificate number is given in the Movie relation, so we can do a simple query on Movie to get this number. We could then do a second query on the relation MovieExec to find the name of the person with that certificate number.

However, we can phrase both these steps as one query about the pair of relations Movie and MovieExec as follows:

SELECT name
FROM Movie, MovieExec
WHERE title = Star Wars AND producerC# = cert#;

This query asks us to consider all pairs of tuples, one from Movie and the other from MovieExec. The conditions on this pair are stated in the WHERE clause:

1. The title component of the tuple from Movie must have value 'Star Wars'.

2. The producerC# attribute of the Movie tuple must be the same certificate number as the cert# attribute in the MovieExec tuple. That is, these two tuples must refer to the same producer.

Whenever we find a pair of tuples satisfying both conditions, we produce the name attribute of the tuple from MovieExec as part of the answer. If the data is what we expect, the only time both conditions will be met is when the tuple from Movie is for Star Wars, and the tuple from MovieExec is for George Lucas. Then and only then will the title be correct and the certificate numbers agree. Thus, George Lucas should be the only value produced. This process is suggested in Figure 1. We take up in more detail how to interpret multirelation queries in Interpreting Multirelation Queries.

The query of Example 1 asks us to pair every tuple of Movie

Tags