T-SQL : check if data exists in table

Question

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

Thorsten Kettner · Accepted Answer · 2019-03-06 18:44:45Z

2

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'

answered Mar 6, 2019 at 18:44

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jen R · Accepted Answer · 2019-03-06 18:43:28Z

0

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

answered Mar 6, 2019 at 18:43

Jen R

1,53318 silver badges23 bronze badges

Collectives™ on Stack Overflow

T-SQL : check if data exists in table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related