---
title: "filter"
description: "A filter operation selectively includes or excludes records from a dataset based on one or more conditions."
canonical: "https://motherduck.com/glossary/filter/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "Analyze JSON Data Using SQL and DuckDB"
    url: "https://motherduck.com/blog/analyze-json-data-using-sql/"
  - title: "Working with SQL with your favorite editor ? Check this 👉 | MotherDuck"
    url: "https://motherduck.com/videos/working-with-sql-with-your-favorite-editor-check-this/"
---

# filter

> A filter operation selectively includes or excludes records from a dataset based on one or more conditions.

## Definition
A `filter` operation selectively includes or excludes records from a dataset based on one or more conditions. In data pipelines and SQL queries, filtering helps narrow down large datasets to just the relevant information needed for analysis or further processing.

## SQL Implementation
In DuckDB SQL, filtering is primarily done using the `WHERE` clause in queries. The conditions in a `WHERE` clause can use comparison operators (like `=`, `<`, `>`), logical operators (`AND`, `OR`, `NOT`), and pattern matching.

Basic example:
```sql
SELECT * FROM customers 
WHERE age > 21 AND country = 'Canada';
```

DuckDB also supports filtering aggregations using the `FILTER` clause, which is more powerful than the standard SQL `HAVING` clause because it can be applied to individual aggregate functions:

```sql
SELECT 
  category,
  COUNT(*) AS total_items,
  COUNT(*) FILTER (WHERE price > 100) AS expensive_items
FROM products
GROUP BY category;
```

## Python Integration
When using DuckDB's Python API, filtering can be done through both SQL and the relational API. The relational API provides a `filter()` method that accepts SQL-style conditions:

```python
# Using DuckDB's relational API
duckdb.table('products').filter('price > 100').show()
```

## Key Differences
Unlike some databases that only support `HAVING` for filtering aggregated results, DuckDB's `FILTER` clause allows for more granular control over individual aggregations within the same query. This makes complex analytical queries more concise and easier to understand.