Continuing from my 4/10 post on the SQL Select statement, probably the most powerful tool in your arsenal for filtering down results is the WHERE statement, which allows you to apply conditions to your query.
These are the operators you can use with WHERE:
| Operator |
Description |
| = |
Equal to |
| != |
Not equal to |
| < |
Less than |
| <= |
Less than or equal to |
| > |
Greater than |
| >= |
Greater than or equal to |
| BETWEEN x AND y |
Between two values |
| IS NULL |
Has no value |
| LIKE |
Match With Wildcard |
| REGEX |
Match With Regular Expression |
Using our previous example of the states table, you could structure a WHERE statement that returned a specific state:
SELECT *
FROM states
WHERE name = ‘texas’;
Would return the row for Texas. You could also use the WHERE command to return all states that have a population of at least 10,000,000, but limited to 5 results and displayed in order of highest population to lowest:
SELECT *
FROM states
WHERE population > 10000000
LIMIT 5
ORDER BY population DESC;
As you can see in the above two examples, you need to use quotes when matching a string but not when matching a numerical value. Usually matching in SQL is not case sensitive, but YMMV based on your database product and configuration.
The BETWEEN operator requires two input variables in order to be successful. Let’s take the last example but assume that instead of wanting to see states with a population greater than 10,000,000, I want to see states with a population between 5,000,000 and 10,000,000:
SELECT *
FROM states
WHERE population BETWEEN 5000000 AND 10000000;
Note that the two input variables have to be separated by the AND keyword.
You can chain multiple WHERE checks together with AND or OR. You can force an order of operations with parentheses but by default AND’s are evaluated before OR’s.
Some examples:
SELECT *
FROM states
WHERE population > 10000000 AND size > 200000;
SELECT *
FROM states
WHERE (population > 10000000 AND size > 200000) OR name = ‘delaware’;
I’ll admit that last one doesn’t seem to be the most useful query but hopefully it gets the point across anyway.
Another useful command is the LIKE command, which allows you to query information with wildcards.
There are two main wildcards that are used for LIKE:
| Wildcard |
Description |
| % |
Matches Any Length of Characters |
| _ |
Matches a Single Character |
Wildcards can be put before, inside or after a search string.
Here’s an example:
SELECT *
FROM states
WHERE name LIKE ‘a%’;
Would return every state that starts with an A, so:
Alaska
Arizona
Arkansas
Alabama
Running the same query with a _ in place of the % would return 0 rows because there is no 2 character state that begins with A.
If you don’t have to use a wildcard, don’t use it. Queries with wildcards (especially with ones at the beginning of the search string) take a long time to run and can slow down your database if relied to heavily upon.
And last but not least, REGEX is the deep end equivalent of LIKE. Everything that you can do with LIKE, you can also do with REGEX. I’m not going to go too far into it because regular expressions are a topic upon themselves, but it’s used in the same way the rest of the commands are.
SELECT *
FROM states
WHERE name REGEX ‘^[aA]+[a-zA-Z]*$’;
Would do the same thing as the a% LIKE example above, just requires a lot more brain cells to get off the ground.
EDIT: I was thinking about it and if you want to get nitpicky, the regex doesn’t do *exactly* the same thing. After the initial A/a, it matches any letter (but not non-letter characters) where the LIKE a% would match any valid character.
There’s more to say on this, I may make a 3rd post.
