Comparing two tables in SQLite

Question

I have two tables and you want to compare rows on sqlite like this

table1           table2
field1           field1

a                   a
b                   d
c                   f
d                   g
e
f
g
h
i

and I want to create such a result

result_table
field1

b
c
e
h
i

How is the syntax in sqlite? Thanks

Answer 1

SELECT DISTINCT Field1
FROM Table1 
WHERE Field1 Not IN 
    (SELECT DISTINCT Field1 FROM Table2)

Answer 2

SELECT columns1 FROM table1 EXCEPT SELECT columns2 FROM table2;

The SQLite EXCEPT clause returns all rows from the left SELECT statement that are not the result of the second SELECT statement. The number of selected columns must be the same in both SELECT statements.

This is great for small to medium sized tables. Avoid tables with millions of rows.

See Compound selection expressions and SQLite SELECT documentation .