More Operators

Here are a few more keyword operators you may need.

BETWEEN

a BETWEEN b AND c is a shortcut for a >= b AND a <= c

SELECT *
FROM daters
WHERE height_cm BETWEEN 160 AND 178;

NOT

You can use NOT to take the opposite of any condition.

SELECT *
FROM daters
WHERE height_cm NOT BETWEEN 160 AND 178;

Note that NOT a = b is the same as a != b and NOT a > b is the same as a <= b.

IN

Previously, we saw a clause like WHERE eye_color = 'blue' OR eye_color = 'green'. This wasn't too hard to write with only 2 values, but things get unpleasant with many more. Instead, you can use the IN (…, …) operator to check if something is equal to one of many choices. It looks like this:

SELECT *
FROM daters
WHERE eye_color IN ('blue', 'green');

IN checks if the value on the left-hand side equals any of the values on the right-hand side. The right-hand side is a parenthesized, comma-separated expression list.

LIKE

We have seen some basic text comparisons. For example, you can get names that start with a "c" by doing WHERE name >= 'c' AND name < 'd'. However, that isn't very readable, and it doesn't do anything more advanced. The LIKE operator lets you match wildcards. It takes the form of LIKE followed by a pattern, for example:

SELECT *
FROM daters
WHERE name LIKE 'F%';

The % character is a wildcard which represents any number of any letter. The above pattern matches an "F" in the beginning, and then any letters after that. You can also put the wildcard at the beginning to get names ending with a specific letter.

SELECT *
FROM daters
WHERE name LIKE '%e';