Filtering and Sorting

Finding Exactly What You Need

Real-world queries rarely need all the data. You need to filter, sort, and limit results to find exactly what you're looking for.

Comparison Operators

  Operator   Meaning              Example
  ─────────────────────────────────────────────
  =          Equal                WHERE Age = 25
  <> or !=   Not equal            WHERE Age <> 25
  >          Greater than         WHERE Age > 25
  <          Less than            WHERE Age < 25
  >=         Greater or equal     WHERE Age >= 25
  <=         Less or equal        WHERE Age <= 25

Logical Operators

  -- AND: both conditions must be true
  SELECT * FROM Students WHERE Age > 20 AND DeptID = 10;

  -- OR: either condition can be true
  SELECT * FROM Students WHERE DeptID = 10 OR DeptID = 20;

  -- NOT: negates a condition
  SELECT * FROM Students WHERE NOT DeptID = 10;

  -- Combining operators (use parentheses!)
  SELECT * FROM Students
  WHERE (DeptID = 10 OR DeptID = 20)
  AND Age > 22;

BETWEEN, IN, LIKE

  -- BETWEEN: range of values (inclusive)
  SELECT * FROM Students WHERE Age BETWEEN 20 AND 25;
  -- equivalent to: Age >= 20 AND Age <= 25

  -- IN: match any value in a list
  SELECT * FROM Students WHERE DeptID IN (10, 20, 30);
  -- equivalent to: DeptID=10 OR DeptID=20 OR DeptID=30

  -- LIKE: pattern matching
  SELECT * FROM Students WHERE Name LIKE '%son';
  -- % matches zero or more characters
  -- _ matches exactly one character

ORDER BY and LIMIT

  -- Sort by one column
  SELECT * FROM Students ORDER BY Name;

  -- Sort descending
  SELECT * FROM Students ORDER BY Age DESC;

  -- Sort by multiple columns
  SELECT * FROM Students ORDER BY DeptID ASC, Name ASC;

  -- Limit results
  SELECT * FROM Students ORDER BY Age DESC LIMIT 5;
  -- Top 5 oldest students

  -- OFFSET (skip first 10, return next 5)
  SELECT * FROM Students ORDER BY ID LIMIT 5 OFFSET 10;