SQL Basics

Searchâ€¦

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:

1

SELECT mID, Rating

2

FROM Review R1

3

WHERE EXISTS (SELECT * FROM Review R2

4

WHERE R1.Rating = R2.Rating and R1.mID <> R2.mID);

Copied!

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:

1

SELECT Title, Year

2

FROM Movie M1

3

WHERE NOT EXISTS (SELECT * FROM Movie M2

4

WHERE M1.Year < M2.Year);

Copied!

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:

1

SELECT mID, Rating

2

FROM Review

3

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

Copied!

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:

1

SELECT Title, Year

2

FROM Movie

3

WHERE Year > ANY (SELECT Year FROM Movie);

Copied!

In the above example query, a movie will be returned if there is some other movie whose *The Godfather*, because it has the smallest

`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 `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.Last modified 1yr ago