union and join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

union and join

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2008-05-05 : 07:06:16

what is the difference between union and join ?
PLEASE EXPLAIN WITH EXAMPLE

Regards
Kannan.D

Desikankannan

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-05 : 07:22:34

In simple terms UNION means merging two subsets of data of same type, while JOIN denotes deriving data from two subsets based on some common key column (although CROSS JOIN does not require this).

You can fine plenty of examples for this in SQL Server help files or even here.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 07:55:40

union is usually used for combining two query results.The result will have both the rows from right hand query and left query. Thus if left query returns m rows and right query returns n rows,then union all returns m+n rows.
join as name suggests join two query parts based on matching condition (common field(s)) to return the columns from both the queries as a single resultset. Thus if we have a left query having m rows and right query having n rows with r rows matching value for a field then result will contain r rows.

ex: Consider two table Employee and Dept

Employee
ID Name Dept
101 Mark 1
105 John 3
107 Kevin 1
108 Mary NULL

Department
ID Name
1 Maths
2 Physics
3 Chemistry

Now if we take

select ID,Name FROM Employee
UNION
select ID,Name FROM Department

we get,

ID Name
101 Mark
105 John
107 Kevin
108 Mary
1 Maths
2 Physics
3 Chemistry

Thus we see it simply gives us combined result of two queries. Also note that the number of returned fields should be same on both sides of union (ID,Name).

Now if we take

select e.Name AS Emp,d.Name As Dept
FROM Employee e
INNER JOIN Department d
ON e.ID=e.Dept

we get
Emp Dept
Mark Maths
John Chemistry
Kevin Maths

If we see here it just returned fields from left and right query after matching corresponding records on condition (e.Dept=d.ID). Also note that Mary was not returned as it didnt had a matching value in right side.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources