Selecto.SetOperations (Selecto v0.4.5)

Copy Markdown

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

# 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)

Summary

Functions

except(left_query, right_query, opts \\ [])

Create an EXCEPT set operation between two queries.

intersect(left_query, right_query, opts \\ [])

Create an INTERSECT set operation between two queries.

union(left_query, right_query, opts \\ [])

Create a UNION set operation between two queries.

Functions

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

  • :all - Use EXCEPT ALL to include duplicates in difference (default: false)
  • :column_mapping - Map columns between incompatible schemas

intersect(left_query, right_query, opts \\ [])

Create an INTERSECT set operation between two queries.

Returns only rows that appear in both queries.

Options

  • :all - Use INTERSECT ALL to include duplicate intersections (default: false)
  • :column_mapping - Map columns between incompatible schemas

union(left_query, right_query, opts \\ [])

Create a UNION set operation between two queries.

Options

  • :all - Use UNION ALL to include duplicates (default: false)
  • :column_mapping - Map columns between incompatible schemas

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"}
  ]
)