Jun 23, 2020 by Robert Gravelle

A short time ago we were introduced the incredibly useful and versatile Case Statement. In that blog, we employed the Case Statement as most DBAs and developers do, in the SELECT clause. Another way to use the Case Statement is within the WHERE clause. There, it may be utilized to alter the data fetched by a query based on a condition. Within that context, the Case Statement is ideally suited to both static queries, as well as dynamic ones, such as those that you would find inside a stored procedure. In today's blog, we'll create a SELECT query in Navicat Premium that returns rows based on the values of another field.

Listing Films by Rental Duration

Before getting to the CASE Statement, let's start with a query that returns a list of movies from the from the Sakila Sample Database. It's a MySQL database that contains a number of tables, views, and queries related to a fictional video rental store. Tables include actor, film, customer, rentals, etc.by film id, title, rental rate, and a rental duration of 5 days.

Our query displays the film id, title, rental rate, and a rental duration columns and narrows down the field to those films whose rental_duration is exactly 5 days. Here's is the statement:

SELECT film_id, title, rental_rate, rental_duration 
FROM film 
WHERE rental_duration = 5 
ORDER BY rental_rate DESC;

Executing the query in Navicat Premium brings up the following results:

Setting Rental Duration Based On Rental Rate

A Case Statement comes in handy for choosing a value based on several other possible values. For instance, let's suppose that we wanted to set the rental_duration based on the rental_rate. We could do that using a Case Statement like so:

SELECT film_id, title, rental_rate, rental_duration 
FROM film 
WHERE rental_duration = CASE rental_rate
	                        WHEN 0.99 THEN 3
	                        WHEN 2.99 THEN 4
	                        WHEN 4.99 THEN 5
	                        ELSE 6
                        END 
ORDER BY title DESC;

That has the effect of associating rental_rates with the rental_durations. Hence:

• When the rental_rate is equal to 0.99, only include films whose rental_duration is equal to 3.
• When the rental_rate is equal to 2.99, only include films whose rental_duration is equal to 4.
• When the rental_rate is equal to 4.99, only include films whose rental_duration is equal to 5.
• For any other rental_rate, only include films whose rental_duration is equal to 6.

We can see the results in the screen capture below:

Notice how whenever a film has a rental rate of 0.99, the rental_duration is always 3. Likewise, films with a rental rate of 2.99 all have a rental_duration of 4, etc...

Rewriting the CASE Statement

Remember that the Case Statement is only an anternative way of combining two or more OR conditions. As such, we can rewrite our query without the Case Statement, but, as you can see, it takes a lot more SQL:

SELECT film_id, title, rental_rate, rental_duration 
FROM film 
WHERE rental_rate = 0.99 AND rental_duration = 3
  OR rental_rate = 2.99 AND rental_duration = 4
  OR rental_rate = 4.99 AND rental_duration = 5
  OR rental_rate NOT IN (0.99, 2.99, 4.99) AND rental_duration = 6
ORDER BY title DESC;

Here are the results. Compare these with that of the CASE query:

Conclusion

In today's blog, we created a SELECT query in Navicat Premium that returns a list of films using a CASE Statement in the WHERE clause. Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!