Description

Login
Back

Beginner · 12 min

Sets, ranges, and distinct values

Use IN, BETWEEN, and DISTINCT for common filters.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Filter a short list

IN checks membership in a list. BETWEEN includes both endpoints, which is useful for numeric ranges. For date-time ranges, an exclusive upper bound is often clearer than assuming the end of a day.

SELECT id FROM sessions WHERE topic IN ('Food', 'Music') AND minutes BETWEEN 10 AND 25 ORDER BY id;

Output

id
2
4
5

Remove duplicate result rows

DISTINCT removes duplicate combinations of the selected columns. It does not mean one row per entity unless those columns identify that entity. Here it returns each topic once.

SELECT DISTINCT topic FROM sessions ORDER BY topic;

Output

topic
Food
Music
Travel

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return each session topic once, sorted alphabetically.

Show solution
SELECT DISTINCT topic FROM sessions ORDER BY topic;

DISTINCT applies to the projected topic column, leaving one row for each different topic.

Practice