SQLAlchemy UNION: Does it Always Need Subqueries?

SQLAlchemy builds the UNION using subqueries even if you don't explicitly write them in your code. This might seem counter-intuitive, but it ensures the queries are properly formed and compatible with different database systems.

from sqlalchemy import select, union

# Define two separate queries
query1 = select("*").from_table("users").where(users.c.name == "Alice")
query2 = select("*").from_table("users").where(users.c.email.like("%@gmail.com"))

# Combine the queries using union
combined_query = union(query1, query2)

# Execute the query and fetch results
# (assuming you have a database connection established)
results = engine.execute(combined_query)

# Print each row in the results
for row in results:
  print(row)

This code defines two separate queries using select and filters them based on specific criteria. Then, it combines them using the union function from the sqlalchemy.sql module.

While you can't avoid subqueries completely, here are some things to keep in mind:

• Consider ORM: If you're using SQLAlchemy's Object Relational Mapper (ORM), you can build your queries using declarative models and relationships between them. The ORM will then translate your object-oriented queries into the appropriate UNION with subqueries.
• Focus on query logic: Write your queries as you normally would, specifying the tables, columns, and filtering conditions. SQLAlchemy will handle the subquery construction for the UNION.