Latest Posts

Natural Joins / Outerjoins

As we recall from Natural Joins / Theta-Joins, a natural join differs from a theta-join in that: 1. The join condition is that all pairs of attributes from the two relations having a common name are equated, and there are no other conditions. 2. One of each pair of equated attributes is projected out.

Subqueries in FROM Clauses

Another use for subqueries is as relations in a FROM clause. In a FROM list, instead of a stored relation, we may use a parenthesized subquery. Since we don't have a name for the result of this subquery, we must give it a tuple-variable alias.

Correlated Subqueries

The simplest subqueries can be evaluated once and for all, and the result used in a higher-level query. A more complicated use of nested subqueries requires the subquery to be evaluated many times: once for each assignment of a value to some term in the subquery that comes

Conditions Involving Tuples

A tuple in SQL is represented by a parenthesized list of scalar values. Examples are (123, foo) and (name, address, networth). The first of these has constants as components; the second has attributes as components. Mixing of constants and attributes is permitted.

Subqueries

In SQL, one query can be used in many ways to help in the evaluation of another. A query that is part of another is called a subquery. Subqueries can have subqueries, and so on, down as many levels as we desire. We already saw one example of the use of subqueries; in

Union, Intersection, and Difference of Queries

Sometimes we wish to combine relations using the set operations of relational algebra: union, intersection and difference. SQL provides corresponding operators that apply to the results of queries, provided those queries produce relations with the same list of attributes and

Interpreting Multirelation Queries

There are many ways to define the meaning of the select-from-where expressions that we have just studied. All are equivalent, in the sense that they each give the same answer for each query applied to the same relation instances. We shall examine each in turn.

Tuple Variables

Disambiguating attributes by prefixing the relation name works as long as the query involves combining many different relations. On the other hand, sometimes we need to ask a query that involves two or more tuples from the same relation,

Disambiguating Attributes

Sometimes we ask a query involving many relations, and among these relations are two or more attributes with the same name. If so, we need a way to indicate which of these attributes is meant by a use of their shared name. SQL solves this problem by allowing us to place a

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