72503

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?

Answer1:

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

Recommend

  • ORA-02287: sequence number not allowed here
  • PostgreSQL merge two queries with COUNT and GROUP BY in each
  • SQL Constraint that one column value cannot be greater than another in a different table
  • R printing to CSV subsetted 'pivot' tables
  • Difference of “FROM a LEFT JOIN b” vs. “FROM a, b”
  • Constraint to avoid combination of foreign keys
  • Select distinct ordered pairs from table join grouped by event's most recent date
  • Query in SQL2000
  • Copy unique data from one table to another
  • Postgres natural order by
  • Is it possible to replace NL join with HS join in sql
  • Merge table on either of the 2 columns in pandas
  • Oracle to retrieve maximum record
  • how to get one column data from one table into another table as different columns in PL/SQL
  • Reducing number of query for inner join when joining base on same table
  • GROUP_CONCAT mysql statement error
  • mysql - The target table of the UPDATE is not updatable
  • Aggregate boolean values to true if any of the source columns is true
  • Referencing the foreign key of another schema
  • AWS EC2 t2.micro instance very unstable
  • Net-ssh session timeout
  • Division with Aggregate Functions in SQL Not Behaving as Expected
  • Linq Full Outer Join on Two Objects
  • Update varbinary(MAX) field in SQLServer 2012 Lost Last 4 bits
  • distinct values from multiple fields within one table ORACLE SQL
  • remove unicode characters but keep all special and English characters with preg_replace
  • Multiple producers single consumer locking schema
  • NUnit 3.0 TestCase const custom object arguments
  • Plotting line graph with factors in R
  • Access Android Market through SSH tunnel
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • ImageMagick, replace semi-transparent white with opaque white
  • FFmpeg Conversion Error
  • Scrapy recursive link crawler
  • Display Images one by one with next and previous functionality
  • SQL merge duplicate rows and join values that are different