In SQL, the WHERE keyword allows you to filter based on both text and numeric values in a table. There are a few different comparison operators you can use:
=equal
<>not equal
>greater than
<=less than or equal to
>=greater than or equal to
For example, you can filter text records such astitle. The following code returns all films with the title'Metropolis':
SELECT title
FROM films
WHERE title = 'Metropolis';
Notice that the WHERE clause always comes after the FROM statement!
Note that in this course we will use<>and not!=for the not equal operator, as per the SQL standard.
Remember, the WHERE clause can also be used to filter text results, such as names or countries.
For example, this query gets the titles of all films which were filmed in China:
SELECT title
FROM films
WHERE country = 'China';
Often, you'll want to select data based on multiple conditions. You can build up your WHERE queries by combining multiple conditions with the AND keyword.
For example,
SELECT title
FROM films
WHERE release_year > 1994
AND release_year < 2000;
What if you want to select rows based on multiple conditions where some but not all of the conditions need to be met? For this, SQL has the OR operator.
For example, the following returns all films released in either 1994 or 2000:
SELECT title
FROM films
WHERE release_year = 1994
OR release_year = 2000;
Checking for ranges like this is very common, so in SQL the BETWEEN keyword provides a useful shorthand for filtering values within a specified range. This query is equivalent to the one above:
SELECT title
FROM films
WHERE release_year
BETWEEN 1994 AND 2000;
Enter the IN operator! The IN operator allows you to specify multiple values in a WHERE clause, making it easier and quicker to specify multiple OR conditions! Neat, right?
So, the above example would become simply:
SELECT name
FROM kids
WHERE age IN (2, 4, 6, 8, 10);
In SQL, NULL represents a missing or unknown value. You can check for NULL values using the expression IS NULL. For example, to count the number of missing birth dates in the people table:
SELECT COUNT(*)
FROM people
WHERE birthdate IS NULL;
As you've seen, the WHERE clause can be used to filter text data. However, so far you've only been able to filter by specifying the exact text you're interested in. In the real world, often you'll want to search for apatternrather than a specific text string.
In SQL, the LIKE operator can be used in a WHERE clause to search for a pattern in a column. To accomplish this, you use something called a wildcard as a placeholder for some other values. There are two wildcards you can use with LIKE:
The % wildcard will match zero, one, or many characters in text. For example, the following query matches companies like'Data','DataC''DataCamp','DataMind', and so on:
SELECT name
FROM companies
WHERE name LIKE 'Data%';
The_wildcard will match asinglecharacter. For example, the following query matches companies like'DataCamp','DataComp', and so on:
SELECT name
FROM companies
WHERE name LIKE 'DataC_mp';
You can also use theNOT LIKEoperator to find records thatdon'tmatch the pattern you specify.
