Using Operators in a Subquery

The Exist Operator & Correlated References

The EXISTS operator checks whether a subquery is empty or not, instead of checking whether values are in the subquery.

A correlated reference is where you use a value inside a subquery, that comes from outside that subquery.

Lets look at an example:

SELECT mID, Rating
FROM Review R1
WHERE EXISTS (SELECT * FROM Review R2
              WHERE R1.Rating = R2.Rating and R1.mID <> R2.mID);

This query will return all movies that have the same rating. First we're going to take the mID's from R1. Then we're creating a new relation called R2. For each movie we're going to check if there is another mID, where the Rating in R2 is the same as the Rating in R1. We then say that each mID should be different, and not equal to itself.

We use a correlated reference to use an outside variable inside a subquery.

Looking for a Largest Value

Assume that you wanted to look for the largest value of some element. In this case, we want to find the movie that was most recently created. Thus, the movie's Year would be the largest.

We could write a query that looks like this:

SELECT Title, Year
FROM Movie M1
WHERE NOT EXISTS (SELECT * FROM Movie M2
                  WHERE M1.Year < M2.Year);

This query says that we are going to find all movies where there does not exist another movie whose Year is greater than the first movie. This would be a form of query that we could write whenever looking for the greatest value of some-sort.

The resulting movie would be: Gravity.

The All Operator

The ALL keyword tells us that instead of checking whether a value is in or not in the result of a subquery, we're going to check if the value has a certain relationship with ALL the results of a subquery.

Lets create a query that checks to see if the Rating of a movie is greater than or equal to ALL elements of the subquery which returns all the Ratings of each movie.

It would look like this:

SELECT mID, Rating
FROM Review
WHERE Rating >= all (SELECT Rating FROM Review);

We would then get an output table of all the movie's with a Rating of 5, since there is no single movie with a greater Rating than every other movie.

The output table would include the movies: Gravity, and Titanic.

The Any Operator

The ANY keyword performs very similar to the ALL keyword, except instead of having to satisfy a condition with ALL of the elements of a set, it only has to satisfy a condition with at least one element of a set.

Lets create a query that finds all movies that have a Year that is not the smallest Year value. In other words, we are looking for movies whose Year is greater than ANY other movie Year.

Our query would look like this:

SELECT Title, Year
FROM Movie
WHERE Year > ANY (SELECT Year FROM Movie);

In the above example query, a movie will be returned if there is some other movie whose Year is less than this movie. We then get a resulting table with all the movies that do not have the least Year value. Thus, we would get every movie except for The Godfather, because it has the smallest Year value.

Conclusion on Operators

The ANY and ALL operators are very convienient when creating queries, however, they are not vital to creating a query. We can always write a query that would normally use the ANY or ALL keywords, by using the EXISTS or NOT EXISTS operators.