MySQL: Select pages that are not tagged?

I have a db with two tables like these below,

page table

pg_id    title
1        a
2        b
3        c
4        d

tagged table

tagged_id   pg_id
1           1
2           4

I want to select the pages which are tagged, I tried with this query below but doesn't work,

SELECT *
FROM root_pages
LEFT JOIN root_tagged ON ( root_tagged.pg_id =  root_pages.pg_id )
WHERE root_pages.pg_id !=  root_tagged.pg_id

It returns zero - Showing rows 0 - 1 (2 tota开发者_StackOverflow社区l, Query took 0.0021 sec)

But I want it to return

pg_id    title
    2        b
    3        c

My query must have been wrong?

How can I return the pages which are not tagged correctly?

---

SELECT *
FROM root_pages
LEFT JOIN root_tagged ON root_tagged.pg_id = root_pages.pg_id
WHERE root_tagged.pg_id IS NULL

The != (or <>) operator compare two values, but cannot be used for NULL.

• NULL = NULL returns false
• NULL = 0 returns false
• NULL != NULL returns false

You get the point, to check for NULL you should use the IS or IS NOT operator.

---

If your density to tag to pages is more than 2:1 or so, then using NOT EXISTS will be faster than using LEFT JOIN + IS NULL

SELECT *
FROM root_pages
WHERE NOT EXISTS (
    SELECT *
    FROM root_tagged
    WHERE root_tagged.pg_id =  root_pages.pg_id )

It is an alternative that more clearly states what you are looking for, a non-existence.

For the strikeout text above:
The question is MySQL specific, and assuming root_tagged.pg_id is not nullable, LEFT JOIN + IS NULL is implemented using ANTI-JOIN which is the same strategy as NOT EXISTS, except there seems to be some overhead added by NOT EXISTS, so LEFT JOIN is supposed to work faster.