Is it always possible to avoid using a UNION in SQL?

Question

Suppose I have a super-simple table (called useful_table) with one column, like the below:

+----------+
| a_number |
+----------+
|     2    |
|     4    |
|     8    |
+----------+

And suppose I have a query with a UNION:

  SELECT
    a_number
  FROM
    useful_table
  WHERE
    a_number = 2
UNION
  SELECT
    a_number + 1
  FROM
    useful_table
  WHERE
    a_number = 2
;

I expect the result to be:

+----------+
| a_number |
+----------+
|     2    |
|     3    |
+----------+

Example query runs in Oracle, but should be applicable to other dialects.

Setup code here:

create table useful_table(a_number NUMBER);
insert into useful_table values (2);
insert into useful_table values (4);
insert into useful_table values (8);

Is it possible to convert this query?

Is it always possible to convert UNIONs to an alternative?

Assumptions:

I am using a subset of (Oracle) SQL, meaning:
- I cannot use temporary tables.
- I cannot use UNION or UNION ALL.
Both sides of the UNION access data from the same table, but may change the raw data in different ways.

Yes, it is possible - for example with a cross join to a small table that can be created on the fly in the `FROM` clause, and a `CASE` expression in the `WHERE` clause. But before we go too far - do you really mean `UNION` and not `UNION ALL`? (You are aware that they both exist, and have different meaning, aren't you?) And then, **why** would you want to write the query without using `UNION` (or, more likely, `UNION ALL`)? What's the bigger picture here? — , Dec 18 '18 at 18:36
`If the SELECT clauses were the same (even if the WHERE clauses were different), then the query should be convertible using CASEs.` - This is not always true. There is only one row with the value of `a_number` = `2`. You can use `CASE` to alter the value is returned, but pulling both `2` and `3` requires an additional row with a value of `2`. This does not exist without a `UNION` or a `JOIN`, which `CASE` cannot produce. A more complete example might help us better understand what you're trying to do. — Patrick Tucci, Dec 18 '18 at 18:42
It's possible, by for example by left joining to a list of numbers. [Example here](https://stackoverflow.com/a/53795616/4003419). But if many fields need to be selected to get the same output as with unions, then it'll need also many coalesce's. — LukStorms, Dec 18 '18 at 19:23
@mathguy I think this is applicable to `UNION` and `UNION ALL`, but I think that you're right; `UNION ALL` is more applicable here. — A Jar of Clay, Dec 19 '18 at 00:48
@PatrickTucci you're right, `CASE`s won't be able to make new rows (but can get the information in new columns). I've removed that line to hopefully reduce confusion. — A Jar of Clay, Dec 19 '18 at 00:59
@LukStorms I'm not sure that that would work for a more general case, when the fields may not all be numbers? If you've got an answer though, it would be great to have it fleshed out! — A Jar of Clay, Dec 19 '18 at 01:05
@AJarofClay Have a look at the example in my previous comment. You'll notice that the numbers are just used in the JOIN. Putting the joined tables/sub-queries next to each other on separated rows if it would select f.e. `table1.*, subquery1.*, table2.*` — LukStorms, Dec 19 '18 at 09:23

score 2 · Accepted Answer · answered Dec 18 '18 at 19:04

2

It is always possible to rewrite a UNION as a FULL OUTER JOIN.

So

  SELECT
    <expression1>
  FROM
    useful_table
  WHERE
    <predicate1>
UNION
  SELECT
   <expression2>
  FROM
    useful_table
  WHERE
    <predicate2>
;

would become

SELECT DISTINCT COALESCE(u1.<expression1> , u2.<expression2> )
FROM            useful_table u1
FULL OUTER JOIN useful_table u2
ON              1 = 0
WHERE           u1.<predicate1>
                OR u2.<predicate2>

Or in your specific example

SELECT DISTINCT COALESCE(u1.a_number, u2.a_number + 1)
FROM   useful_table u1
       FULL OUTER JOIN useful_table u2
                    ON 1 = 0
WHERE  u1.a_number = 2
        OR u2.a_number = 2

answered Dec 18 '18 at 19:04

Martin Smith

438,706
87
741
845

It is not always possible. What if you want both rows and there is overlapping criteria, to me that means you want a Union. – Joe C Dec 18 '18 at 19:09
@JoeC - The combined `FULL OUTER JOIN` and `DISTINCT` will return the same results. Please provide a specific example where you think they don't return the same thing. The `FULL OUTER JOIN` on false condition basically provides a `UNION ALL` ed result set but with the columns not merged (done by `COALESCE`). – Martin Smith Dec 18 '18 at 19:11

GMB · Answer 2 · 2018-12-18T19:39:33.080

1

One solution would be to duplicate output rows using a CROSS JOIN with a CONNECT BY cte.

In general however this works under the assumptions that :

only one table is involved
there is a single WHERE clause
the operations to perform on duplicated records are simple enough to be implemented based on an incrementing integer, as returned by the CONNECT BY cte (which still leaves quite some room to act)

The following query does match your exact use case :

with data as (select level -1 l from dual connect by level <= 2)
select a_number + l
from useful_table, data
where a_number = 2

edited Dec 18 '18 at 19:39

answered Dec 18 '18 at 18:49

GMB

216,147
25
84
135

Perhaps you could elaborate whether you think this will extend to the general case? – A Jar of Clay Dec 18 '18 at 19:04
@JarOfClay : done ; but your question is quite wide so it is uneasy to provide a very accurate answer... – GMB Dec 18 '18 at 19:19

score -3 · Answer 3 · answered Dec 18 '18 at 19:12

I will use Union when nothing else will do. I have always found that joins and case constructs from one query always perform better than a Union.

However as someone commented, there is a time when a row can match two cases criteria and it cant happen with case logic.

Queries are specific to the goal, but I think it is fair to say that you can not always replace Union.

In your specific use case, it can.

Is it always possible to avoid using a UNION in SQL?

3 Answers3