CODEWITHSUNDEEP
sqlt-sql
Ewald Bos
Ewald Bos

Reputation: 1770

T-SQL : check if data exists in table

I am using a view that checks if data exists in another table and if it exists if shows the number of row's connected to that table. Now I was wondering if there might be a faster way of doing this, since I am only interested if there is data and not necessary how many data rows there are. I guess when it does not need to count it will be faster.

This is what I use now:

SELECT
    dbo.user.id,
    dbo.user.userCode,
    COALESCE (TotalProducts.ProductsInback, 0) AS ProductsInback
FROM 
    dbo.user
LEFT OUTER JOIN
    (SELECT COUNT(id_product) AS ProductsInback, userCode
     FROM dbo.Product
     GROUP BY userCode) AS TotalProducts ON dbo.Product.userCode = TotalProducts.userCode
WHERE 
    dbo.user.userCode = 'XYZ'

Now this works all fine an it gives me the number of products connected to the user XYZ that are in the back of the store. However I just want to know if the user has products in the back of the store, I don't need to know how many. That seems to me a faster solution (walking anyway to the back of the store). So replacing the COUNT with ... ?

Upvotes: 0

Views: 131

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You are right, for a lookup whether data exists in another table, we use EXISTS or IN, because we don't have to go through all matching rows then, but can stop at the first one found.

EXISTS:

SELECT
  id,
  userCode,
  CASE WHEN EXISTS (SELECT * FROM dbo.Product p WHERE p.userCode = u.userCode )
       THEN 1 ELSE 0 END AS ProductsInback
FROM dbo.user u
WHERE u.userCode = 'XYZ'

IN:

SELECT
  id,
  userCode,
  CASE WHEN userCode IN (SELECT userCode FROM dbo.Product)
       THEN 1 ELSE 0 END AS ProductsInback
FROM dbo.user
WHERE userCode = 'XYZ'

Upvotes: 2

Jen R
Jen R

Reputation: 1535

If you change your left join to an inner join, you will just get a list of users with products. The other users will not appear on the list. 

SELECT
dbo.user.id,
dbo.user.userCode

FROM dbo.user

JOIN dbo.Product
  ON dbo.Product.userCode= TotalProducts.userCode

Upvotes: 0

Related Questions