With respect to the operations in relational algebra (not in SQL):

I'm having trouble understanding why 2 relations have to be union-compatible before the union operation can be applied on them. I came across this question in Ramez Elmasri's Fundamentals of Database Systems.

What is union compatibility? Why do the UNION, INTERSECTION, and DIFFERENCE operations require that the relations on which they are applied be union compatible?

I can understand why this is the case for INTERSECTION & DIFFERENCE but I don't see why this constraint also applies for the UNION operation. Why can't we just union both relations together (something like a full outer join)? What's stopping anyone from doing that?

From what I've gathered in my Google searches, the answer goes something like because UNION, INTERSECTION & DIFFERENCE are set operations which are binary, and so for the result to be a relation, it is bound to have tuples of same number of attributes and the domain should be same too. (Here and here)

What I'm understanding from the answers from my searches is that the result has to have the tuples with same number of attributes and domains. My question then is wouldn't the result of full outer join not be a relation? (According to what's stated above).

  • 1
    Are you thinking about an OUTER UNION, i.e what Date & Darwen refers to as <OR>? – Lennart Oct 31 '17 at 19:03
  • For the same reason you can't add apples and oranges? – Vérace Oct 31 '17 at 21:54
  • Seriously though, give some sample data and a desired result! – Vérace Oct 31 '17 at 22:34
  • @Vérace You mentioned that this is the same reason why we can't add apples and oranges but in a sense we are "allowed" to add apples & oranges using a full outer join. To add on, I don't have any code or sample data; this is merely a question from the reference book I was reading. – qwertyc Nov 1 '17 at 7:23
  • OK - academic/theoretical questions are welcome here! I would suggest that you are not "adding" the records with an OUTER JOIN but "combining" them! I can see where the join might be of use - can you provide me with a scenario where your idea would be beneficial? – Vérace Nov 1 '17 at 7:40

Well, my answer may not be serving your question but what you are asking is to have an OUTER UNION as suggested by @Lennart. The result you are expecting is what OUTER UNION will give you. Whereas the UNION operator will only work if the two relations have same types of tuples. So you can consider that OUTER UNION is a more general form of UNION operator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.