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?

Join and Union all are two different concepts, Union all merge two result sets in ONE where as Join combines your columns in one result set. — Vishwanath Dalvi, Jan 31 '13 at 04:57

score 16 · Accepted Answer · edited Nov 21 '22 at 06:14

16

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".

edited Nov 21 '22 at 06:14

ggorlen

44,755
7
76
106

answered Jan 31 '13 at 05:02

Matt Runion

1,031
7
13

Matt(mrunion),sgeddes and Vidhwanathan Iyyer. All 3 of you have the right answer. I could mark only one so i used FIFO :D. Thanks a lot guys for your wonderful answers. You Rock!! – Sparky Jan 31 '13 at 09:25
See that would make sense, if you were using entire tables. I was able to get a union between 3 different tables with different number of columns when I was using a union on the selects of one column for each table. But when i tried to use said union(the original union by itself did return rows) as a select * from "table" where "ID" not in (union) it called there error. Can you explain why this would happen? – eragon2262 Nov 10 '16 at 18:27

score 4 · Answer 2 · answered Jan 31 '13 at 05:02

Join Operation combines columns from two tables.

Where as Union and Union all combines two results sets, so In order to combine two results you need to have same number of columns with compatible data types.

In real world e.g. In order to play a game of cricket you need 11 players in both team, 7 in one team and 11 in opposite team not allowed.

score 2 · Answer 3 · answered Sep 24 '14 at 18:57

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.

score 1 · Answer 4 · answered Jan 31 '13 at 05:02

You need to research the difference between UNION and JOIN.

Basically, you use UNION when you want to get records from one source (table, view, group of tables, etc) and combine those results with records from another source. They have to have the same columns to get a common set of results.

You use JOIN when you want to join records from one source with another source. There are several types of JOINs (INNER, LEFT, RIGHT, etc) depending on your needs. When using JOINs, you can specify whichever columns you'd like.

Good luck.

score 0 · Answer 5 · answered Jan 31 '13 at 05:14

join are basically used when we want to get data from two or more tables, and for this there is no need to fetch same number of columns, consider a situation where by using normalization concepts we have divided the table into two parts,

emp [eid, ename,edob,esal] emp_info [eid,emob_no]

here i we want to know name and mobile no. of all employees then we will use the concept of join, because here information needed by us can't be provided by same table.

so we will use..

SELECT E.ENAME,EI.EMOB_NO FROM EMP E, EMP_INFO EI WHERE E.EID = EI.EID AND LOWER(E.ENAME)='john' ;

now consider about situation where we want to find employees those are having saving account and loan account in a bank.. here we want to find common tuples from two tables results. for this we will use set operations. [ intersection ]

for set operations 2 conditions MUST BE satisfied.

same no. of attributes must be fetches from each table.
domain of each attribute must be same of compatible to the higher one...

Same number of columns for Union Operation

5 Answers5