CODEWITHSUNDEEP
sql
Hauke L.
Hauke L.

Reputation: 293

Union of tables without repeating conditions

I have three tables A, B, C. A and B have the same structure, i.e. the same columns and already a distinct key. What I want to do is unify A and B, and then join the union with C. I know I can do this in this way:

(SELECT x FROM A INNER JOIN C ON y WHERE z) 
UNION 
(SELECT x FROM B INNER JOIN C ON y WHERE z)

However, then I have to repeat the conditions which is very inconvenient to me, especially when the query gets longer and needs to be adjusted frequently.

If I do the union inside, then the query does not complete for me at all (may be it just takes unbearably long)

SELECT y
FROM (SELECT * FROM A UNION SELECT * FROM B) U
INNER JOIN C on y
WHERE z

Is there a more elegant, efficient way of doing this without repetitions?

I use SAP SQL.

Upvotes: 1

Views: 399

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269783

This should work pretty well if your columns don't have duplicates:

SELECT DISTINCT x
FROM ((SELECT *
       FROM A INNER JOIN 
            C
            ON y 
      ) 
      UNION ALL
      (SELECT *
       FROM B INNER JOIN
            C
            ON y
      )
     ) abc
WHERE z;

Upvotes: 0

Ruben Helsloot
Ruben Helsloot

Reputation: 13129

The reason the query doesn't complete the second time, is that by union-ing, you lose all context and statistics about the table, it isn't able to use indexes, and thus has to fall back on slower methods.

It sounds like the first option is your only one, but you might be able to use a VIEW to prevent you from having to type the same thing over and over. See their documentation for details.

Upvotes: 1

Related Questions