4

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?

Community
  • 1
  • 1
Buttons840
  • 9,239
  • 15
  • 58
  • 85

1 Answers1

5

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 == 'foo@bar.com'),
    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: ('foo@bar.com', '%@yahoo.com')

cowbert
  • 3,212
  • 2
  • 25
  • 34