This is the second post in our series featuring SQL tutorials for those getting started. Check out our last post on how to join tables and come back for more Simple SQL posts. Show
The WHERE ClauseIn SQL, the SELECT statement is used to return specific columns of data from a table. Similarly, the WHERE clause is used to choose the rows of a table, and the query will return only the rows that meet the given criteria.
In this blog post I’ll be describing some common comparison operators and how to use them with the WHERE clause. I’ll be working with a demo database called Cities, that contains city names and population based on the 2010 census. Comparison OperatorsThese comparison operators are used with WHERE to determine equality or difference between variables or values. You may or may not be familiar with these common comparison operators:
Let’s see it in action. To demonstrate, we will ask a question: What are the cities that have a population greater than 700,000 and less than 3,000,000? First consider what the query looks like without using WHERE, and the outcome.
By selecting for the city and population column, the query returns all rows for the database. To answer the question, we want to know which rows in the population column have a value of greater than 700,000 and a value of less than 3,000,000. In this example we also have two comparisons, so we need to define whether we want the query to return only the results when one is true or when both are true. Since we want to know the result when both are true, we use AND.
And our result is three cities - San Francisco, Houston and Chicago.
If instead our question was: What is the population of either San Francisco or Houston? - our query would use OR because either comparison can be true.
If we had used AND in this example then we would have no result, because there are no cities that are named both San Franicsco and Houston. Using the IN( ) OperatorThe IN() operator is useful when you want to show the values that are inside of a list. Let’s ask the question: What is the population of San Francisco, Portland, and Seattle? In this scenario we are asking for the rows in the population column where our cities can be San Francisco, Portland or Seattle. Rather than writing a query for each of these cities, or writing a long WHERE clause, we can put the cities in a list.
And as you can see, the query returns only the rows for which we specified in the list.
Using the LIKE OperatorThe LIKE operator (or NOT LIKE) is used to perform pattern matching, combined with wildcards. If you are looking for a value that starts, ends or contains a certain search term, you can use LIKE to return all possible matches.
Let’s see this in action. For example, what is the population of the cities located in California? In this question, we want to pull out any rows from the cities column that end with “CA”. So our search term, “CA” will begin with the wildcard “%”. What SQL clause in a SELECT statement is used to filter data?The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.
Which keyword is used to filter the records in SQL?What if you have lots of values that you are interested in? There are many occasions where you might like to filter data based on a list of values. That is where the SQL keyword IN comes into play. It works the same way as if you used the equals sign in the WHERE statement with multiple OR conditions.
Which element of the SELECT statement is used to filter the data?The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause.
Which clause in a SELECT statement allows you to filter the results?With the WHERE clause, we can filter the result set of the select statement. Filtering patterns are used after the WHERE clause.
|