WHERE Clause

Learning Focus

Use this lesson to master WHERE Clause filtering in DuckDB for precise data retrieval.

Concept Overview

The WHERE clause filters rows before aggregation or output. Only rows matching the condition are included.

Basic Syntax

SELECT columns
FROM table
WHERE condition;

Examples

Example 1: Equality Filter

SELECT * FROM employees WHERE department = 'Engineering';

Example 2: Comparison Operators

SELECT name, salary FROM employees WHERE salary > 80000;
SELECT name, salary FROM employees WHERE salary BETWEEN 70000 AND 90000;

Example 3: Multiple Conditions

SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 85000;

SELECT * FROM employees
WHERE department = 'HR' OR active = false;

Example 4: Pattern Matching

SELECT * FROM employees WHERE name LIKE 'A%';       -- starts with A
SELECT * FROM employees WHERE name ILIKE '%smith%'; -- case-insensitive

Example 5: NULL Filtering

SELECT * FROM employees WHERE active IS NOT NULL;
SELECT * FROM employees WHERE department IS NULL;

Example 6: IN Clause

SELECT * FROM employees
WHERE department IN ('Engineering', 'Finance');

Example 7: Date Filtering

SELECT * FROM employees
WHERE hire_date >= DATE '2020-01-01'
  AND hire_date <  DATE '2023-01-01';

Example 8: Filter on File Data

SELECT name, salary
FROM read_csv_auto('employees.csv')
WHERE salary > 80000;

Common Pitfalls

Pitfall	Consequence	Prevention
Comparing to NULL with =	Always false — never matches	Use IS NULL or IS NOT NULL
WHERE after GROUP BY	Syntax/logic error	Use HAVING to filter after grouping
Case-sensitive LIKE	Missing matches	Use ILIKE for case-insensitive matching

Quick Reference

WHERE col = 'value'
WHERE col > 100
WHERE col BETWEEN 10 AND 100
WHERE col IN ('a','b','c')
WHERE col LIKE 'prefix%'
WHERE col ILIKE '%pattern%'
WHERE col IS NULL
WHERE col IS NOT NULL
WHERE cond1 AND cond2
WHERE cond1 OR cond2

What's Next

• Next: ORDER BY - Sort your query results.
• Module Overview - Return to this module index.