22

Raw SQL desired:

SELECT
    id
FROM
   (SELECT some_table.id FROM some_table WHERE some_table.some_field IS NULL) AS subq1
   UNION
   (SELECT some_table.id WHERE some_table.some_field IS NOT NULL)
LIMIT 10;

Here is the python code:

import sqlalchemy

SOME_TABLE = sqlalchemy.Table(
 'some_table',
 sqlalchemy.MetaData(),
 sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
 sqlalchemy.Column('some_field', sqlalchemy.Text))

stmt_1 = sqlalchemy.sql.select(SOME_TABLE.columns).where(SOME_TABLE.columns.some_field != None)
stmt_2 = sqlalchemy.sql.select(SOME_TABLE.columns).where(SOME_TABLE.columns.some_field == None)

# This gets a programming error.
stmt_1.union(stmt_2).limit(10);

Here is the outputted SQL (with parameters swapped in) that gets this error: ERROR: syntax error at or near "UNION":

SELECT some_table.id, some_table.some_field
FROM some_table
WHERE some_table.some_field IS NOT NULL
 LIMIT 10 UNION SELECT some_table.id, some_table.some_field
FROM some_table
WHERE some_table.some_field IS NULL
 LIMIT 10
 LIMIT 10

How can I alias the subqueries?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
kkaehler
  • 493
  • 1
  • 4
  • 13

1 Answers1

27

I used a little bit different approach:

# the first subquery, select all ids from SOME_TABLE where some_field is not NULL
s1 = select([SOME_TABLE.c.id]).where(SOME_TABLE.c.some_field != None)

# the second subquery, select all ids from SOME_TABLE where some_field is NULL
s2 = select([SOME_TABLE.c.id]).where(SOME_TABLE.c.some_field != None)

# union s1 and s2 subqueries together and alias the result as "alias_name"
q = s1.union(s2).alias('alias_name')

# run the query and limit the aliased result to 10
session.query(q).limit(10)

Here is the produced SQL query:

SELECT alias_name.id AS alias_name_id 
FROM (SELECT some_table.id AS id 
FROM some_table 
WHERE some_table.some_field IS NOT NULL UNION SELECT some_table.id AS id 
FROM some_table 
WHERE some_table.some_field IS NULL) AS alias_name 
LIMIT 10

I think this is the result you wanted.

André C. Andersen
  • 8,955
  • 3
  • 53
  • 79
jbub
  • 2,586
  • 23
  • 23
  • In another question I asked if it's possible to perform the union without using a subquery: http://stackoverflow.com/q/36876121/565879 – Buttons840 Apr 26 '16 at 21:38
  • 7
    If you have more than two sub-queries that you want to union, you can use `union(s1, s2, s3, ...)`. – tsauerwein May 24 '16 at 15:52
  • 8
    @MatthewMoisen, That is absolutely wrong. `some_field != None` will produce `IS NOT NULL`, however, `is not None` will just return the boolean value `True` because the `is` keyword/operator cannot be overloaded like `!=` can. The SQLAlchemy docs [specifically state](https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#common-filter-operators) that if you don't like fighting your linters for this very reason, to use `.isnot()` instead. – OozeMeister Feb 07 '19 at 23:31