Client APIs
Operations
There are a number of operations that can be performed on relations. These are all short-hand for running the SQL queries – and will return relations again themselves.  
#### `aggregate(expr, groups = {})` {#docs:api:python:relational_api::aggregateexpr-groups--}  
Apply an (optionally grouped) aggregate over the relation. The system will automatically group by any columns that are not aggregates.  
```python
import duckdb

rel = duckdb.sql("SELECT * FROM range(1_000_000) tbl(id)")
rel.aggregate("id % 2 AS g, sum(id), min(id), max(id)")
```  
```text
┌───────┬──────────────┬─────────┬─────────┐
│   g   │   sum(id)    │ min(id) │ max(id) │
│ int64 │    int128    │  int64  │  int64  │
├───────┼──────────────┼─────────┼─────────┤
│     0 │ 249999500000 │       0 │  999998 │
│     1 │ 250000000000 │       1 │  999999 │
└───────┴──────────────┴─────────┴─────────┘
```  
#### `except_(rel)` {#docs:api:python:relational_api::except_rel}  
Select all rows in the first relation, that do not occur in the second relation. The relations must have the same number of columns.  
```python
import duckdb

r1 = duckdb.sql("SELECT * FROM range(10) tbl(id)")
r2 = duckdb.sql("SELECT * FROM range(5) tbl(id)")
r1.except_(r2).show()
```  
```text
┌───────┐
│  id   │
│ int64 │
├───────┤
│     5 │
│     6 │
│     7 │
│     8 │
│     9 │
└───────┘
```  
#### `filter(condition)` {#docs:api:python:relational_api::filtercondition}  
Apply the given condition to the relation, filtering any rows that do not satisfy the condition.  
```python
import duckdb

rel = duckdb.sql("SELECT * FROM range(1_000_000) tbl(id)")
rel.filter("id > 5").limit(3).show()
```  
```text
┌───────┐
│  id   │
│ int64 │
├───────┤
│     6 │
│     7 │
│     8 │
└───────┘
```  
#### `intersect(rel)` {#docs:api:python:relational_api::intersectrel}  
Select the intersection of two relations – returning all rows that occur in both relations. The relations must have the same number of columns.  
```python
import duckdb

r1 = duckdb.sql("SELECT * FROM range(10) tbl(id)")
r2 = duckdb.sql("SELECT * FROM range(5) tbl(id)")
r1.intersect(r2).show()
```  
```text
┌───────┐
│  id   │
│ int64 │
├───────┤
│     0 │
│     1 │
│     2 │
│     3 │
│     4 │
└───────┘
```  
#### `join(rel, condition, type = "inner")` {#docs:api:python:relational_api::joinrel-condition-type--inner}  
Combine two relations, joining them based on the provided condition.  
```python
import duckdb

r1 = duckdb.sql("SELECT * FROM range(5) tbl(id)").set_alias("r1")
r2 = duckdb.sql("SELECT * FROM range(10, 15) tbl(id)").set_alias("r2")
r1.join(r2, "r1.id + 10 = r2.id").show()
```  
```text
┌───────┬───────┐
│  id   │  id   │
│ int64 │ int64 │
├───────┼───────┤
│     0 │    10 │
│     1 │    11 │
│     2 │    12 │
│     3 │    13 │
│     4 │    14 │
└───────┴───────┘
```  
#### `limit(n, offset = 0)` {#docs:api:python:relational_api::limitn-offset--0}  
Select the first *n* rows, optionally offset by *offset*.  
```python
import duckdb

rel = duckdb.sql("SELECT * FROM range(1_000_000) tbl(id)")
rel.limit(3).show()
```  
```text
┌───────┐
│  id   │
│ int64 │
├───────┤
│     0 │
│     1 │
│     2 │
└───────┘
```  
#### `order(expr)` {#docs:api:python:relational_api::orderexpr}  
Sort the relation by the given set of expressions.  
```python
import duckdb

rel = duckdb.sql("SELECT * FROM range(1_000_000) tbl(id)")
rel.order("id DESC").limit(3).show()
```  
```text
┌────────┐
│   id   │
│ int64  │
├────────┤
│ 999999 │
│ 999998 │
│ 999997 │
└────────┘
```  
#### `project(expr)` {#docs:api:python:relational_api::projectexpr}  
Apply the given expression to each row in the relation.  
```python
import duckdb

rel = duckdb.sql("SELECT * FROM range(1_000_000) tbl(id)")
rel.project("id + 10 AS id_plus_ten").limit(3).show()
```  
```text
┌─────────────┐
│ id_plus_ten │
│    int64    │
├─────────────┤
│          10 │
│          11 │
│          12 │
└─────────────┘
```  
#### `union(rel)` {#docs:api:python:relational_api::unionrel}  
Combine two relations, returning all rows in `r1` followed by all rows in `r2`. The relations must have the same number of columns.  
```python
import duckdb

r1 = duckdb.sql("SELECT * FROM range(5) tbl(id)")
r2 = duckdb.sql("SELECT * FROM range(10, 15) tbl(id)")
r1.union(r2).show()
```  
```text
┌───────┐
│  id   │
│ int64 │
├───────┤
│     0 │
│     1 │
│     2 │
│     3 │
│     4 │
│    10 │
│    11 │
│    12 │
│    13 │
│    14 │
└───────┘
```