Welcome back! So far, you've learned about the basics of databases and SQL, and how to use the SELECT statement to retrieve data. In this lesson, we'll take a look at the WHERE clause, which is essential for filtering data in SQL queries.
The WHERE clause allows you to specify conditions that must be met for the rows to be included in the result set. This is crucial when you want to focus on specific data points within a larger dataset. By the end of this lesson, you'll be able to filter data using various conditions and operators effectively.
Let's begin by looking at the basic syntax for using the WHERE clause in a SQL query:
SQL1SELECT column1, column2, ... 2FROM table_name 3WHERE condition;
Here, condition is a logical expression that returns TRUE or FALSE. Only rows for which this expression is TRUE will be included in the result set. The condition often involves comparison operators such as:
=: Equal to a value.>: Greater than a value.<: Less than a value.>=: Greater than or equal to a value.<=: Less than or equal to a value.<>: Not equal to a value.
With this syntax in mind, let's move on to a practical example.
Suppose we want to only select rows where the movie is in phase 1. To do this, we use the = operator. For example:
SQL1SELECT * FROM movies
2WHERE phase = 1;This will output:
Plain text1 movie_id | movie_name | release_date | phase 2----------+------------------------------------+--------------+------- 3 1 | Iron Man | 2008-05-02 | 1 4 2 | The Incredible Hulk | 2008-06-13 | 1 5 3 | Iron Man 2 | 2010-05-07 | 1 6 4 | Thor | 2011-05-06 | 1 7 5 | Captain America: The First Avenger | 2011-07-22 | 1 8 6 | The Avengers | 2012-05-04 | 1 9(6 rows)
The WHERE clause paired with the = operator limits the movies listed to only those in phase 1.
Let's say we only want to select movies that were released before 2014. To do this we use the < operator. To compare dates, use the format YYYY-MM-DD.
SQL1SELECT * FROM movies
2WHERE release_date < '2015-01-01';The WHERE clause filters the results to include only rows where the release_date column has a value earlier than January 1st, 2015.
When you execute this query, you'll get the following result:
Plain text1 movie_id | movie_name | release_date | phase 2----------+------------------------------------+--------------+------- 3 1 | Iron Man | 2008-05-02 | 1 4 2 | The Incredible Hulk | 2008-06-13 | 1 5 3 | Iron Man 2 | 2010-05-07 | 1 6 4 | Thor | 2011-05-06 | 1 7 5 | Captain America: The First Avenger | 2011-07-22 | 1 8 6 | The Avengers | 2012-05-04 | 1 9 7 | Iron Man 3 | 2013-05-03 | 2 10 8 | Thor: The Dark World | 2013-11-08 | 2 11 9 | Captain America: The Winter Soldier| 2014-04-04 | 2 12 10 | Guardians of the Galaxy | 2014-08-01 | 2 13(10 rows)
Now let's write a query to find movies that are not in phase 3 using the <> operator.
SQL1SELECT * FROM movies
2WHERE phase <> 3;WHERE phase <> 3 selects only the entries where the value in the phase column does not equal 3.
This will output:
Plain text1 movie_id | movie_name | release_date | phase 2----------+---------------------------------------------+--------------+------- 3 1 | Iron Man | 2008-05-02 | 1 4 2 | The Incredible Hulk | 2008-06-13 | 1 5 3 | Iron Man 2 | 2010-05-07 | 1 6 4 | Thor | 2011-05-06 | 1 7 5 | Captain America: The First Avenger | 2011-07-22 | 1 8 6 | The Avengers | 2012-05-04 | 1 9 7 | Iron Man 3 | 2013-05-03 | 2 10 8 | Thor: The Dark World | 2013-11-08 | 2 11 9 | Captain America: The Winter Soldier | 2014-04-04 | 2 12 10 | Guardians of the Galaxy | 2014-08-01 | 2 13 11 | Avengers: Age of Ultron | 2015-05-01 | 2 14 12 | Ant-Man | 2015-07-17 | 2 15 24 | Black Widow | 2021-07-09 | 4 16 25 | Shang-Chi and the Legend of the Ten Rings | 2021-09-03 | 4 17 26 | Eternals | 2021-11-05 | 4 18 27 | Spider-Man: No Way Home | 2021-12-17 | 4 19 28 | Doctor Strange in the Multiverse of Madness | 2022-05-06 | 4 20 29 | Thor: Love and Thunder | 2022-07-08 | 4 21 30 | Black Panther: Wakanda Forever | 2022-11-11 | 4 22 31 | Ant-Man and The Wasp: Quantumania | 2023-02-17 | 5 23 32 | Guardians of the Galaxy Vol. 3 | 2023-05-05 | 5 24 33 | The Marvels | 2023-11-10 | 5 25(22 rows)
In this lesson, we explored the WHERE clause's importance and how it helps in filtering data in SQL queries. We covered:
- The syntax and basic structure of the
WHEREclause. - Various comparison operators for setting conditions.
- Filtering entries by date
- Practical examples of how to filter data using the
WHEREclause.
Understanding and using the WHERE clause will significantly enhance your ability to extract meaningful insights from your data. Be sure to practice the concept through the exercises that follow this lesson. Great job, and get ready for more advanced querying techniques in future lessons!