Can you execute UNION without a subquery in SQLAlchemy?

Question

Another question shows how to do a join in SQLAlchemy.

Can I perform a join in SQLAlchemy without using a subquery?

For example, in a related question, SQLAlchemy creates SQL forms:

SELECT * FROM (SELECT column_a FROM table_a UNION SELECT column_b FROM table_b)

But I would like SQLAlchemy to create SQL like:

SELECT column_a FROM table_a UNION SELECT column_b FROM table_b

The latter SQL is shorter, does not use a subquery and does the same. Can SQLAlchemy create a join similar to the last query?

Answer 1

In the SQLAlchemy SQL Expression Language Tutorial :

You can use sqlalchemy.sql.expression.union :

 >>> from sqlalchemy.sql import union >>> u = union( ... addresses.select(). ... where(addresses.c.email_address == ' [e-mail] '), ... addresses.select(). ... where(addresses.c.email_address.like('[e-mail]')), ... ).order_by(addresses.c.email_address) >>> u.compile() SELECT addresses.id, addresses.user_id, addresses.email_address FROM addresses WHERE addresses.email_address = ? UNION SELECT addresses.id, addresses.user_id, addresses.email_address FROM addresses WHERE addresses.email_address LIKE ? ORDER BY addresses.email_address (' [e-mail] ', '[e-mail]')