WHERE the wild things are

1. WHERE the wild things are

2. Multiple WHERE conditions

You might need to apply more than one WHERE condition to your query. With AND, OR, and the correct use of parentheses, we can write queries to meet requirements and really impress our colleagues.

3. AND

On the left hand, the query returns records from the songlist table where the artist is AC/DC, giving us 29 rows in total. On the right-hand side, we add a further condition using `AND` after the WHERE clause, limiting records to those released prior to 1980. This reduces the number of rows that are returned to 9.

4. AND again

Here's another example. By adding an additional AND statement, we reduce the number of rows returned from 3, to 1.

5. OR

Can you guess what this query might return? That's right - it will retrieve songs released in 1994.

6. OR(2)

What about now? That's right, it also returns any song released after 2000. Using OR, we specify an alternate condition, and if a record meets either of the conditions, it is returned.

7. OR (3)

Let's try another example. The top query returns Green Day songs from 1994 - a total of 3 rows. The middle query returns Green Days songs released after the year 2000. This also returns 3 rows.

8. OR (4)

Now, if I ask for songs by Green Day, released in 1994 OR after 2000, how many rows will I retrieve? Six? Let's see the results... Whoa. That's not good. We've got more than 6 rows. Why do you think this happened?

9. What went wrong?

Let's figure this out. We asked for songs by Green Day, released in 1994 or after 2000. But, actually, we specified 2 completely different sets. The OR keyword specifies a different condition. So we actually asked for either songs by Green Day and released in 1994. OR any song, by any artist, released after 2000. So how do we fix this?

10. Parentheses for the win..

By wrapping parentheses around the 2 release_year clauses, we make it clearer that we want songs by Green Day, (and only Green Day) with a release_year of 1994 or greater than 2000). You could also write a slightly longer query, as shown on the bottom left. The top query avoids repeating the artist condition, but the bottom query is perhaps even clearer. AND / OR combinations can be tricky. You want one or more common conditions, and one or more varying conditions. Use parentheses to ensure the common conditions (in this case, the artist) stay common to both subsets, while the different conditions are also applied. If you are not getting the results you expect, don't be afraid to write out a longer query, using additional parentheses to make the meaning of the query clearer.

11. IN it to win it

We've seen how to use BETWEEN to select a range of numerical values. We can use IN to perform a similar selection for either text or numeric values. On the left, we filter for various artists, and on the right, for various release_years.

12. I LIKE it

You can use LIKE, along with the percent sign (%) to perform wildcard searches on text fields. To filter songs beginning with the letter 'A', you would type LIKE, then an apostrophe,then the letter a, the percent sign,and finally, another apostrophe. Likewise, to filter for artists beginning with 'f', you would write the query on the top right.

13. Let's practice!

That wraps up this lesson, let's go and try out our new skills.

Create Your Free Account

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