When we perform our SQL queries, we often want to select one or more columns of data for the final result. However, it’s in our best interest to filter our results so that we only get a smaller set of results than the full database. From potentially thousands to millions of entries, we can filter specifically to entries that concern us, and not look at any unnecessary or extraneous data. This is why filtration with WHERE is such an important topic of discussion.
When working with WHERE, we want to deal with boolean expressions, which essentially tell us if a statement is true or false. The query filters out all of the false information and gives us anything that is true. To see the kind of power that these boolean expressions can have, we can explore boolean operators and keywords.
Comparison Operators
<, <=, =, >=, >
(Less Than, Less Than or Equal, Equal, Greater Than or Equal, Greater Than)
No, that’s not a really clever arrow emoji. We can use these specific comparison operators when we are looking at numerical values. For example, if we wanted to see all the items priced at $400 or under, we would write a query:
SELECT item, price
FROM purchases
WHERE price <= 400;
Another example of this is if we wanted to see if something was purchased in New York:
SELECT item, state
FROM purchases
WHERE state = “NY”;
Note that in other programming languages, the equal operator is ==, but in SQL, our equal operator is simply =. This is because SQL only accepts queries, and we are never introducing any new code, functions or programs to our database.
AND, OR, NOT
We can filter out multiple columns or extend the functionality of our filters by adding AND, OR, or NOT as part of our where statement. If we wanted to see any items purchased in New York that were $400 or less (and order them by descending price), we would write:
SELECT item, state, price
FROM purchases
WHERE state = “NY” AND price <= 400
ORDER BY price DESC;
LIKE - SQL’s Search Tool:
Search engines are often used daily, and our searches on the web perform much like a search in SQL. The LIKE keyword, when used in conjunction with WHERE allows us to perform a search on the column, only returning items that resemble the text we want. To use LIKE, we add the wildcard symbol (%) before or after depending on where the text is inside our target string. For example:
WHERE item LIKE ‘%Book’
will return items that end in Book, such as “Jungle Book”
WHERE item LIKE ‘Book%’
Will return items that begin with Book, such as “Bookstore” and
WHERE item LIKE ‘%Book%’
Will return the previous two, as well as “Jungle Bookstore”
Note that our searches in SQL are case sensitive, meaning that capitalization would return a different result. In order to ignore case, we would write:
WHERE item COLLATE UTF_GENERAL_CI LIKE ‘%Book%’
By using WHERE, we can now select portions of columns based off of filters we want to apply. This allows us to extract the data we want. In the next article, we will use GROUP BY to explore aggregates and grouping, which will show us how to perform group tasks on our columns, such as counting, averaging, and summing.