October 15, 2014 at 3:12 am
Hi All,
I am having below two tables:
1) TableA : Which contains 5 columns(Column1,..........Column5)
2)TableB : Which contains 10 columns(Column1,..........Column10)
TableB contains millions of data.
Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.
I am doing as below:
select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )
But query is taking almost 5 minutes. Is there is another approach? PLease suggest.
Thanks,
Abhas.
October 15, 2014 at 3:40 am
Abhas
That query isn't syntactically correct. Please will you post the actual query you're running, along with table DDL (in the form of CREATE TABLE statements) and sample data (in the form of INSERT statements).
John
October 15, 2014 at 3:42 am
As per my understanding which could be wrong, your actual query will not bring the correct records. if you want to exclude the records, then you should follow LEFT Join / NOT EXISTS approach.
Following is the example:
declare @table1 table ( column1 int, column2 int, column3 int)
declare @table2 table ( column1 int, column2 int, column3 int)
insert into @table1
select 1, 2, 3 union all
select 4, 5, 6 union all
select 7, 8, 9
insert into @table2
select 1, 2, 3 union all
select 4, 5, 6
--------------- Original Query (Wrong Results)
select a.*
from @table1 a
left join @table2 b on a.column1!=b.column1
and a.column2!=b.column2
and a.column3!=b.column3
--------------- LEFT JOIN Query (Corret Result)
select a.*
from @table1 a
left join @table2 b on a.column1=b.column1
and a.column2=b.column2
and a.column3=b.column3
where b.column1 is null
--- below are not required should be ignored.
--and b.column2 is null
--and b.column3 is null
--------------- NOT EXISTS Query (Corret Result)
select * from @table1 a
where not exists (select 1
from @table2 b
where a.column1=b.column1
and a.column2=b.column2
and a.column3=b.column3)
hope it helps
EDITED
Adjusted the WHERE Clause in LEFT JOIN Query.
One more thing you need to test the performance of LEFT JOIN / NOT EXISTS for deploying it into the production.
October 15, 2014 at 4:26 am
Hi,
Thanks Twin.
Thank you very much for your help with perfect solution. I really appreciated.
Thank you once again
Thanks,
Abhas.
October 15, 2014 at 4:44 am
glad to help, just edited the post for review.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply