Queries with EXISTS

1. Queries with EXISTS

The EXISTS function is another handy feature in SQL.

2. EXISTS

It is a special case of a correlated nested query and allows us the check whether the result of a correlated nested query is empty or not. The EXISTS function returns a boolean value, which is either TRUE or FALSE. TRUE is returned if the result of the correlated nested query has at least one row, that means it is not empty. FALSE is returned if the query returns an empty table. When EXISTS returns TRUE the corresponding row of the outer query is selected. Since the EXISTS function only evaluates whether a nested query outputs any rows, it doesn't matter which columns are specified in the SELECT component. So we can always simply use SELECT star for the subquery.

3. Movies with at least one rating

Here we have an example of a query with the EXISTS function. We are interested in a list of movies for which there exists at least one rating. A movie is excluded from the final result if the table in the inner query is empty, that is if the number of rows of the results table from the inner query is zero. Let's have a look at the result of the inner query for a specific movie ID.

4. Movies with at least one rating

Here we select all records from the table renting where rating is not null and the movie ID is 11. This query returns an empty table.

5. Movies with at least one rating

For the movie with ID 1, however, the table is not empty. Hence, the movie with ID 1 will be part of the result from the outer select statements, whereas the movie with ID 11 will be excluded.

6. EXISTS query with result

Here is the query with the EXISTS function and the first two rows of the resulting table. The movie with ID 1 is part of the result, since it has at least one rating.

7. NOT EXISTS

The function `NOT EXISTS` returns TRUE if the table is empty - if no record exists in the inner query. The rows in the outer query are selected if `NOT EXISTS` is TRUE. We replace the EXISTS in the previous query with NOT EXISTS. With this query we select those movies which don't have any ratings. We already know that the movie with ID 11 doesn't have any ratings, so for this movie, the resulting table from the inner query will be empty. The result of this correlated query with NOT EXISTS function shows that the movie with ID 11 - the movie Showtime - is the only one without any ratings.

8. Let's practice!

Now it's time for you to practice queries with EXISTS function.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.