Same number of columns for Union Operation

sql sql-server-2008-r2 union union-all
Sparky picture Sparky · Jan 31, 2013 · Viewed 19.9k times · Source

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

mrunion picture mrunion · Jan 31, 2013

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

Related questions

Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More
CREATE TABLE IF NOT EXISTS equivalent in SQL Server

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Read More