If you’re still struggling to see the big picture – don’t worry. Each section is an accumulation of the last so hopefully you’ll start to catch on! If not, refer to previous sections and try to practice writing more SQL queries on your own. It’ll all start to come together!
Back to business – what does the WHERE clause do? It serves not just one, but TWO functions:
- To filter data in a table
- To join two tables together – Explained in a further lesson
Filtering Using a SQL WHERE Clause
In the previous section, we covered how to query the “Customer” table to return all data back from a table. In this example, let’s use the Employee table.
SELECT * FROM EMPLOYEE;
In the above output – we can see there is NOT any filtering because we haven’t specified a WHERE clause. What if we only want to see a small set of employee record instead of all the records?
Yes, this table is small and we could probably eye out one or two employee records, but try to imagine that it’s thousands or even millions of records instead. That’s the normal use case. We’re not going to try to “eye out” the records we want because that would take too long.
As an example, let’s create a scenario where we need to get a list of our IT staff as per a request from Human Resources. We can write a SQL statement with a WHERE clause filter to do that.
It looks like we have two employees that are labeled as “IT Staff”. Let’s get the basic employee information so we can send this out to HR in case they have any computer issues that need to be resolved. That might include the following selected fields:
Great! Now we have the info we can send out!
Filtering Multiple Values Using ‘AND’
At this point, we can see how to filter values based on specific columns, but what if we wanted to filter based on multiple columns and not just individual ones? This is where the AND operator comes in.
Maybe we want to ask the database for all of the sales agents that live in the city of Calgary. We could write our query like this:
As we can see, there are a few employees that seem to fit that criteria. The WHERE clause is a great way to filter the data down. To filter the data further you can also use as many AND operators as needed.
Just remember that using a SQL WHERE clause in a SQL query will limit the data you will have returned back. It’s used as a way to filter or limit the results.