SQL and MySQL course Β· Module 3: Filtering (WHERE)
The Scriptorium - the art of filtering
In this lesson3
Welcome back! You are entering the Scriptorium - the hall where scribes no longer copy whole shelves, but pick out only the scrolls they truly need. Here you will learn the librarian's most important skill: filtering data.
Why filter?
In the previous location you learned SELECT, which returns every row from a shelf:
SELECT * FROM ksiazki;But the ksiazki shelf may hold thousands of scrolls! We rarely need them all. More often we look for specific ones: only books more expensive than 30 coins, only authors from Greece, only loans that have not yet been returned.
The WHERE clause
To pick out specific rows we use the WHERE clause (meaning "where", "on condition that"). We always write it after the FROM part:
SELECT * FROM ksiazki
WHERE dostepna = 1;This query means: "Show all columns of the books for which the dostepna column has the value 1". The Library will walk through every scroll and return only those that satisfy the condition.
How does it work?
Imagine a scribe who reads each scroll in turn and asks one question: "Does this scroll satisfy the condition?". If yes - it goes on the result pile. If no - it is put back on the shelf. In the end you receive only the pile of matching scrolls.
In this location you will meet all the tools we use to build conditions: comparison operators, the connectors AND/OR/NOT, lists IN, ranges BETWEEN, patterns LIKE and the mysterious emptiness NULL. Let's begin!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What is the WHERE clause used for in a SELECT query?
2. After which part of the query do we write the WHERE clause?
Hands-on tasks in the game
- Vertical ordering
Arrange the lines of a simple filtering query in order (from first to last):