Writing robust data pipelines with AILivestream July 22

START FREE
← Back to Glossary

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:

Copy code
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:

Copy code
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:

Copy code
# 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.

Related terms

FILTER clause →

The FILTER clause restricts which rows an aggregate function processes, letting you compute multiple conditional aggregates in a single GROUP BY query without CASE expressions.

relational API →

The relational API in DuckDB provides a fluent, Pythonic interface for constructing and executing SQL queries programmatically.

WHERE clause →

The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.

HAVING clause →

The HAVING clause filters grouped results after aggregation, letting you keep or discard groups based on conditions over aggregate values like SUM() or COUNT().

Bloom filter →

A Bloom filter is a space-efficient probabilistic data structure used to test whether a value might be in a set, with no false negatives but a small, tunable rate of false positives.

Predicate pushdown →

Predicate pushdown is a query optimization that moves filter conditions as close as possible to the data source, so fewer rows (or bytes) are read before filtering happens.

READ NEXT