| Author |
 Topic  |
|
|
user811568
Starting Member
USA
1 Posts |
 Posted - 07/05/2011 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/05/2011 : 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 - 07/06/2011 : 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 - 07/06/2011 : 08:56:49
|
| CAN ANY ONE SAY HOW TO SUM THE VALUE BY DIFFERENT FIELD |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/06/2011 : 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
USA
15559 Posts |
Posted - 07/06/2011 : 09:20:37
|
Ezhilarasan-
Do not hijack someone else's thread, post a new thread for your questions. |
|
|
|
Asif5566
Starting Member
9 Posts |
Posted - 07/06/2011 : 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.
|
|
|
| |
Topic |
|
|
|
SQL - JOIN using UNION ?? UNION using JOIN ?? with
