Same number of columns for Union Operation

Question

I was going through union and union all logic and trying examples. What has puzzled me is why is it necessary to have same number of columns in both the tables to perform a union or union all operation?

Forgive me if my question's silly, but i couldn't get the exact answer anywhere. And conceptually thinking, as long as one common column is present, merging two tables should be easy right?(like a join operation). But this is not the case, and I want to know why?

Answer 1

JOIN operations do NOT require the same number of columns be selected in both tables. UNION operations are different that joins. Think of it as two separate lists of data that can be "pasted" together in one big block. You can't have columns that don't match.

Another way to look at it is a JOIN does this:

TableA.Col1, TableA.Col2 .... TableB.Col1, TableB.Col2

A UNION does this:

TableA.Col1, TableA.Col2
TableB.Col1, TableB.Col2

JOINS add columns to rows, UNIONS adds more rows to existing rows. That's why they must "match".

Matt

Answer 2

Lets say you have EMPTable with columns and values as below: id, name, address, salary, DOB 1, 'SAM', '2 Merck Ln', 100000, '08/18/1980'

IF you want to UNION with only the column name (lets say value is 'TOBY'), it means you have to default other values to NULL (a smart software or db can implicitly do it for you), which in essence translates to below (to prevent the integrity of a relational table) ->

1, 'SAM', '2 Merck Ln', 100000, '08/18/1980'
UNION
NULL,'TOBY', NULL, NULL, NULL

A "union" by definition is a merger of (different) values of THE same class or type.