Union Compatibility

Every set operation in relational algebra—union, intersection, and difference—requires a critical precondition: union compatibility. Without it, these operations are undefined, because the resulting relation would have no coherent schema.

Union compatibility ensures that when we combine tuples from two relations, the result forms a valid relation with a well-defined structure. It is the gatekeeper that preserves the integrity of the relational model.

This page provides exhaustive coverage of union compatibility: the formal requirements, how database systems evaluate compatibility, common mistakes, and practical techniques for making incompatible relations compatible through projection and renaming.

Union compatibility (also called type compatibility or schema compatibility) is a relationship between two relations that permits set operations. It is a necessary condition—without it, union, intersection, and difference are undefined.

Formal Definition:

Two relations R(A₁, A₂, ..., Aₙ) and S(B₁, B₂, ..., Bₘ) are union-compatible if and only if:

1. Same Degree (Arity): n = m

   • Both relations must have the same number of attributes

2. Domain Compatibility: For each i from 1 to n:

   • dom(Aᵢ) is compatible with dom(Bᵢ)
   • Corresponding attributes must have compatible data types

Notation:

We write R ≈ S to denote that R and S are union-compatible.

If R ≈ S, then:

• R ∪ S is defined
• R ∩ S is defined
• R − S is defined
• S − R is defined

What Union Compatibility Does NOT Require:

• Same attribute names: Names can differ between relations
• Identical domains: Compatible domains (not identical) are sufficient
• Same cardinality: Relations can have different numbers of tuples
• Same constraints: Constraints are not compared for compatibility

Result Schema:

When R ∪ S (or R ∩ S, R − S) is computed, the result schema is typically determined by:

1. Attribute names from the first operand (R)
2. Domains resolved to accommodate both operands (widening)
3. Constraints are NOT automatically inherited

Domain compatibility is more nuanced than simple type equality. Database systems typically allow compatible types that can be meaningfully compared, even if not identical.

Compatible Type Categories:

Domains are generally compatible if they belong to the same type family and values can be compared without loss of information:

Domain Resolution (Widening):

When compatible domains differ, the result domain is typically the "wider" type that can accommodate values from both:

INT + BIGINT → BIGINT (larger integer type)
VARCHAR(50) + VARCHAR(100) → VARCHAR(100) (larger capacity)
FLOAT + DOUBLE → DOUBLE (higher precision)
DECIMAL(10,2) + DECIMAL(8,4) → DECIMAL(12,4) (covers both)

Implicit Conversion:

Some databases perform implicit conversion for set operations:

-- This might work (integer to string conversion)
SELECT id, name FROM Table1  -- id is INT
UNION
SELECT product_code, name FROM Table2  -- product_code is VARCHAR

However, relying on implicit conversion is risky and not recommended. Explicit casting ensures clarity and portability.

A critical distinction that many database learners overlook is the difference between structural compatibility (what the database checks) and semantic compatibility (what makes business sense).

Structural Compatibility:

• Same number of columns
• Compatible data types for corresponding columns
• Verified automatically by the database engine
• If unsatisfied, results in a compile-time or execution error

Semantic Compatibility:

• Columns represent the same real-world concept
• Values are comparable in meaning, not just type
• NOT verified by the database (human responsibility)
• If violated, results in meaningless but syntactically valid results

Examples of Semantic Mismatches:

-- Structurally compatible, semantically WRONG:

-- Example 1: Different entities
SELECT id, name FROM Customers
UNION
SELECT id, title FROM Products;  -- Meaningless mix!

-- Example 2: Different units
SELECT product_id, price_usd FROM US_Store
UNION
SELECT product_id, price_eur FROM EU_Store;  -- Currency mismatch!

-- Example 3: Different time zones
SELECT event_id, timestamp_utc FROM US_Events
UNION
SELECT event_id, timestamp_local FROM EU_Events;  -- Time zone chaos!

-- Example 4: Different scales
SELECT account_id, balance_cents FROM SystemA
UNION
SELECT account_id, balance_dollars FROM SystemB;  -- 100x error!

In each case, the query executes successfully but produces nonsensical or dangerous results.

When relations are not naturally union-compatible, we can often achieve compatibility through the strategic use of projection (π) and renaming (ρ). These operations allow us to reshape relations for set operations.

Strategy 1: Projection to Shared Schema

When relations have different degrees, project both to a common subset of compatible attributes:

-- Original incompatible relations:
Customers(cust_id, name, email, phone, address)
Suppliers(supp_id, company_name, contact_email)

-- Project to common compatible schema:
π_{cust_id, name}(Customers) ∪ π_{supp_id, company_name}(Suppliers)

-- Now both have degree 2 with compatible domains

Strategy 2: Renaming for Clarity

While attribute names don't affect compatibility, renaming improves clarity when combining relations:

-- Rename for unified result schema:
ρ_{id, name, email}(π_{cust_id, name, email}(Customers))
∪
ρ_{id, name, email}(π_{supp_id, company_name, contact_email}(Suppliers))

-- Result has clean, unified attribute names: (id, name, email)

Strategy 3: Adding Constant Columns

When one relation is missing an attribute present in another, use constants:

-- SQL: Add source indicator
SELECT cust_id, name, email, 'Customer' AS source_type
FROM Customers
UNION
SELECT supp_id, company_name, contact_email, 'Supplier' AS source_type
FROM Suppliers;

Understanding common union compatibility errors helps you diagnose issues quickly and write correct queries from the start. Here are the most frequent problems and their solutions:

Different database systems have varying levels of strictness and behavior regarding union compatibility. Understanding these differences is essential for writing portable SQL and diagnosing cross-platform issues.

PostgreSQL (Strictest):

• Requires exact type matches or explicit compatible types
• Won't implicitly convert INTEGER to VARCHAR
• Clear error messages for type mismatches

-- PostgreSQL: This fails
SELECT 1 UNION SELECT 'a';  -- ERROR: types integer and text cannot be matched

-- Fix requires explicit cast
SELECT CAST(1 AS TEXT) UNION SELECT 'a';

MySQL (Most Lenient):

• Extensive implicit type conversion
• May convert between very different types
• Can produce unexpected results

-- MySQL: This works (but might not be what you want)
SELECT 1 UNION SELECT 'abc';  -- Returns 1 and 'abc' as strings

SQLite (Dynamic Typing):

• Type affinity rather than strict types
• Almost any combination works
• Least predictable for complex types

-- SQLite: Very permissive
SELECT 123 UNION SELECT 'hello' UNION SELECT 45.67;

Beyond basic projection and casting, several advanced patterns help achieve compatibility in complex scenarios.

Pattern 1: Synthetic Key Generation

When combining relations with incompatible keys, generate a synthetic unified key:

-- Create unified key with source prefix
SELECT CONCAT('CUS_', CAST(customer_id AS VARCHAR(20))) AS unified_id,
       name, email, 'Customer' AS source
FROM Customers
UNION
SELECT CONCAT('SUP_', CAST(supplier_id AS VARCHAR(20))) AS unified_id,
       company_name, contact_email, 'Supplier' AS source
FROM Suppliers;

Pattern 2: Polymorphic Schema with NULLs

Create a "supertype" schema that accommodates attributes from all subtypes:

-- Union of different entity types with type-specific columns
SELECT 'Employee' AS entity_type,
       emp_id AS id,
       name,
       salary,
       NULL AS hourly_rate,
       NULL AS project_code
FROM Employees
UNION ALL
SELECT 'Contractor' AS entity_type,
       contractor_id,
       name,
       NULL,
       hourly_rate,
       project_code
FROM Contractors;

Pattern 3: Temporal Schema Alignment

When unioning data from different time periods with schema evolution:

-- Old schema: (id, name, address)
-- New schema: (id, first_name, last_name, street, city, zip)

SELECT id, 
       name AS full_name,
       address AS full_address,
       NULL AS street,
       NULL AS city,
       NULL AS zip,
       'legacy' AS schema_version
FROM Customers_Archive
UNION ALL
SELECT id,
       CONCAT(first_name, ' ', last_name),
       CONCAT(street, ', ', city, ' ', zip),
       street,
       city,
       zip,
       'current' AS schema_version
FROM Customers_Current;

Pattern 4: JSON/XML Expansion

For semi-structured data, expand to compatible relations:

-- Expand JSON customer field to match relational schema
SELECT customer_data->>'id' AS id,
       customer_data->>'name' AS name,
       customer_data->>'email' AS email
FROM JSON_Customers
UNION
SELECT CAST(id AS TEXT), name, email
FROM Relational_Customers;