union and filter

Database
Enthusiast

union and filter

select item

 , location

 , date

 , quantity

from t1

union

select item

 , location

 , date

 , quantity

from t2

there will be cases where

item,location,date will be the same, but quantity different.

in that case, I only want to see the values from t1

how can I exclude records from t2?

1 REPLY
Senior Apprentice

Re: union and filter

A direct translation of should be:

select item
, location
, date
, quantity
from
(
select 1 as x, item
, location
, date
, quantity
from t1
union
select 2 as x, item
, location
, date
, quantity
from t2
) as dt
qualify
row_number()
over (partition by item, location, date order by x) = 1

But changing it to a Full Outer Join might be more efficient, this should return the same result:

select coalesce(t1.item, t2.item),
, coalesce(t1.location, t2.location
, coalesce(t1.date, t2.date
, coalesce(t1.quantity, t2.quantity)
from
(
select item
, location
, date
, quantity
from t1
) as t1
full outer join
(
select item
, location
, date
, quantity
from t2
) as t2
on t1.item = t2.item
and t1.location = t2.location
and t1.date = t2.date

Dieter