02.3 Filtering with LIKE, IN, BETWEEN and NULL

01 | FOUR COMMON QUESTIONS

Which names start with Robotics? Which clubs belong to one of two categories? Which fees fall within a range? Which rooms are still unassigned?

LIKE, IN, BETWEEN and IS NULL give these different questions clear forms. They extend WHERE rather than replace SELECT or FROM.

Use this section’s expanded Clubs table. It is a separate retrieval dataset , not a migration of the Section 01 database. All examples use SQLite. The queries below read data without changing stored records.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    room TEXT,
    fee_cents INTEGER NOT NULL,
    session_minutes INTEGER NOT NULL
);

INSERT INTO Clubs
    (club_id, club_name, category, capacity, room, fee_cents, session_minutes)
VALUES
    (1, 'Robotics', 'Technology', 12, 'AIR Lab', 1500, 90),
    (2, 'Game Design', 'Technology', 16, 'Room 12', 0, 60),
    (3, 'Art', 'Creative', 12, 'Studio', 800, 60),
    (4, 'Film', 'Creative', 20, NULL, 1000, 90),
    (5, 'Chess', 'Strategy', 16, 'Room 12', 0, 45),
    (6, 'Coding', 'Technology', 20, 'AIR Lab', 0, 60),
    (7, 'Drama', 'Creative', 24, NULL, 1200, 90),
    (8, 'Robotics Junior', 'Technology', 10, '', 500, 45);
"""

query = """
SELECT club_id, club_name, category, capacity, room, fee_cents, session_minutes
FROM Clubs
ORDER BY club_id;
"""

try:
    connection.executescript(setup_sql)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

02 | SHARED DATA AND MISSING VALUES

Money is stored as integer cents to avoid using binary floating point as the stored monetary amount. fee_cents = 0 means free, not unknown. Film and Drama have NULL rooms; Robotics Junior has an empty text room, deliberately included so you can compare the two situations.

03 | LIKE USES A PATTERN

SELECT club_name
FROM Clubs
WHERE club_name LIKE 'Robotics%'
ORDER BY club_id;

% matches zero or more characters. The pattern matches Robotics itself and Robotics Junior. A wildcard is part of a quoted pattern, not a column name.

'%Design' matches names ending in Design, and '%ob%' matches names containing ob. A leading wildcard can make ordinary index-based lookup less useful, so write patterns that fit your actual need.

04 | UNDERSCORE MATCHES ONE CHARACTER

SELECT club_name
FROM Clubs
WHERE club_name LIKE '_rt'
ORDER BY club_id;

_ matches exactly one character. Here it matches the A in Art. In contrast, 'A%' allows any length after A, including no additional characters.

Default SQLite LIKE is case-insensitive for ordinary ASCII letters, but does not provide complete Unicode case-folding by default. Other engines and configurations differ. Our examples use simple ASCII names and do not depend on accented-case behaviour.

SELECT club_name
FROM Clubs
WHERE club_name LIKE '%Robotics%'
ORDER BY club_id;

05 | IN CHECKS A LIST

SELECT club_name, category
FROM Clubs
WHERE category IN ('Creative', 'Strategy')
ORDER BY club_id;

IN tests membership of the listed values. It is a concise alternative to repeated equality comparisons connected by OR.

The list is enclosed in parentheses and values are separated by commas. Each text value needs its own quotes; 'Creative, Strategy' would be one different text value.

06 | BETWEEN INCLUDES BOTH ENDS

SELECT club_name, fee_cents
FROM Clubs
WHERE fee_cents BETWEEN 500 AND 1000
ORDER BY fee_cents ASC, club_id ASC;

BETWEEN 500 AND 1000 includes 500 and 1000. It expresses the same inclusive boundaries as fee_cents >= 500 AND fee_cents <= 1000.

Do not reverse the bounds and expect automatic correction. Use ordinary comparisons when one boundary must be exclusive.

07 | NULL NEEDS IS NULL

SELECT club_name, room
FROM Clubs
WHERE room IS NULL
ORDER BY club_id;

NULL means a missing or unknown value. Ordinary equality with NULL does not evaluate as true, so room = NULL does not find these records.

WHERE includes true conditions; false or unknown conditions do not qualify. Use IS NULL to test missingness directly and IS NOT NULL for the opposite.

08 | EMPTY TEXT IS STILL A VALUE

SELECT club_name, room
FROM Clubs
WHERE room IS NOT NULL
ORDER BY club_id;

This result includes Robotics Junior because its room is empty text, not NULL. “Not missing” and “usable room name” are not identical requirements.

SELECT club_name
FROM Clubs
WHERE room IS NULL OR trim(room) = ''
ORDER BY club_id;

The second query finds NULL or blank room values under this ordinary-space trimming rule. It finds Film, Drama and Robotics Junior.

09 | NOT IN AND NULL CAN SURPRISE YOU

SELECT club_name, room
FROM Clubs
WHERE room <> 'AIR Lab'
ORDER BY club_id;

The result excludes NULL rooms because the comparison is unknown, not true. If missing rooms should also qualify, add OR room IS NULL .

Similarly, NOT IN ('AIR Lab', NULL) does not simply mean “everything except AIR Lab”. NULL can make the membership comparison unknown. Keep NULL out of a simple exclusion list and handle missingness explicitly.

10 | COMBINE THE IDEAS

SELECT club_name, category, capacity
FROM Clubs
WHERE category IN ('Technology', 'Creative')
  AND capacity BETWEEN 10 AND 16
  AND club_name LIKE '%o%'
ORDER BY club_id;

Each condition is complete and AND requires all three. Read one candidate row through the conditions before testing your prediction.

SELECT club_name
FROM Clubs
WHERE category = 'Creative' AND room IS NULL
ORDER BY club_name, club_id;

11 | PATTERN CHALLENGE

SELECT club_name, fee_cents
FROM Clubs
WHERE category = 'Technology'
  AND fee_cents BETWEEN 0 AND 500
  AND club_name LIKE '%o%'
ORDER BY club_id;

Check the distinction between =, LIKE and IS NULL before moving on. These are different operations even when they all appear inside WHERE.

KEEP LEARNING

Previous page 02.4 DISTINCT and calculated columns

Reference: SQLite SELECT · SQLite expressions and filters