question

askmlx121 avatar image
askmlx121 asked

Alter Way of Leftouter join method?

Can anyone give me the method of alter way of left outer join Because when we create a indexed view we could not able create index on theat view due to statement contains FULL JOIN or LEFT JOIN or RIGHT JOIN. So i searched the web fully it gives suggestion using UNION Instead of left join,I mentioned query below. So could you give me any other method is available to replace LEFT JOIN with that other than UNION? Is it full to reduce optimization cost when we use UNION instead of LEFT JOIN? Quick suggestions are highly appreciated.............buddys......... ----------------------My original table using left join method----------------- SELECT tablea.*, CASE WHEN tableb.tablebcolumn1 IS NULL THEN 0 ELSE 1 END IsHosted FROM tablea LEFT JOIN tableb ON tablea.column=tableb.column -----------Example of alter way of using UNIONALL instead of leftouterjoin ------- SELECT tablea.*, CASE WHEN tableb.tablebcolumn1 IS NULL THEN 0 ELSE 1 END IsHosted FROM tablea where tablea.column IN (select tableb.column from tableb) UNION ALL SELECT tablea.*, CASE WHEN tableb.tablebcolumn1 IS NULL THEN 0 ELSE 1 END IsHosted FROM tablea where tablea.column NOT IN (select tableb.column from tableb) -------------------------------------------------------------------------------------- Note: Due to security reason i did not give original table name. i gave table name of tablea and tableb.........as my originaltable name
t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
You cannot Index a view which has Left Join, UNION, Derived table, self join , DISTINCT etc. For full list of restriction check [here][1]. [1]: http://msdn.microsoft.com/en-us/library/ms191432.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

durak2012 avatar image
durak2012 answered
create table #ta (tida int, col1a varchar(10)); create table #tb (tidb int, col1b varchar(10)); insert into #ta (tida, col1a) values(1,'a1'); insert into #ta (tida, col1a) values(2,'a2'); insert into #ta (tida, col1a) values(3,'a3'); insert into #tb (tidb, col1b) values(1,'b1'); insert into #tb (tidb, col1b) values(2,'b2'); /* how data looks with left join */ select a.tida,a.col1a,b.col1b from #ta a left join #tb b on a.tida = b.tidb; alter table #ta add col1b varchar(10); /* replace left join with update */ update a set col1b = b.col1b from #ta a join #tb b on a.tida = b.tidb; /* the same output as left join but from one table */ select tida,col1a,col1b from #ta; drop table #ta; drop table #tb
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.