Spread the love

In some cases, you may want to retrieve all rows from a table, especially for small tables such as language. Most of the time, however, you will not want to retrieve every row from a table but will want a way to filter out those rows that are not of interest. This is a job for the where clause.

The where clause is the mechanism for filtering out unwanted rows from your result set.

For example, perhaps you are interested in renting a film but you are only interested in movies rated G that can be kept for at least a week. The following query employs a where clause to retrieve only the films meeting these criteria:

SELECT title
FROM film
WHERE rating = 'G' AND rental_duration >=7;

In this case, the where clause filtered out 971 of the 1000 rows in the film table. This where clause contains two filter conditions, but you can include as many conditions as are required; individual conditions are separated using operators such as and, or, and not.

Let’s see what would happen if you change the operator separating the two conditions from and to or:

SELECT title
FROM film
WHERE rating = 'G' OR rental_duration >=7;

When you separate conditions using the and operator, all conditions must evaluate to true to be included in the result set; when you use or, however, only one of the conditions needs to evaluate to true for a row to be included, which explains why the size of the result set has jumped from 29 to 340 rows. So, what should you do if you need to use both and and or operators in your where clause? Glad you asked. You should use parentheses to group conditions together. The next query specifies that only those films that are rated G and are available for 7 or more days, or are rated PG-13 and are available 3 or fewer days, be included in the result set:

SELECT title, rating, rental_duration
FROM film
WHERE (rating = 'G' AND rental_duration >= 7)
	OR (rating = 'PG-13' AND rental_duration < 4);

You should always use parentheses to separate groups of conditions when mixing different operators so that you, the database server, and anyone who comes along later to modify your code will be on the same page.

Rating: 1 out of 5.

Leave a Reply