subquery vs join results are different

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

subquery vs join results are different

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-13 : 20:59:13

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

169 Posts

Posted - 2013-09-14 : 00:40:59

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

veeranjaneyulu

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-14 : 09:08:52

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...

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-14 : 09:25:53

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?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-09-16 : 08:00:43

[code]
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
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-16 : 08:48:00

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-09-16 : 09:52:01

Thank you peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources