Finding Unique Rows that Fail to Join in MySQL?

I have two tables, one is called "keywords"; this table simply stores keywords as a unique keyword ID, and the text开发者_StackOverflow社区 of the keyword. The other is called "keylinks"; this table stores rows linking a media ID to a keyword ID.

If I had a media item, and I wanted to get all the keywords for that media item, I would use the following code:

   SELECT keywords.*, keylinks.*
     FROM keywords
LEFT JOIN keylinks ON (keylinks.keyword_id = keywords.keyword_id)
    WHERE keylinks.media_id = ?

What if I wanted to do the opposite?

Instead of getting the keywords that match a media ID, I would like to get the keywords that DON'T match a media ID. How would I do this? I cant simply use WHERE keylinks.media_id != ? because that would return thousands of rows of keylink entries that don't relate to that specific media ID, which may in fact be matching keywords.

---

There's at least three means of doing this. ANSI provides EXCEPT, which doesn't appear to be supported by MySQL at this time.

LEFT JOIN/IS NULL

The placement of criteria with an OUTER JOIN is crucial - if in the WHERE clause, the criteria is applied after the JOIN. If the criteria is in the JOIN, the criteria is applied before the JOIN.

   SELECT k.*
     FROM KEYWORDS k
LEFT JOIN KEYLINKS kl ON kl.keyword_id = k.keyword_id 
                     AND.media_id = ?
    WHERE keylinks.media_id IS NULL

NOT EXISTS

SELECT k.*
  FROM KEYWORDS k
 WHERE NOT EXISTS (SELECT NULL 
                     FROM KEYLINKS kl 
                    WHERE kl.keyword_id = k.keyword_id
                      AND kl.media_id = ?)

NOT IN

SELECT k.*
  FROM KEYWORDS k
 WHERE k.keyword_id NOT IN (SELECT kl.keyword_id
                              FROM KEYLINKS kl
                             WHERE kl.media_id = ?)

Which Performs Best?

It depends on if the columns compared can be nullable (the value could be NULL) or not.

• If the values are not nullable, the LEFT JOIN / IS NULL is the fastest means on MySQL only.
• Otherwise, if the columns are nullable -- NOT EXISTS/NOT IN are the most efficient.

---

SELECT K1.keyword_id 
  FROM keywords AS K1
 WHERE 
   NOT EXISTS (SELECT * 
                 FROM keylinks AS K2 
                WHERE K2.keyword_id = K1.keyword_id 
                  AND K2.media_id = %d);

This will give you all keywords for which there are no keylinks in the database.