Select records from a table, which don't exist

问题:

I have 2 tables TABLE1 and TABLE2 in sqlite DB. TABLE 2 has some of the records of the TABLE1. What I want is to select all the records from TABLE 1, which don't exist in TABLE2. So I coded:

String sq = "SELECT TABLE1.name, TABLE1.surname, TABLE1.id FROM TABLE1"
            + "LEFT JOIN TABLE1"
            + "ON TABLE1.id <> TABLE2.id";

However this query returns something that i don't get it.. Which is the right query?

回答1:

Check for NULLs in second table like:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.id = TABLE2.id
WHERE TABLE2.id IS NULL

Alternate solution with NOT EXISTS:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE TABLE1.id = TABLE2.id)

One more with NOT IN:

SELECT TABLE1.name, TABLE1.surname, TABLE1.id 
FROM TABLE1
WHERE TABLE1.id NOT IN(SELECT id FROM TABLE2)

回答2:

Use subquery to return all id of table 2, now include those ids in table1 which don't exist in table 2.

Select name, surname, id from TABLE1 where id not in (Select id from TABLE2)