SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 subquery vs join results are different
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 09/13/2013 :  20:59:13  Show Profile  Reply with Quote
I have 2 tables(label and product) both has the column called uniqueCode which is nvarchar(255) datatype and nullable column.

product table has 30,000 records and label table has 5000 records. I would like to get the uniqueCode from label which doesn't exist in the product table upc column. I tried with not in but returns null data but i have 3000 unmatched data that needs to be displayed. Then i tried with join and it is taking 10+ minutes to provide the result.


 select * from label where not in(select uniqueCode from Product where uniqueCode is not null) and uniqueCode is not null


The above query returns null though it has records to show up



    SELECT pp.*
  FROM label pp
       LEFT JOIN Product p ON pp.uniqueCode  <> p.uniqueCode       
 WHERE 
   p.uniqueCode  is not  NULL



[Note : both the columns are non indexed column]

Am i missing anything in this query ? why it is taking too much time to execute. Any suggestions....

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
164 Posts

Posted - 09/14/2013 :  00:40:59  Show Profile  Reply with Quote
select * from label
where label.uniqueCode not in(select uniqueCode from Product where uniqueCode is not null)
and uniqueCode is not null

veeranjaneyulu

Edited by - VeeranjaneyuluAnnapureddy on 09/14/2013 01:09:38
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 09/14/2013 :  09:08:52  Show Profile  Reply with Quote
I already tried that but i am getting any value. please refer my first query, i missed to include label. unicode before not in.

any other suggestions...
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 09/14/2013 :  09:25:53  Show Profile  Reply with Quote
i am done with my requirement using below three concepts.

1. using Not In
2. Using Left Join
3.Using Not Exists.

Which one is the best choice to achieve this ? suggestions please... but all are taking 4 seconds to produce the result.

as i said in my earlier post label table has 5000 records and product table has 30000 records. Is it fine to take 4 seconds to search and produce the result?

Edited by - sqllover on 09/14/2013 09:29:35
Go to Top of Page

Ifor
Aged Yak Warrior

574 Posts

Posted - 09/16/2013 :  08:00:43  Show Profile  Reply with Quote

SELECT *
FROM label L
WHERE NOT EXISTS
(
	SELECT 1
	FROM Product P
	WHERE P.uniqueCode = l.uniqueCode
);

-- or 

SELECT L.*
FROM label L
	LEFT JOIN Product P
		ON L.uniqueCode = P.uniqueCode
WHERE P.uniqueCode IS NULL;

-- or

SELECT *
FROM label
WHERE NOT uniqueCode IN
(
	SELECT uniqueCode
	FROM Product
);

-- or

SELECT uniqueCode
FROM label
EXCEPT
SELECT uniqueCode
FROM Product;

-- etc

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 09/16/2013 :  08:48:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 09/16/2013 :  09:52:01  Show Profile  Reply with Quote
Thank you peso.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000