Selecto.SetOperations (Selecto v0.3.16)
Set operations for combining query results using UNION, INTERSECT, and EXCEPT.
Set operations allow combining results from multiple Selecto queries using standard SQL set operations. All participating queries must have compatible column counts and types.
examples
Examples
# Basic UNION - combine results from two queries
query1 = Selecto.configure(users_domain, connection)
|> Selecto.select(["name", "email"])
|> Selecto.filter([{"active", true}])
query2 = Selecto.configure(contacts_domain, connection)
|> Selecto.select(["full_name", "email_address"])
|> Selecto.filter([{"status", "active"}])
combined = Selecto.union(query1, query2, all: true)
# INTERSECT - find common records
premium_active = Selecto.intersect(premium_users, active_users)
# EXCEPT - find differences
free_users = Selecto.except(all_users, premium_users)
# Chained set operations
result = query1
|> Selecto.union(query2)
|> Selecto.intersect(query3)
|> Selecto.except(query4)Link to this section Summary
Functions
Create an EXCEPT set operation between two queries.
Create an INTERSECT set operation between two queries.
Create a UNION set operation between two queries.
Link to this section Functions
Link to this function
except(left_query, right_query, opts \\ [])
Create an EXCEPT set operation between two queries.
Returns rows from the first query that don't appear in the second query.
options
Options
:all- Use EXCEPT ALL to include duplicates in difference (default: false):column_mapping- Map columns between incompatible schemas
Link to this function
intersect(left_query, right_query, opts \\ [])
Create an INTERSECT set operation between two queries.
Returns only rows that appear in both queries.
options
Options
:all- Use INTERSECT ALL to include duplicate intersections (default: false):column_mapping- Map columns between incompatible schemas
Link to this function
union(left_query, right_query, opts \\ [])
Create a UNION set operation between two queries.
options
Options
:all- Use UNION ALL to include duplicates (default: false):column_mapping- Map columns between incompatible schemas
examples
Examples
# Basic UNION (removes duplicates)
Selecto.union(query1, query2)
# UNION ALL (includes duplicates, faster)
Selecto.union(query1, query2, all: true)
# UNION with column mapping
Selecto.union(customers, vendors,
column_mapping: [
{"name", "company_name"},
{"email", "contact_email"}
]
)