CODEWITHSUNDEEP
sqlalchemy
Buttons840
Buttons840

Reputation: 9637

Can you perform a UNION without a subquery in SQLAlchemy?

Another question shows how to do a union in SQLAlchemy.

Is it possible to perform a union in SQLAlchemy without using a subquery?

For example, in the linked question SQLAlchemy produces SQL of the form:

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

But I would like SQLAlchemy to produce SQL like:

SELECT column_a FROM table_a UNION SELECT column_b FROM table_b

The latter SQL is shorter, doesn't use a subquery, and does the same thing. Can SQLAlchemy produces a union like the latter query?

Upvotes: 5

Views: 4207

Answers (1)

cowbert
cowbert

Reputation: 3442

Per 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 == '[email protected]'),
    addresses.select().where(addresses.c.email_address.like('%@yahoo.com')),
).order_by(addresses.c.email_address)
u.compile()

This produces:

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

Params: ('[email protected]', '%@yahoo.com')

Upvotes: 7

Related Questions