Learn to write SQL WHERE clauses that actually work. Use AND, OR, and parentheses to filter data precisely. BETWEEN finds ranges, IN picks from a list, and LIKE searches text with wildcards.
Chapters
You might need to apply more than one where condition to your query with and, OR and the correct use of parenthesis, we can write queries to meet requirements and really impress our colleagues. On the left hand, the query returns records from the song list table where the artist is ACDC, 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 rows released prior to 1980. This reduces the number of rows that are returned to nine. Here's another example. By adding an additional and statement, we reduce the number of rows returned from three to one. Can you guess what this query might return? That's right. It will retrieve songs released in 1994. 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. Let's try another example. The top query returns Green day songs from 1994 a total of three rows. The middle query returns Green Day songs released after the year 2000. This also returns three rows. 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 six rows. Why do you think this happened? Let's figure this out. We asked for songs by Green day released in 1994 or after 2000, but actually we specified two 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? By wrapping parenthesis around the two 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 parenthesis to ensure the common conditions. In this case, the artist stay common to both subsets, while the different conditions are also applied. If you're not getting the results you expect, don't be afraid to write out a longer query using additional parenthesis to make the meaning of a query clearer. 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, you can use like along with a 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. That wraps up this lesson. Let's go and try out our new skills.
