CODEWITHSUNDEEP
sqlsqlite
kuslahne
kuslahne

Reputation: 730

Comparing two tables in SQLite

I have two tables and 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 produce result like this

result_table
field1

b
c
e
h
i

How is the syntax in sqlite? Thanks

Upvotes: 9

Views: 22356

Answers (2)

Aurel Wisse
Aurel Wisse

Reputation: 101

SELECT columns1 FROM table1 EXCEPT SELECT columns2 FROM table2;

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

This works fine for small to medium size tables. Avoid for tables with millions of lines.

See Compound Select Statements and the documentation of the SQLite SELECT statement.

Upvotes: 10

John Woo
John Woo

Reputation: 263723

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

Upvotes: 18

Related Questions