SQL : instead of using a union want to use a where clause but one of the clauses is only true if ano-CodePudding

I would like to not use a union in my SQL query - how can I rewrite the following query?

select 
    id,
    status,
    date
from 
    table
where 
    status = 'Active'
union
select 
    id,
    status,
    date
from 
    table
where 
    status = 'Cancelled'
    and cancel_reason_id is not null

How can I rewrite this without the union?

CodePudding user response：

Use Boolean logic:

select 
    id,
    status,
    date
from table
where status = 'Active'
or (status = 'Cancelled' and cancel_reason_id IS NOT NULL)

If you are combining with other conditions then make sure you have the correct parentheses to make it evaluate properly, e.g.:

select 
    id,
    status,
    date
from table
where
(
  status = 'Active'
  or (status = 'Cancelled' and cancel_reason_id IS NOT NULL)
)
and some_other_condition

... so you aren't caught out by the condition precedence rules.

CodePudding user response：

Looks like

select 
    id,
    status,
    date
from table
where status = 'Active'
   or (status = 'Cancelled' and cancel_reason_id is not null)