Filtering by Comparing Values3:58 with Andrew Chalkley
You don't need to just search for equal or unequal values. You can compare them to a value you decide.
There are several relational operators you can use:
<=less than or equal to
>=greater than or equal to
These are primarily used to compare numeric and date/time types.
SELECT <columns> FROM <table> WHERE <column name> < <value>; SELECT <columns> FROM <table> WHERE <column name> <= <value>; SELECT <columns> FROM <table> WHERE <column name> > <value>; SELECT <columns> FROM <table> WHERE <column name> >= <value>;
SELECT first_name, last_name FROM users WHERE date_of_birth < '1998-12-01'; SELECT title AS "Book Title", author AS Author FROM books WHERE year_released <= 2015; SELECT name, description FROM products WHERE price > 9.99; SELECT title FROM movies WHERE release_year >= 2000;
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
SQL isn't only used for finding or filtering on exact matches, 0:00 it can be used to compare values using relational operators. 0:05 The name of relational operators comes from their 0:09 usage in comparing the relationship between two values, 0:12 not because they're used in a relational database. 0:16 These operators are actually found in many of the programming languages, 0:19 not just SQL. 0:23 So let's take a look at the operators we already know. 0:24 The equality or equal to operator compares the quality of two values. 0:28 The text of Andrew is the same as the text of Andrew. 0:33 So this condition is true. 0:37 The inequality operator, or the not equal to operator, 0:39 compares the values, and if they don't match, the condition is true. 0:43 The text of Andrew is not the same as the text of Lauren, so this is true. 0:48 Now onto the relational operators. 0:54 You tend to see these operators when comparing numbers and dates. 0:56 We're going to focus on numbers first. 1:00 The first one we're going to look at is the less than operator, 1:02 which is represented by a left angle bracket. 1:06 All operators read from left to right. 1:09 If the value on the left-hand side of the operator is smaller than the value on 1:12 the right side of the operator, the condition will evaluate to true. 1:16 If the two values are equal to each other, the condition will evaluate to false, 1:21 because the left value is not smaller than the right value. 1:26 In this example, all values less than 40, but not including 40, are true. 1:30 If you wanted to compare values less than or equal to each other, 1:36 you'd use the less than operator, followed by the equals sign. 1:40 This is known as the less than or equal to operator. 1:44 This is just like the less than operator, but 1:48 it is inclusive of the value on the right hand side. 1:50 How about creating values greater than a particular value? 1:54 Well, we use the right angle bracket or the greater than symbol. 1:58 100 is greater than 40 and 99, but is not greater than 100. 2:02 However, 100 is greater than or equal to 100. 2:09 Let's use these new operators to answer some more questions from our database. 2:13 Let's start with the question what books are in the library 2:18 that were first published after 2005? 2:22 Let's start with our general SQL statement. 2:26 Select star from books. 2:28 Then let's add a where clause, 2:34 first published is greater than 2005. 2:38 Notice how Harry Potter and the Half Blood Prince wasn't selected, 2:45 since it's first published year was in 2005. 2:52 2005 isn't greater than itself, it is equal to itself. 2:55 If we wanted to include books from 2005 too, we should use the greater than or 3:01 equal to operator to get all books from 2005 onwards. 3:06 Finally, let's find some classics in our library. 3:11 Let's find all books released before the 20th century. 3:14 How would you change this query? 3:18 That's right. 3:21 We would change the greater than or equal to operator to be the less than 3:23 operator and the value 1,900. 3:29 Nice. 3:35 Obviously, 1900 is the beginning of the 20th Century. 3:37 But if we wanted to include 1900, we'd use the less than or equal to operator. 3:40 Our results are the same since we have no books in our library from 1900. 3:47 With these comparison operators under your belt, 3:50 you can now filter your data in more exciting ways. 3:54
You need to sign up for Treehouse in order to download course files.Sign up