'Alternative to UNION clause

I'm trying to teach myself and better understand alternative methods to a UNION and discover when I can use joins.

As I'm playing with this I can't seem to get what I want without a UNION. Is it possible to write this in a single query?

SELECT DISTINCT a.id
  FROM table1 a, table2 b
 WHERE a.id = b.id
       AND a.ind IS NULL
       AND b.year >= '2017'
       AND b.code IN ('01','02','03')
       AND b.flag NOT IN ('F','L')
UNION
SELECT DISTINCT a.id
  FROM table1 a, table3 c
 WHERE a.id = c.id
       AND a.ind IS NULL
       AND c.area = 'MAIN'
       AND SYSDATE >= c.start

Thanks in advance for any guidance or help.

sqloracle


Solution 1:[1]

Whenever you see a distinct, resources have been wasted.

SELECT a.id
  FROM table1 a
  where a.ind IS NULL
    and (   exists (select null from table2 b
                      WHERE a.id = b.id
                        AND b.year >= '2017'
                        AND b.code IN ('01','02','03')
                        AND b.flag NOT IN ('F','L') )
         or exists (SELECT null FROM table3 c
                      WHERE a.id = c.id
                        AND c.area = 'MAIN'
                        AND SYSDATE >= c.startdt)
        )

Indexes on table2 (id,year,code,flag) and table3 (id,area,startdt) won't hurt performance. Oracle 11gR2 did not allow me to have a column named "start".

Solution 2:[2]

I would use this

select a.id
from table1 a
where
  a.ind is null and (
  a.id in (
    select b.id
    from table2 b
    where
    b.year >= '2017'
    and b.code IN ('01','02','03')
    and b.flag NOT IN ('F','L')  
  ) or
  a.id in (
    select c.id
    from table3 c
    where
    c.area = 'MAIN'
    and sysdate >= c.start
  )
)

DISTINCT + table joins can be rewritten as IN/EXISTS most of the times. In several databases it might even select a better execution plan (e.g: Oracle)

Solution 3:[3]

Please let me know if using Full outer join makes sense

with cte as (
select 1 as nu union 
select 2 as nu union 
select 3 as nu union 
select 4 as nu union 
select 5 as nu union 
select 6 as nu )
,cte2 as (
select 1 as nu union 
select 2 as nu union 
select 3 as nu )
,cte3 as (
select 7 as nu union 
select 8 as nu union 
select 9 as nu )
select coalesce(cte.nu,cte2.nu,cte3.nu)
from cte
full outer join cte2 on cte.nu = cte2.nu
full outer join cte3 on cte.nu = cte3.nu

Solution 4:[4]

Please check if this works

SELECT DISTINCT a.id
  FROM table1 a, table2 b, table3 c
 WHERE (a.id = b.id
        OR a.id = c.id)
       AND a.ind IS NULL
       AND (( b.year >= '2017'
       AND b.code IN ('01','02','03')
       AND b.flag NOT IN ('F','L'))
       OR (c.area = 'MAIN'
       AND SYSDATE >= c.start))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 gpeche
Solution 3 SSMan
Solution 4 Avani

Related Questions