Learn SQLite in Neovim: Multi-Condition Filters with AND, OR, NOT | Episode 6

Learn SQLite in Neovim: Multi-Condition Filters with AND, OR, NOT

Filtering data with a single condition is often not enough to get meaningful results. This guide demonstrates how to combine multiple WHERE conditions in SQLite using AND, OR, NOT, and parentheses to create precise queries. Using a music library example, you'll learn to filter songs by multiple criteria like recent and short duration, specific artists, or excluding certain years.

Code

-- Create a table for songs
CREATE TABLE songs (
  id INTEGER PRIMARY KEY,
  artist TEXT,
  title TEXT,
  year INTEGER,
  duration INTEGER  -- duration in seconds
);

-- Insert sample data
INSERT INTO songs (artist, title, year, duration) VALUES
  ('Queen', 'Bohemian Rhapsody', 1975, 354),
  ('Eagles', 'Hotel California', 1976, 390),
  ('Nirvana', 'Smells Like Teen Spirit', 1991, 301),
  ('Adele', 'Hello', 2015, 295),
  ('Queen', 'Another One Bites the Dust', 1980, 215),
  ('Eagles', 'Take It Easy', 1972, 210),
  ('Coldplay', 'Yellow', 2000, 270),
  ('Imagine Dragons', 'Believer', 2017, 204);

-- Select recent (after 2000) AND short (less than 300 seconds) songs
SELECT * FROM songs
WHERE year > 2000 AND duration < 300;

-- Select songs by Queen OR Eagles
SELECT * FROM songs
WHERE artist = 'Queen' OR artist = 'Eagles';

-- Select songs NOT released after 2000
SELECT * FROM songs
WHERE NOT year > 2000;

-- Select songs by Queen OR Eagles AND short duration (less than 300 seconds)
-- Parentheses ensure OR is evaluated before AND
SELECT * FROM songs
WHERE (artist = 'Queen' OR artist = 'Eagles') AND duration < 300;

-- Select songs NOT (released between 1975 and 1990)
SELECT * FROM songs
WHERE NOT (year >= 1975 AND year <= 1990);

Key Points

• AND requires both conditions to be true, narrowing the result set.
• OR requires either condition to be true, broadening the result set.
• NOT negates a condition, returning rows that do not match it.
• Parentheses control the order of evaluation when combining AND and OR.
• Combining these operators allows precise, multi-condition filtering in SQL queries.