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

Questions

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

byMR

October 5, 2022

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?

>Solution :

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.