NOT IN not work in my query

Question

I have 2 tables data and page, in data table I have some records, and some data records id will be store in page table.

Now I want select id and title form data table which is not on page table. So I wrote this query :

SELECT d.id,d.title
FROM data AS d, page AS p
WHERE d.id NOT IN (p.data_id)
ORDER BY d.title ASC

this query will work, but when page table is empty this query cannot restore records !

Answer 1

use LEFT JOIN

SELECT  a.*
FROM    data a
        LEFT JOIN page b
            ON a.ID = b.data_id
WHERE   b.data_id IS NULL
ORDER   BY a.title ASC

• SQLFiddle Demo
• SQLFiddle Demo (empty page table)

Answer 2

Here is it with subquery, but without join:

SELECT id, title
FROM data
WHERE id NOT IN (SELECT data_id FROM page)
ORDER BY title ASC

Answer 3

The NOT IN will give you what you want but depending on your database system (and the type of indexes) this will not be the best (fastest) solution. More often then not EXISTS will be faster. But your mileage may vary.

Give it a try:

SELECT id, title FROM data
WHERE NOT EXISTS (SELECT * FROM page WHERE page.data_id = data.id)
ORDER BY title ASC

Answer 4

I think you are trying to determine what data does not have a page:

SELECT d.id, d.title
FROM data d
WHERE d.id NOT IN (
    SELECT data_id FROM page
)
ORDER BY d.title ASC;