Before you can take the union or the difference of two relations in relational algebra, what must be true of them? Explain what "union compatibility" means and what breaks without it.
answer
- same degree + compatible types per position
- strict model: same attribute names, else ρ rename
- SQL matches positionally, names from first operand
- join builds a heading; set ops preserve one
- mismatch = compile-time error, not runtime
basics
~20 sThe two relations must be union-compatible: same number of attributes, in corresponding positions, with compatible types. Without that, the result would have no well-defined heading, so the expression is simply invalid — not merely wrong at runtime.
solid answer
~50 sUnion `∪`, intersection `∩` and difference `−` are **binary operators over relations of the same shape**. Union compatibility requires: - the same **degree** (number of attributes), and - **compatible domains** for attributes in corresponding positions. Strict formulations of the relational model additionally require the same attribute *names*, and provide the rename operator ρ to fix mismatches; SQL relaxes this and matches columns **positionally**, taking the result column names from the first operand and coercing types where it can. Without compatibility there is no sensible heading for the result — you cannot say what a tuple of `R ∪ S` even looks like — so the expression is ill-formed. This is a *type* error, caught before execution. The practical trap in SQL: positional matching means two `SELECT`s with the same column names in a different order will union happily and produce silently wrong data, as long as the types are compatible.
code
sql · 9 lines-- correct: matching positions
SELECT city, country FROM supplier
UNION
SELECT city, country FROM customer;
-- executes fine, semantically wrong: positions swapped on the second branch
SELECT city, country FROM supplier
UNION
SELECT country, city FROM customer;go deeper
Say the two relations must have the same number of attributes with matching types in the same positions, and that a mismatch is rejected outright.
Add the named-attribute strictness versus SQL's positional matching, the rename operator as the fix, and result names coming from the first branch.
Raise the silent-swap hazard when types coincide, the fragility of SELECT * in set operations, and the not-distinct-from matching rule for nulls.
Frame it as a typing discipline: the set operators are the only place the engine cannot tell you your semantics are wrong, so enforce explicit column lists and shared views as the contract between branches.
## Why the restriction exists at all Relational algebra is **closed**: every operator returns a relation, and a relation has a single heading — a fixed set of named, typed attributes shared by all its tuples. The set operators combine tuples from two inputs into one output body. For that output to be a relation, the two inputs' tuples must be *the same kind of thing*. If R's tuples are `(emp_id, name)` and S's are `(order_id, total, ship_date)`, there is no heading that could describe the mixture. Hence the compatibility precondition. Contrast this with join, which has no such requirement: join *builds* a new, wider heading from two different ones. Set operators do not build anything — they select from an existing shape. ## The rules precisely Two relations R and S are **union-compatible** when: 1. **Same degree**: they have the same number of attributes. 2. **Corresponding attributes are type-compatible**: the i-th attribute of R and the i-th attribute of S draw from the same (or compatible) domain. 3. **In the strict named-attribute model, same attribute names.** Under this reading, heading equality is the requirement, and order is irrelevant because a heading is a *set* of attributes. When names differ, you apply the rename operator ρ first: `R ∪ ρ_{cust_id → id}(S)`. The difference between formulations (2) and (3) is worth knowing, because it explains why the textbook and the SQL standard feel different. Textbook algebra with named attributes matches by *name*; SQL matches by *position*. ## The SQL rules In SQL, `SELECT … UNION SELECT …` requires: - the same number of columns in each operand; - column types in corresponding positions that are compatible or coercible to a common type; - the result column names come from the **first** operand — the second operand's names are ignored entirely. SQL is therefore *positional*. This is the single most common practical bug with set operators: ``` SELECT city, country FROM supplier UNION SELECT country, city FROM customer ``` Both sides are two text columns, so this executes without complaint and produces a result set where cities and countries are jumbled. No engine can catch it, because the types line up. The defensive habit is to write explicit column lists in a fixed order on every branch, never `SELECT *`, and to eyeball the ordering when reviewing set operations. Also note that `SELECT *` on either side makes the query fragile in a different way: adding a column to one table changes that branch's degree and breaks the query — or worse, if both tables gain a column, changes the pairing. ## What happens when compatibility fails It is a **static, compile-time error**, not a runtime surprise: the engine reports something like "each UNION query must have the same number of columns" or a type-mismatch on a specific column position. That is the desirable behaviour — the expression has no meaning, so it is rejected before any data is touched. ## Which operators need it All three of the classical set operators — union, intersection, difference — require union compatibility, and all three preserve the heading in the result. Note that difference is *not* symmetric in its meaning (`R − S ≠ S − R`) but it is symmetric in its *requirement*: both operands must still have the same shape. Cartesian product and join are the operators that do **not** require it; they combine headings instead. ## Nulls and distinctness under set operators One semantic detail that trips people once they move to SQL: matching for the set operators is based on *distinctness*, not on the `=` comparison. Two rows that both hold a null in the same column count as duplicates for `UNION`, and a row with a null is removed by `EXCEPT` when the other side has a matching row with a null in the same position — even though `null = null` does not evaluate to true in a `WHERE` predicate. This is deliberate: the set operators need an equivalence relation on tuples, and "is not distinct from" is that relation. ## The compact summary Set operators are the algebra's way of combining relations that are already *the same shape*: same degree, same corresponding types (and, strictly, the same attribute names — fix mismatches with rename). Joins combine relations of *different* shapes. Get that dividing line right and the compatibility rule stops feeling like an arbitrary restriction and starts looking like the only definition that could work.
- Why do join and Cartesian product not require union compatibility?Because they construct a new heading rather than reusing one. A product pairs every tuple of R with every tuple of S and concatenates their attributes, so the result's degree is the sum of the inputs' degrees. Set operators, by contrast, put tuples from both inputs into a single body, which only makes sense if those tuples already share a heading.
- How do you union two relations whose attributes mean the same thing but are named differently?Apply the rename operator ρ to one side so the headings match, then take the union — for example `R ∪ ρ_{customer_id → id}(S)`. In SQL you achieve the same with column aliases in the branch's select list, though SQL would have accepted the union anyway since it matches positionally and takes result names from the first branch.
- If both branches of a union return a text column and an integer column but in opposite order, what happens?The engine rejects it with a type-mismatch error on the offending column position, because SQL pairs columns positionally and text does not coerce to integer. That error is a lucky outcome: when the swapped columns happen to share a type, the same mistake compiles cleanly and silently produces mixed-up data.
Union is merging two decks of cards that use the same card layout; a join is pasting a card from one deck onto a card from another. You can only merge decks whose cards have the same fields in the same slots.
saying these in an interview costs you the question
- Thinking column names are what SQL matches on, rather than position.
- Believing a degree or type mismatch fails at runtime instead of being rejected up front.
- Claiming joins also require union compatibility.
- Assuming a same-type column swap between branches will be caught by the engine.
- Saying difference has looser shape requirements than union because it is asymmetric.