Subquery on similar field to get difference

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 2000 Forums

SQL Server Development (2000)

Subquery on similar field to get difference

Author

Topic

j_j_j
Starting Member

2 Posts

Posted - 2006-02-07 : 14:15:33

Hi, everybody!
I'm trying to find new entries in table STORE_PRODUCT
for specified store
that not exist yet in catalogue PRODUCTS.

select prd_model, prd_name from store_product
where store_product.store_num = 105
and prd_model not in
(select prd_model from products)

... Nothing. But if I want to check on known
models I put additional filters and now I can see those records.

select prd_model, prd_name from
store_product
where store_product.store_num = 105
and prd_model like 'SE210%'
and prd_model not in
(select prd_model from products where product_model like 'SE210%')

What's a problem? May be resulting subquery set is toobig to handle?
Is there another way to do this?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-07 : 14:31:31

If prd_model in table products is nullable, you should change the query like this to exclude null products.prd_model. Also, it is good practice to always add an alias on each table, and use it on each column to make sure the subquery does not use the wrong column.


select
	a.prd_model,
	a.prd_name
from
	store_product a
where
	a.store_num = 105	and
	a.prd_model not in
	(
	select
		b.prd_model 
	from
		products b
	where
		b.prd_model is not null
	)

CODO ERGO SUM

j_j_j
Starting Member

2 Posts

Posted - 2006-02-07 : 15:36:33

Thanks, it works.
Is this way (subquery) good for this case?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-07 : 16:03:09

I'd use LEFT OUTER JOIN instead.

SELECT p.prd_model, p.prd_name
FROM Store_Product ps
LEFT OUTER JOIN Products p
ON ps.JoinColumnGoesHere= p.JoinColumnGoesHere
WHERE p.prd_model IS NULL

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-07 : 18:01:02

quote:
Originally posted by j_j_j

Thanks, it works.
Is this way (subquery) good for this case?

You have to be careful when you are using a NOT IN with a subquery to make sure that the subquery can never return a null value, because it will fail the test, even if the value you are comparing is not in the subquery result.

This code shows what happens. Notice that the second query returns no rows, even though 1 is not in the subquery result set.


-- Without NULL in subquery result
select
	y = 1
where
	1 not in
	(
	select 2
	union all
	select 3
	)


-- With NULL in subquery result
select
	x = 1
where
	1 not in
	(
	select 2
	union all
	select 3
	union all
	select null
	)

Results:


y           
----------- 
1

(1 row(s) affected)

x           
----------- 

(0 row(s) affected)

CODO ERGO SUM

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-08 : 04:35:52

Better use not exists(...) (more readable) or left outer join (may be more efficient).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources