Which tags are not in the database?

Question

Given a collection of user specified tags how do I determine which ones are not in the tags table with 1 SQL Statement?

Assuming a table schema tags (id, tag) and I'm using mysql, if there's an optimization I'm unaware of.

thanks

Answer 1

select * from canonical_list_of_tags where tag not in (select tag from used_tags)

At least that works in T-SQL for SQL Server ...

Edit: Assuming that the table canonical_list_of_tags is populated with the result of "Given a collection of user specified tags"

Answer 2

select
    utt.tagName
from
    userTypedTags utt
    left join tag t on utt.tagName = t.tag
where
    t.ID is null
    and utt.userID = <ID of the User in question>

That is assuming that you have table

userTypedTags(userID, tagName)

I have added a related question

Answer 3

SELECT  Tag
FROM    UserSpecifiedTags
  LEFT OUTER JOIN AllTags ON UserSpecifiedTags.Tag = AllTags.Tag
WHERE   AllTags.Tag IS NULL

This should return what you want. In my experience, executing a join and looking for rows which don't have a match is much quicker than using the IN operator.