The WHERE statement is one of the most commonly used statements when writing SQL queries. It is used in filtering data from a table. The WHERE statement filters data when it meets a certain condition or criteria.
YIf you were asked to "Select every student who owns more than 5 apples"
Using a WHERE statement, this will be written as
SELECT students, apples
FROM class
WHERE apples > 5
This means that rows will only be selected if the apple value of that row is greater than 5. The WHERE statement can work with all the comparison operators. These include:
greater than >
less than <
greater than or equal to >=
less than or equal to <=
equal to =
not equal to !=
The WHERE statement can also work with logical operators (AND, OR, LIKE, IN, BETWEEN, and NOT). Logical operators combined with the WHERE statement can be used to select rows based on multiple criteria. They can also be used in conjunction with comparison operators.
AND OPERATOR
The AND operator is used to select rows based on the rows meeting all criteria specified. For example, you work as a data analyst at a restaurant and your boss wants to get a list of customers that bought food worth $1000 and above AND have also visited the restaurant more than 3 times. Your WHERE statement will look like this
SELECT *
FROM customers
WHERE total_amt_spent >= 1000 AND visits >= 3
This query selects all columns and rows from the 'customers' table WHERE the 'total_amt_spent' row is equal to or greater than (>=) $1000 AND the number of visits is equal to or greater than (>=) 3. Using the AND statement, if one of the criteria fails, that row will not be selected. So if a row meets the $1000 criteria but only contains 2 visits, the row will not be selected.
OR OPERATOR
The OR operator is used to select rows if they meet any of the listed criteria. For example, you work as a data analyst at a restaurant and your boss wants to get a list of customers that bought food worth $1000 and above OR have visited the restaurant more than 3 times. Your WHERE statement will look like this
SELECT *
FROM customers
WHERE total_amt_spent >= 1000 OR visits >= 3
This query selects all columns and rows from the 'customers' table WHERE the 'total_amt_spent' row is equal to or greater than (>=) $1000 OR the number of visits is equal to or greater than (>=) 3. Using the OR statement, the rows just needs to meet one of the criteria and it will be selected
LIKE OPERATOR
When working with text, the LIKE operator comes in handy. The LIKE operator makes use of the '%' symbol. The '%' symbol indicates that we may desire any amount of characters before or after a certain set of characters. Because lower and uppercase characters are not the same within the string, you must specify i.e letter 'T' differs from searching for the letter 't'. You can use single or double quotes The '%' symbol can come before or after the search word depending on what you're looking for.
SELECT name
FROM customers
WHERE name LIKE '%s';
This query returns all names that end in 's'. The '%' symbol indicates that any number of text can appear before the 's'. If the '%' symbol came after the 's', it would return all names that begin with 's' irrespective of the number of text that comes after it.
The '%' symbol can also be put at both ends if perhaps you're searching for all fields that contain some specific characters - WHERE name LIKE '%https%'; if we want to retrieve all web addresses that have 'https' in them.
The LIKE operator only works with text except when used in conjunction with CAST to perform operations on numeric data; this is beyond the scope of this article
IN OPERATOR
When working with numeric and text columns, the IN operator comes in handy. You can use a '=' operator when working with single values but when you want to check multiple values, it's advisable to use the IN operator. The IN operator simply says, if any value in the list is present in the rows, select the row. The OR operator can also be used for multiple values, but the IN operator is a cleaner method to construct these queries.
SELECT *
FROM orders
WHERE meal IN ('tasty shrimps', 'spicy snails', 'roasted beef');
This query selects all rows from the orders table where customers ordered any of the 3 meals. If you were to write an OR query, it would be
SELECT *
FROM orders
WHERE meal = 'tasty shrimps' OR meal = 'spicy snails' OR meal = 'roasted beef'
Now, we see why we use the IN operator
BETWEEN OPERATOR
The BETWEEN operator lets you specify a range of numeric values. It can be used to replace AND statements when dealing with the same column
SELECT *
FROM orders
WHERE total_amt_spent BETWEEN 1000 AND 2000;
If you were to use the AND operator, it will look like this
SELECT occurred_at, gloss_qty
FROM orders
WHERE gloss_qty >= 1000 AND gloss_qty <= 29;
In AND statements, the column name has to be specified for every criterion. In BETWEEN statements, the column name doesn't have to be specified more than once as stated in the example above. It's a simpler way of using the AND operator on a single column.
####NOT OPERATOR The NOT operator comes in handy when working with the two of the operators discussed above: IN and LIKE. We can get all of the rows that don't satisfy a specific condition by specifying NOT LIKE or NOT IN.
SELECT name
FROM customers
WHERE name NOT LIKE '%s';
This query returns every name that doesn't end in 's'
SELECT *
FROM orders
WHERE meal NOT IN ('tasty shrimps', 'spicy snails', 'roasted beef');
This query returns every row that doesn't contain any of these: 'tasty shrimps', 'spicy snails', or 'roasted beef'. The NOToperator is for exclusion
All these operators talked about can be used in tandem as the business case needs. We just need to get our creative juices flowing. We are SQL engineers lol.
We have come to the end of this article. I hope you gained as much as I did while writing it.
You can share this article if it has helped you or you know someone it may help. Thank you in advance.
Till we see again,
Koms