SQL - JOIN using UNION ?? UNION using JOIN ?? with

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

SQL - JOIN using UNION ?? UNION using JOIN ?? with

Author

Topic

user811568
Starting Member

1 Post

Posted - 2011-07-05 : 19:20:06

I was asked this question during one of my interviews. Can you do JOIN using UNION keyword? Can you do UNION using JOIN keyword?

That is - 1. I should get same output as JOIN without using JOIN keyword, but using UNION Keyword? 2. I should get same output as UNION without using UNION keyword, but using JOIN Keyword?

Can you give me an example of how to do this if possible?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-05 : 20:15:51

There may be contrived examples where one could get the same result set from a UNION as one can from a JOIN, but I don't know.

UNION and JOIN serve completely different purposes - one unions two result sets, the other joins two (or more) tables allowing you to, for example retrieve columns from both tables that match the join conditions.

EZHILARASAN
Starting Member

2 Posts

Posted - 2011-07-06 : 08:55:28

select A.* from (
select
client.CLIENTID,
campaign.CAMPAIGNID,
banner.BANNERID,
affiliate.AFFILIATEID,
zone.ZONEID,

ifnull(client.clientname,'') ADVERTISER,
ifnull(campaign.campaignname,'') CAMPAIGN,
ifnull(banner.description,'') BANNER,
ifnull(affiliate.name,'') PUBLISHER,
ifnull(zone.zonename,'') ZONE,
sum(ifnull(ad_hourly.clicks,0)) CLICKS,
sum(ifnull(ad_hourly.impressions,0)) IMPRESSION,
sum(ifnull(ad_hourly.requests,0)) CTR,
sum(ifnull(ad_hourly.conversions,0)) CONVERSION
from
ox_clients client
join ox_campaigns campaign on client.clientid = campaign.clientid
join ox_banners banner on campaign.campaignid = banner.campaignid
join ox_ad_zone_assoc zone_assoc on banner.bannerid = zone_assoc.ad_id
join ox_affiliates affiliate on affiliate.affiliateid = zone_assoc.link_type
join ox_zones zone on zone.zoneid = zone_assoc.zone_id and zone.affiliateid = affiliate.affiliateid
left outer join ox_data_summary_ad_hourly ad_hourly on zone.zoneid = ad_hourly.zone_id and zone_assoc.ad_id = ad_hourly.ad_id
where campaign.weight > 0 AND date_time like '2010-06-25%'
group by
client.clientname,
campaign.campaignname,
banner.description,
affiliate.name,
zone.zonename
with rollup ) A
where A.ADVERTISER <> ''
order by A.ADVERTISER,
A.CAMPAIGN,
A.BANNER,
A.PUBLISHER,
A.ZONE

EZHILARASAN
Starting Member

2 Posts

Posted - 2011-07-06 : 08:56:49

CAN ANY ONE SAY HOW TO SUM THE VALUE BY DIFFERENT FIELD

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-06 : 09:15:10

Were you given a specific example?
A join extends the width of the resultset and a union extends the cardinality.
A general replacement of joins with union or vice-versa isn't possible.

Maybe that's what you were meant to say?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-06 : 09:20:37

Ezhilarasan-

Do not hijack someone else's thread, post a new thread for your questions.

Asif5566
Starting Member

9 Posts

Posted - 2011-07-06 : 09:49:43

quote:
Originally posted by EZHILARASAN

CAN ANY ONE SAY HOW TO SUM THE VALUE BY DIFFERENT FIELD

Hello, If I understand u correctly,

You can do it using:
'WITH CUBE' statement after Group By.

Also, u can Group By (showing only Summed up values) using another clause called GROUPING SETS.

I had experimented with these two a while back and seemed quite useful.
By googling u can have good tutorials on these.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources