October 15, 2016 at 3:17 am
hi.
all first cols are pk of the table.
either #i or #j will have entery for #a table's record. like aid 1,2 is in #i and aid 3 is in #j.
Create table #A(Aid Bigint,des varchar(10))
insert into #a(Aid,des) values(1,'a')
insert into #a(Aid,des) values(2,'a1')
insert into #a(Aid,des) values(3,'a3')
Create table #i(iid bigint, Aid Bigint,spoint decimal(9,2),epoint decimal(9,2))
insert into #i(iid,Aid,spoint,epoint) values(1,1,2,3)
insert into #i(iid,Aid,spoint,epoint) values(2,2,4,5)
Create table #j(jid bigint, Aid Bigint,spoint decimal(9,2),epoint decimal(9,2))
insert into #j(jid,Aid,spoint,epoint) values(3,3,7,8)
select a.aid, isnull(i.spoint ,j.spoint)
from #a a
left join #i i on a.Aid = i.aid
left join #j j on a.aid=j.aid
i can write the above query like following so please tel me which one is correct and to be followed.
select a.aid,i.spoint
from #a a
join #i i on a.Aid = i.aid
union
select a.aid, j.spoint
from #a a
join #j j on a.aid=j.aid
yours sincerley
October 15, 2016 at 9:58 am
I would go with the second one. If you have indexed the PKs, then this can result in less scanning. In the first query, you must scan all of a, whether they have matches or not.
October 16, 2016 at 12:25 am
1)ok, that means both queries are correct any one can be used?
2) i have chainged the join a.aid=i.aid
select a.aid, isnull(i.spoint ,j.spoint)
from #a a
left join #i i on a.Aid = i.aid
left join #j j on a.aid=j.aid
i can write the above query like following so please tel me which one is correct and to be followed.
select a.aid,i.spoint
from #a a
join #i i on a.Aid = i.aid
union
select a.aid, j.spoint
from #a a
join #j j on a.aid=j.aid
the real situation was quite tipical, they where entering records in both tables #i and #j and
wanted to give priority to #i
because when they where entering record first in #j in that case they were entering a record in #i also
with spoint null.
so i had to put an left joint in second query to pick up only thoes records which has spoint null in #i
but have an entery in #j.
so in that case it will slow down when compared to first one.
yours sincerly
October 17, 2016 at 12:11 pm
rajemessage 14195 (10/16/2016)
1)the real situation was quite tipical, they where entering records in both tables #i and #j and
wanted to give priority to #i
because when they where entering record first in #j in that case they were entering a record in #i also
with spoint null.
so i had to put an left joint in second query to pick up only thoes records which has spoint null in #i
but have an entery in #j.
The query with the union does not give priority to #i. It treats #i and #j the same. Also, if the values in #i & #j are different (implied if prioritizing), the union will return multiple values for the record in #a.
First you need to focus on accuracy, then worry about performance. I would use the left join strategy because it follows the business rules as described. Also, I think performance would still be good as #a is only touched once along with each record in #i & #j.
However, assuming every record in #a has one and only one match in #i or #j, then you can use this to join #a to #i/#j with only a single scan of #a:
SELECT a.Aid, u.spoint
FROM #a a
INNER JOIN (SELECT aid, spoint FROM #i UNION ALL SELECT aid, spoint FROM #j) AS u
ON a.Aid = u.Aid
If an #a record can exist in both, but the values are guaranteed to be the same, you can use this:
SELECT a.Aid, u.spoint FROM #a a
INNER JOIN (SELECT aid, spoint FROM #i UNION SELECT aid, spoint FROM #j) AS u
ON a.Aid = u.Aid
The benefit over the initial union query is it checks for distinct values before the join to #a.
Wes
(A solid design is always preferable to a creative workaround)
October 17, 2016 at 1:07 pm
It might be worth trying code below, as it seems to limit the number of "j" lookups required. A nested-loop join didn't prevent the extra lookups -- for whatever reason -- so I forced a hash join.
select a.aid, i.*, isnull(i.spoint ,j.spoint) as spoint
from #a a
left join #i i on a.Aid = i.aid
left hash join #j j on (i.aid is null) and a.Aid = j.aid
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply