Performant check for existence of values

I have a little performance problem with an MySQL-Query and don't know how to build it right, so it does work with as little as possible effort.

Problem:

Imagine a table with two columns: user(int), item(int)

The users have different items associated with. Maybe user-1 has item-1, item-2, item-3 and user-2 has item-1, item-3, item-4.

What I want to know is for a specific user-X and each item in seperate if at least one other user has this items also.

What I started with was

SELECT item
FROM table
WHERE item IN (SELECT item FROM table WHERE user = X) AND user != X
GROUP BY item

... but this was inefficient because in this case the query searchs through the whole table and checks for each item even then if each one was already found. I can't LIMIT开发者_运维问答 the query because I don't know how many items user-X actually has. And sending a search query with LIMTI 1 for each item individually is also not such a good idea.

For abstraction you can also say I want to know if a set of item-X, ..., item-Y (not order or continous numbering) are associated with at least one user.

I don't care for how often the item is around at all, just want to know if at least once.

So, how can this be done right? Thanks for any suggestions!

What you're looking for is the EXISTS clause.

It will evaluate as true if there are any matching rows in the provided subquery.

SELECT item
FROM table AS t1
WHERE user = X
  AND EXISTS ( SELECT * FROM table AS t2 WHERE user <> X AND t1.item = t2.item )
GROUP BY item

It's a simple HAVING, no?

give me items that both user x and at least one other user has

SELECT item
FROM table
GROUP BY item -- per item
HAVING 
  COUNT (DISTINCT UserID) > 1 -- more than one user for an item
  AND 
  COUNT(CASE WHEN userID = X THEN 1 ELSE NULL END) > 0 -- including user x

SELECT DISTINCT t1.item
    FROM table t1
        INNER JOIN table t2
            ON t1.item = t2.item
                AND t1.user <> t2.user
    WHERE t1.user = X