What you will be able to do
After this lesson you can write three kinds of filter that come up constantly: "is one of these values", "is within this range" and "looks like this text". Each one replaces a longer or clumsier condition.
IN: a list of values
You can write several equalities on one column with OR, but IN is shorter and easier to read:
SELECT first_name, department_id
FROM employees
WHERE department_id IN (3, 4, 5);+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Dev | 3 |
| Anika | 3 |
| Yash | 3 |
| Lata | 4 |
| Omar | 4 |
| Pooja | 5 |
| Karan | 5 |
| Sana | 5 |
+------------+---------------+This returns the same rows as department_id = 3 OR department_id = 4 OR department_id = 5. The list can hold text values as well, in single quotes, such as job_title IN ('Designer', 'Accountant'). NOT IN gives the opposite:
SELECT first_name, department_id
FROM employees
WHERE department_id NOT IN (1, 2);+------------+---------------+
| first_name | department_id |
+------------+---------------+
| Dev | 3 |
| Anika | 3 |
| Yash | 3 |
| Lata | 4 |
| Omar | 4 |
| Pooja | 5 |
| Karan | 5 |
| Sana | 5 |
+------------+---------------+Eight rows come back. Ravi Pillai, who has no department, is not among them, because a missing value is never "not in" a list in the SQL sense. See the NULL lesson. IN can also take a subquery instead of a typed list, which you will meet later.
The NOT IN trap
A single NULL inside the list makes NOT IN return nothing at all. This is one of the best known SQL surprises:
SELECT first_name, department_id
FROM employees
WHERE department_id NOT IN (1, 2, NULL);(no rows returned)Why? NOT IN (1, 2, NULL) expands to department_id <> 1 AND department_id <> 2 AND department_id <> NULL. The last test is unknown for every row, so the whole condition can never be true. You rarely type a NULL into a list yourself. The danger is a list produced by a subquery that happens to contain one, so remember this when you get to that lesson.
BETWEEN: a range
BETWEEN low AND high keeps values in a range, and both ends count:
SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 80000 AND 100000;+------------+--------+
| first_name | salary |
+------------+--------+
| Rohan | 82000 |
| Neha | 90000 |
| Sameer | 90000 |
| Pooja | 85000 |
| Ravi | 95000 |
+------------+--------+It is the same as salary >= 80000 AND salary <= 100000. Always put the smaller value first. BETWEEN 100000 AND 80000 is valid, but matches nothing in most databases. NOT BETWEEN selects the outside of the range.
BETWEEN is natural for dates. Use it for a whole calendar year like this:
SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';+------------+------------+
| first_name | hire_date |
+------------+------------+
| Kavya | 2020-01-20 |
| Sameer | 2020-08-24 |
| Omar | 2020-11-09 |
+------------+------------+Take care when a column holds both a date and a time, such as a timestamp. A value like 2020-12-31 15:30:00 is greater than '2020-12-31', so it falls outside the range. For timestamps, a safer pattern is >= '2020-01-01' AND < '2021-01-01'. The hire_date column here holds dates only, so BETWEEN is safe.
LIKE: text patterns
LIKE compares text to a pattern with two wildcard characters:
| Wildcard | Matches |
|---|---|
% |
any number of characters, including none |
_ |
exactly one character |
Names that start with A:
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'A%';+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Arjun | Rao |
| Anika | Shah |
| Aman | Gupta |
+------------+-----------+Names that end with a:
SELECT first_name
FROM employees
WHERE first_name LIKE '%a';+------------+
| first_name |
+------------+
| Meera |
| Kavya |
| Isha |
| Neha |
| Tara |
| Anika |
| Lata |
| Pooja |
| Sana |
| Diya |
+------------+A pattern with _ fixes the length. Four underscores match names of exactly four letters:
SELECT first_name
FROM employees
WHERE first_name LIKE '____';+------------+
| first_name |
+------------+
| Isha |
| Neha |
| Tara |
| Yash |
| Lata |
| Omar |
| Sana |
| Ravi |
| Diya |
| Aman |
+------------+You can combine wildcards. This looks for job titles that contain the word Support anywhere:
SELECT DISTINCT job_title
FROM employees
WHERE job_title LIKE '%Support%';+------------------+
| job_title |
+------------------+
| Support Lead |
| Support Engineer |
+------------------+NOT LIKE is the opposite. A search like '%word%' with a leading % cannot use a normal index in most databases, so it can be slow on very big tables. A search anchored at the start, like 'A%', can often use an index.
Matching a real % or _
To search for a literal percent sign or underscore, choose an escape character and name it with ESCAPE. Here ! is used, which avoids backslash trouble because MySQL treats the backslash in text specially:
SELECT name FROM projects WHERE name LIKE '%!_%' ESCAPE '!';The sample projects have no underscore in any name, so this would return no rows. The !_ in the pattern means a literal underscore instead of "any one character".
Case sensitivity
Does LIKE 'a%' match Arjun? It depends on the database:
SELECT first_name FROM employees WHERE first_name LIKE 'a%';+------------+
| first_name |
+------------+
| Arjun |
| Anika |
| Aman |
+------------+On SQLite this finds the three names that start with a capital A, because SQLite ignores case in LIKE for ordinary English letters. MySQL and SQL Server do the same with their usual settings. PostgreSQL's LIKE is case sensitive, so the same query would return nothing there. PostgreSQL has ILIKE for case-insensitive matching, but it is not standard SQL. If you need identical behaviour everywhere, convert both sides with LOWER().
Differences between databases
LIKEcase sensitivity differs as above.ILIKEexists on PostgreSQL only.- Extra pattern syntax. SQL Server's
LIKEalso supports character ranges in square brackets, such as[A-C]%. That is not standard and does not work elsewhere. MySQL has a separateREGEXP, which the MySQL LIKE and REGEXP lesson covers. IN,BETWEENand the basic%and_wildcards work on all four databases.
Common mistakes
- A
NULLin aNOT INlist. The result is empty. - Reversing
BETWEENbounds, or forgetting that the ends are included. - Using
LIKEwithout a wildcard, as inLIKE 'Arjun'. It works, but it only says=in a more confusing way. - Using
BETWEENon timestamps and losing the last day. - Putting
%at the start of every search. It is slow on large tables, so use it knowingly.
Exercise
Show the first name and job title of employees whose first name has exactly five letters and whose salary is between 60000 and 90000 inclusive.
Show one possible answer
SELECT first_name, job_title, salary
FROM employees
WHERE first_name LIKE '_____'
AND salary BETWEEN 60000 AND 90000;+------------+--------------------+--------+
| first_name | job_title | salary |
+------------+--------------------+--------+
| Rohan | Developer | 82000 |
| Anika | Content Strategist | 68000 |
| Pooja | Support Lead | 85000 |
+------------+--------------------+--------+Key points
- IN (a, b, c) is a shorter way to write several OR conditions on one column.
- BETWEEN low AND high includes both end values, and works for numbers, dates and text.
- LIKE matches text patterns. % stands for any number of characters, and _ for exactly one.
- Whether LIKE ignores letter case depends on the database, so test it.
- NOT IN returns nothing if the list contains a NULL. Remove NULLs from the list first.