Union All Vs Left Outer 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

General SQL Server Forums

New to SQL Server Programming

Union All Vs Left Outer Join

Author

Topic

ruchijani
Starting Member

23 Posts

Posted - 2009-08-03 : 04:26:08

Hello to all,

I have tables like

TABLE1

PrimaryID
RequestNo
Id

TABLE2
Id
Name

TABLE3
Id
Name

TABLE4
Id
Name

Now Id of TABLE1 reference to Id of TABLE2 or Id of TABLE3 or Id of TABLE4 depend upon RequestNo
If RequestNo = 1 then reference to Id of TABLE2
If RequestNo = 2 then reference to Id of TABLE3
If RequestNo = 3 then reference to Id of TABLE4

Which query gives me faster output for more data from below

LEFT OUTER JOIN OR UNION ALL

SELECT TABLE1.*,COALESCE(TABLE2.Name,TABLE3.Name,TABLE4.Name) AS Name FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE2.Id =
TABLE1.Id LEFT OUTER JOIN TABLE3 ON TABLE3.Id = TABLE1.Id
LEFT OUTER JOIN TABLE4 ON TABLE4.Id = TABLE1.Id

OR

SELECT TABLE1.*,TABLE2.Name FROM TABLE1 INNER JOIN TABLE2 ON
TABLE1.Id = TABLE2.Id WHERE RequestNo = 1

UNION ALL

SELECT TABLE1.*,TABLE3.Name FROM TABLE1 INNER JOIN TABLE3 ON
TABLE1.Id = TABLE3.Id WHERE RequestNo = 2

UNION ALL

SELECT TABLE1.*,TABLE4.Name FROM TABLE1 INNER JOIN TABLE4 ON
TABLE1.Id = TABLE4.Id WHERE RequestNo = 3

Thanks
Ruchi

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-03 : 07:56:30

My guess would be that it would be faster to normalize your data.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

ruchijani
Starting Member

23 Posts

Posted - 2009-08-03 : 08:34:57

Hiii i m new to sql server so can u please tell me how can I normlize this tables?

Thanks
Ruchi

johnconstraint
Starting Member

23 Posts

Posted - 2009-08-03 : 08:44:54

You can follow the link for normalization basics
http://databases.about.com/od/specificproducts/a/normalization.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources