# SQLAlchemy 2.0(07)查詢資料(4-union and other operator)
###### tags: `python` `sqlalchemy`

[官方文件](https://docs.sqlalchemy.org/en/20/tutorial/data_select.html)

有時候我們的查詢並不單單是一個來源，有多個，然後我們希望可以垂直堆疊把資料整合起來，這可以利用`union`與`union all`。兩者最主要的差異在前者排除重覆資料，後者則是全部呈現。

## union

from sqlalchemy import union_all
stmt1 = select(user_table).where(user_table.c.name == "sandy")
stmt2 = select(user_table).where(user_table.c.name == "spongebob")
u = union_all(stmt1, stmt2)
with engine.connect() as conn:
    result = conn.execute(u)
    print(result.all())

SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?

這很單純的利用函數`union_all`把兩個`select`結合起來。我們也可以把這個結合起來的查詢`u`做為一個子查詢的操作：

u_subq = u.subquery()
stmt = (
    select(u_subq.c.name, address_table.c.email_address)
    .join_from(address_table, u_subq)
    .order_by(u_subq.c.name, address_table.c.email_address)
)
with engine.connect() as conn:
    result = conn.execute(stmt)
    print(result.all())

SELECT anon_1.name, address.email_address
FROM address JOIN
  (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  FROM user_account
  WHERE user_account.name = ?
UNION ALL
  SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
  FROM user_account
  WHERE user_account.name = ?)
AS anon_1 ON anon_1.id = address.user_id
ORDER BY anon_1.name, address.email_address

## exists
`exists`也是一資料庫查詢常用的作法，有人說有索引就用`exists`，沒索引就用`in`，再不然就直接`inner join`，反正青菜蘿蔔都有人說好，就自己體驗一下吧：

subq = (
    select(func.count(address_table.c.id))
    .where(user_table.c.id == address_table.c.user_id)
    .group_by(address_table.c.user_id)
    .having(func.count(address_table.c.id) > 1)
).exists()
with engine.connect() as conn:
    result = conn.execute(select(user_table.c.name).where(subq))
    print(result.all())

SELECT user_account.name
FROM user_account
WHERE EXISTS (SELECT count(address.id) AS count_1
FROM address
WHERE user_account.id = address.user_id GROUP BY address.user_id
HAVING count(address.id) > ?)

範例主要是先做一個聚合操作，利用`count`統計數量，然後用`having`做個條件排除，最後搭配`.exists()`弄成一個子查詢，在查詢的時候把這個子查詢做為條件`where`的參數。如果你想要的是NOT EXISTS的話，就把這個子查詢加個小蚯蚓變成`.where(~subq)`就可以了。

## func
最後提到，多數我們平常操作資料庫常用的函數都是在`func`裡面，所以你可以用`func.upper`、`func.lower`...真找不到的情況下就來看看官方文件，相信會有所收獲的。