Apache Drill中Union操作是否支持NULL值？恳请技术指导

Absolutely! Apache Drill fully supports using NULL values within UNION operations — your sample query should work exactly as intended, provided your table's column data types are compatible across the two SELECT clauses.

Let me break down why this works and what to keep in mind:

• Drill adheres to standard SQL rules for UNION, which requires corresponding columns in each SELECT to have compatible data types. Since NULL is a universal placeholder that aligns with any data type, it plays nicely here. For example, if your Supplydate and OrderDate columns are DATE types, the NULL values in your query will be implicitly treated as DATE to match the non-null columns.
• Your query already ensures the number of columns matches across both SELECT statements (5 columns each: ID, plus four date-related columns), which is a hard requirement for UNION to work.
• If you want to make the data type of NULL explicit (to avoid any potential edge cases with type inference), you can cast it like this: CAST(NULL AS DATE) instead of just NULL. This is optional but can make your query more self-documenting.

Here's your sample query formatted for clarity:

SELECT 
  ID, 
  Supplydate as A_SupplyDate,
  OrderDate as A_Orderdate, 
  NULL as B_SupplyDate,
  NULL as B_OrderDate 
FROM tbl 
WHERE Category='A' 

UNION 

SELECT 
  ID, 
  NULL as A_SupplyDate,
  NULL as A_Orderdate, 
  Supplydate as B_SupplyDate,
  OrderDate as B_OrderDate 
FROM tbl 
WHERE Category='B'

Just double-check that the data types of Supplydate and OrderDate are consistent between the two clauses (they should be, since they're coming from the same table) and you're good to go.

内容的提问来源于stack exchange，提问作者user1046415