conditional union

Question

Let's say I have three subqueries combined in a UNION.

select * from (
    select nr, pos, '1' from foo
    union all
    select nr, pos, '2' from bar
    union all
    select nr, pos, '3' from foobar
)

What I need is a result that returns rows from foobar only if the nr value was not returned by the first sub-query (from foo).

The nr column has to be the only condition, not the entire row, so a UNION wouldn't help. Am I right?

Sorry I don't know how to explain this better.

Answer 1

Add a where clause to the last select to make it return results only if the first two queries had no results. See updated query below:

select nr, pos, '1' from foo
union all
select nr, pos, '2' from bar
union all
select nr, pos, '3' from foobar fb
 where not exists (select 1 from foo f where fb.nr = f.nr)
   and not exists (select 1 from bar b where fb.nr = b.nr)

Answer 2

maybe a trick like this:

select distinct min(x), nr, pos from (
    select nr, pos, '1' as x from foo
    union all
    select nr, pos, '2' from bar
    union all
    select nr, pos, '3' from foobar
)
group by nr, pos