Filtering and Sorting

In this lesson, you'll learn how to narrow down your query results with WHERE clauses and organize them with ORDER BY. These are essential skills for finding and presenting data effectively.

The WHERE Clause

The WHERE clause filters rows based on conditions. Only rows that meet the condition are included in the results.

Basic Filtering

-- Create a customers table
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    city TEXT,
    country TEXT
);

-- Insert sample data
INSERT INTO customers (id, name, age, city, country)
VALUES
    (1, 'Alice Johnson', 28, 'New York', 'USA'),
    (2, 'Bob Smith', 35, 'London', 'UK'),
    (3, 'Charlie Davis', 22, 'New York', 'USA'),
    (4, 'Diana Wilson', 31, 'Paris', 'France'),
    (5, 'Eve Brown', 26, 'London', 'UK');

-- Find customers from New York
SELECT * FROM customers WHERE city = 'New York';

-- Find customers older than 30
SELECT * FROM customers WHERE age > 30;

Comparison Operators

SQL provides various operators for comparisons:

-- Create an inventory table
CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    price DECIMAL(10, 2),
    stock INTEGER
);

-- Insert products
INSERT INTO inventory (id, product, price, stock)
VALUES
    (1, 'Laptop', 999.99, 5),
    (2, 'Mouse', 24.99, 100),
    (3, 'Keyboard', 79.99, 50),
    (4, 'Monitor', 299.99, 12),
    (5, 'Headphones', 149.99, 0);

-- Equal to
SELECT * FROM inventory WHERE stock = 0;

-- Not equal to
SELECT * FROM inventory WHERE stock != 0;

-- Greater than
SELECT * FROM inventory WHERE price > 100;

-- Less than or equal to
SELECT * FROM inventory WHERE stock <= 10;

-- Between (inclusive range)
SELECT * FROM inventory WHERE price BETWEEN 50 AND 300;

Logical Operators

Combine multiple conditions with AND, OR, and NOT:

-- Create an employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT,
    salary INTEGER,
    years_experience INTEGER
);

-- Insert employees
INSERT INTO employees (id, name, department, salary, years_experience)
VALUES
    (1, 'John Doe', 'Engineering', 75000, 5),
    (2, 'Jane Smith', 'Marketing', 65000, 3),
    (3, 'Mike Johnson', 'Engineering', 85000, 8),
    (4, 'Sarah Williams', 'Sales', 70000, 4),
    (5, 'Tom Brown', 'Engineering', 60000, 2);

-- AND: Both conditions must be true
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 70000;

-- OR: At least one condition must be true
SELECT * FROM employees
WHERE department = 'Sales' OR years_experience > 5;

-- NOT: Negates a condition
SELECT * FROM employees
WHERE NOT department = 'Engineering';

-- Complex combinations using parentheses
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Sales')
  AND salary >= 70000;

Pattern Matching with LIKE

Search for patterns in text using wildcards:

-- Create a books table
CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    author TEXT,
    genre TEXT
);

-- Insert books
INSERT INTO books (id, title, author, genre)
VALUES
    (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction'),
    (2, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction'),
    (3, 'The Catcher in the Rye', 'J.D. Salinger', 'Fiction'),
    (4, 'Pride and Prejudice', 'Jane Austen', 'Romance'),
    (5, 'The Hobbit', 'J.R.R. Tolkien', 'Fantasy');

-- % matches any sequence of characters
SELECT * FROM books WHERE title LIKE 'The%';

-- Find books with "and" anywhere in the title
SELECT * FROM books WHERE title LIKE '%and%';

-- _ matches exactly one character
SELECT * FROM books WHERE author LIKE 'J._ _alinger';

NULL Values

Handle missing data with IS NULL and IS NOT NULL:

-- Create a tasks table
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    task TEXT NOT NULL,
    assigned_to TEXT,
    completed_date TEXT
);

-- Insert tasks (some without assignments or completion dates)
INSERT INTO tasks (id, task, assigned_to, completed_date)
VALUES
    (1, 'Write documentation', 'Alice', '2025-01-10'),
    (2, 'Fix bug #123', 'Bob', NULL),
    (3, 'Design new feature', NULL, NULL),
    (4, 'Review code', 'Charlie', '2025-01-12'),
    (5, 'Update database', NULL, NULL);

-- Find unassigned tasks
SELECT * FROM tasks WHERE assigned_to IS NULL;

-- Find completed tasks
SELECT * FROM tasks WHERE completed_date IS NOT NULL;

Sorting with ORDER BY

Sort your results in ascending or descending order:

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT,
    price DECIMAL(10, 2),
    rating DECIMAL(3, 2)
);

-- Insert products
INSERT INTO products (id, name, category, price, rating)
VALUES
    (1, 'Laptop Pro', 'Electronics', 1299.99, 4.5),
    (2, 'Wireless Mouse', 'Electronics', 29.99, 4.2),
    (3, 'Office Chair', 'Furniture', 199.99, 4.8),
    (4, 'Desk Lamp', 'Furniture', 39.99, 4.0),
    (5, 'USB Cable', 'Electronics', 9.99, 3.9);

-- Sort by price (ascending - lowest first)
SELECT * FROM products ORDER BY price ASC;

-- Sort by price (descending - highest first)
SELECT * FROM products ORDER BY price DESC;

-- Sort by multiple columns
SELECT * FROM products ORDER BY category ASC, price DESC;

-- Combine WHERE and ORDER BY
SELECT * FROM products
WHERE category = 'Electronics'
ORDER BY rating DESC;

Practice Exercises

Try these exercises using the tables above:

  1. Find all employees in Engineering with less than 5 years of experience
  2. Get products priced between $10 and $100, sorted by price
  3. Find books where the author's name starts with 'J' and the genre is Fiction
  4. List all tasks that are assigned but not completed, ordered by assigned person
  5. Find customers from USA or UK who are under 30 years old

Pro Tip: Always put WHERE before ORDER BY in your queries. The order is: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

Key Takeaways

  • WHERE filters rows based on conditions
  • Comparison operators: =, !=, >, <, >=, <=, BETWEEN
  • Logical operators: AND, OR, NOT
  • LIKE with % and _ enables pattern matching
  • IS NULL and IS NOT NULL handle missing data
  • ORDER BY sorts results (ASC ascending, DESC descending)
  • Combine WHERE and ORDER BY to filter and sort data

Next Steps

Now that you can filter and sort data, you're ready to learn how to modify data with INSERT, UPDATE, and DELETE statements!

Filtering and Sorting | LearningSQL.org