CODEWITHSUNDEEP
sqloracle-database
user2924127
user2924127

Reputation: 6252

Oracle SQL - UNION ALL return rows even if they don't exist

I Have the following query which unions two smaller queries:

SELECT 'Static 1' type, somefield, some_id   
      FROM (SELECT some_id, somefield
               FROM sometable
                WHERE someothercolumn = 'Static 1'
                and someid = :id)
     UNION ALL
     SELECT 'Static 1' type, somefield, some_id   
      FROM (SELECT some_id, somefield
               FROM sometable
                WHERE someothercolumn = 'Static 2'
                and someid = :id)

If the query returns a row and query 2 returns a row then I will have two rows like this:

type      |      somefield     |     some_id
---------------------------------------------
Static 1  |       somevalue    |      1
Static 2  |       somevalue    |      1

Which is the output I want. If one of those two queries doesn't return a row (or even if both don't return a row) then I will have zero rows returned. This is not what I want. I always want the 'type' and the 'some_id' to return even if there is no data. So if both of those queries didn't have any data my expected result would be:

type      |      somefield     |     some_id
---------------------------------------------
Static 1  |                    |      1
Static 2  |                    |      1

I have tried to UNION ALL each query with a NOT EXISTS but that way the 'some_id' would need to be some arbitrary hardcoded value. What I want is for it to always show the bind variable passed, :id.

Upvotes: 1

Views: 1855

Answers (3)

Laurenz Albe
Laurenz Albe

Reputation: 246918

Perhaps something like:

WITH a AS (/* subquery 1 */),
     b AS (/* subquery 2 */)
SELECT (SELECT a.col1 FROM a),
       (SELECT a.col2 FROM a),
       ...
       FROM dual
UNION ALL
SELECT (SELECT b.col1 FROM b),
       (SELECT b.col2 FROM b),
       ...
       FROM dual

Upvotes: 1

forpas
forpas

Reputation: 164099

Make a CTE for your query and another CTE for the 2 extra rows and use UNION ALL and NOT EXISTS it like this:

WITH 
  cte1 AS (
    <your query here>
  ),
  cte2 AS (
    SELECT 'Static 1' type, null somefield, :id FROM dual
    UNION ALL
    SELECT 'Static 2' type, null somefield, :id FROM dual 
  )
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
WHERE NOT EXISTS (SELECT 1 FROM cte1)

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142798

One option is to select the header dummy row with the NOT EXISTS to decide whether to return it (yes, if actual row in the table doesn't exist; no, if row exists).

Have a look at the example: see line #4 which is commented at the moment which means that your table doesn't contain row that satisfies that condition, so a dummy header row will be displayed:

SQL> with test (type, some_id) as
  2    (select 'Static 99', 44 from dual union all
  3     select 'Static 2' , 57 from dual
  4     --union all select 'Static 1', 66 from dual   -- toggle to see the difference
  5    )
  6  -- This is a header which won't be displayed if table contains
  7  -- type = 'Static 1' and some_id = 66
  8  select 'Static 1' type, 1 some_id
  9  from dual
 10  where not exists (select null
 11                    from test
 12                    where type = 'Static 1'
 13                      and some_id = 66
 14                   )
 15  union all
 16  -- This returns actual rows (if they exist)
 17  select 'Static 1' type, some_id
 18  from (select some_id
 19        from test
 20        where type = 'Static 1'
 21          and some_id = 66
 22       );

TYPE        SOME_ID
-------- ----------
Static 1          1

SQL>

However, if it exists (line #4 uncommented), then actual data is displayed, without that dummy header row:

SQL> with test (type, some_id) as
  2    (select 'Static 99', 44 from dual union all
  3     select 'Static 2' , 57 from dual
  4     union all select 'Static 1', 66 from dual   -- toggle to see the difference
  5    )
  6  -- This is a header which won't be displayed if table contains
  7  -- type = 'Static 1' and some_id = 66
  8  select 'Static 1' type, 1 some_id
  9  from dual
 10  where not exists (select null
 11                    from test
 12                    where type = 'Static 1'
 13                      and some_id = 66
 14                   )
 15  union all
 16  -- This returns actual rows (if they exist)
 17  select 'Static 1' type, some_id
 18  from (select some_id
 19        from test
 20        where type = 'Static 1'
 21          and some_id = 66
 22       );

TYPE        SOME_ID
-------- ----------
Static 1         66

SQL>

If that does what you want, apply the same principle to the second select you're using (to Static 2).

Upvotes: 1

Related Questions