CODEWITHSUNDEEP
sqlsql-server
Happy
Happy

Reputation: 1145

how to use NOT EXISTS in sql server

i am trying to find one column value is present in another table column value.

e.g

Product_Name(tb_new_purchase)
1.car
2.bus
3.truck 


Product_Name(tb_new_product_Name_id)
1.car

i need to select bus and truck

Here is my code

SELECT Product_Name
FROM tb_new_purchase
WHERE  NOT EXISTS (SELECT Product_Name FROM tb_new_product_Name_id )

But its not returning any values.where i made error? thanks....

Upvotes: 0

Views: 329

Answers (6)

Dipendu Paul
Dipendu Paul

Reputation: 2753

You can use EXCEPT to achieve what you need like this:

SELECT  Product_Name
FROM tb_new_purchase
EXCEPT
SELECT Product_Name 
FROM tb_new_product_Name_id

Upvotes: 0

Twelfth
Twelfth

Reputation: 7180

I know there are already 5 answers here....but just to throw this out here, you can do this as a join and not a where exists. Subqueries can really hinder performance and runtime. In MSSQL, the runtimes should be about the same...is MySQL, this join syntax works far better. 

Select a.product_name
from tb_new_purchase a
left join tb_new_product_Name_id b on a.product_name = b.product_name
where b.product_name is null

Using a left join here will produce a null in the b.product_name column whenever the product_name is not in the b table. Where product_name is null operates as a filter all lines that have an entry in the 'b' table here.

Upvotes: 0

borjab
borjab

Reputation: 11655

You need to add a condition to restrict the subquery. If you execute the query

SELECT Product_Name FROM tb_new_product_Name_id

It always returns some record so NOT EXISTS is false. Try something like

SELECT Product_Name
FROM tb_new_purchase
WHERE  NOT EXISTS (SELECT Product_Name 
                     FROM tb_new_product_Name_id 
                    WHERE tb_new_purchase.id = tb_new_product_Name_id.id)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your query is checking all the product names in tb_new_product_Name_id. If the table is not empty, then it passes the where clause. I think you want a correlated subquery:

SELECT np.Product_Name
FROM tb_new_purchase np
WHERE  NOT EXISTS (SELECT 1
                   FROM tb_new_product_Name_id npni
                   WHERE np.Product_Name = npni.Product_Name
                  );

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41549

Either use NOT IN or pass the field into the NOT EXISTS

SELECT Product_Name
FROM tb_new_purchase
WHERE Product_Name NOT IN(SELECT Product_Name FROM tb_new_product_Name_id)

or

SELECT Product_Name
FROM tb_new_purchase
WHERE NOT EXISTS (SELECT Product_Name FROM tb_new_product_Name_id
                  WHERE tb_new_product_Name_id = tb_new_purchase.Product_Name  )

Upvotes: 1

Delphi.Boy
Delphi.Boy

Reputation: 1216

This is what you need:

SELECT Product_Name FROM tb_new_purchase WHERE Product_Name NOT IN(SELECT Product_Name FROM tb_new_product_Name_id)

Upvotes: 0

Related Questions