In previous lessons, we've discussed the basics of databases, SQL syntax, and how to retrieve data using SELECT statements. We also explored how to filter data using the WHERE clause. In this lesson, we'll dive deeper into filtering data by using logical operators such as AND, OR, IN, and BETWEEN. These operators help refine your data queries, allowing you to extract more specific results.
The AND operator allows you to combine multiple conditions in a query. All the conditions connected by AND must be true for a row to be included in the result set.
Let's say you want to find movies that have an IMDb rating greater than 7 and a runtime of fewer than 120 minutes. Here's how you'd write that query:
SQL1SELECT * FROM movie_details
2WHERE imdb_rating > 7 AND runtime_minutes < 120;SELECT *: This retrieves all columns from the table.FROM movie_details: This specifies the table we're querying.WHERE imdb_rating > 7 AND runtime_minutes < 120: This condition selects only those rows where theimdb_ratingis greater than 7 and theruntime_minutesare less than 120.
Plain text1 movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes 2----------+--------------------+------------------------+-------------+----------------- 3 12 | 130 | 519.3 | 7.3 | 117 4 14 | 165 | 677.7 | 7.5 | 115 5 20 | 162 | 622.7 | 7.1 | 118 6(3 rows)
In the resulting output, we can see that all entries have an imdb_rating greater than 7 and a runtime of less than 120 minutes.
The output contains the movie_id column from the movie_details table. The movie_id uniquely identifies each movie in the tables. To find the movie title of each row, we can find the corresponding movie_id column in the movies table. From the movies table, we know that the movie with ID 12 is "Ant-Man", movie 14 is "Doctor Strange", and movie 20 is "Ant-Man and The Wasp".
The OR operator also allows you to combine multiple conditions, but in this case, only one of the conditions needs to be true for a row to be included in the result set.
Let's say you want to select movies that have a budget greater than 220 million USD or box office sales greater than 2000 million USD. Here's how you'd write that query:
SQL1SELECT * FROM movie_details
2WHERE budget_million_usd > 220 OR box_office_million_usd > 2000;WHERE budget_million_usd > 220 OR box_office_million_usd > 2000: This condition selects rows where either thebudget_million_usdis greater than 220 or thebox_office_million_usdis greater than 2000.
The output is:
Plain text1 movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes 2----------+--------------------+------------------------+-------------+----------------- 3 11 | 250 | 1405.4 | 7.3 | 141 4 13 | 250 | 1153.3 | 7.8 | 147 5 19 | 321 | 2048.4 | 8.4 | 149 6 22 | 356 | 2797.8 | 8.4 | 181 7 27 | 260 | 1995.4 | 8.4 | 148 8 30 | 250 | 859 | 7.3 | 161 9 32 | 250 | 845 | 8.1 | 150 10 33 | 250 | 200 | 6.1 | 124 11(8 rows)
In the output, all rows have budget_million_usd greater than 220 OR a box_office_million_usd greater than 2000. The output also includes rows that meet both the conditions. For example, the movie with movie_id 11 meets both criteria.
The IN operator simplifies checking if a value matches any value in a list of values.
Let's say you want to find movies that belong to specific phases, for example, phases 1 and 5. Here's how you'd write that query:
SQL1SELECT * FROM movies
2WHERE phase IN (1, 5);WHERE phase IN (1, 5): This condition selects rows where thephaseis either 1 or 5.
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 31 | Ant-Man and The Wasp: Quantumania | 2023-02-17 | 5 10 32 | Guardians of the Galaxy Vol. 3 | 2023-05-05 | 5 11 33 | The Marvels | 2023-11-10 | 5 12(9 rows)
The output shows only the movies from phases 1 and 5.
The BETWEEN operator allows you to select values within a certain range. It's inclusive, meaning that it includes the start and end values.
Let's say you want to find movies with a release date within the year 2023. Here's how you'd write that query:
SQL1SELECT * FROM movies
2WHERE release_date BETWEEN '2023-01-01' AND '2023-12-31';WHERE release_date BETWEEN '2023-01-01' AND '2023-12-31': This condition selects rows where therelease_datefalls between January 1, 2023, and December 31, 2023, inclusive.
The output is:
Plain text1 movie_id | movie_name | release_date | phase 2----------+-----------------------------------+--------------+------- 3 31 | Ant-Man and The Wasp: Quantumania | 2023-02-17 | 5 4 32 | Guardians of the Galaxy Vol. 3 | 2023-05-05 | 5 5 33 | The Marvels | 2023-11-10 | 5 6(3 rows)
From the table, we can see these are the only 3 Marvel movies released in 2023.
In this lesson, we covered the use of logical operators such as AND, OR, IN, and BETWEEN in SQL queries. These operators allow you to filter data more precisely, making your queries more powerful and specific.
As you move to the practical exercises, you will be able to solidify your understanding by applying these operators in real-world scenarios. Keep practicing and explore the upcoming lessons to continue your journey in mastering SQL with PostgreSQL.
Happy querying!