In SQLite, how do I select items in one table that are not in another?
There are two tables in SQLite:
Table1:
-------
id
name
Table2:
-------
id
temp_name
My question is, how do I write a SQL query that returns names in Table2
that are not in Table1
?
For example:
Table1:
-------
1, 'john'
2, 'boda',
3, 'cydo',
4, 'linus'
Table2:
-------
1123, 'boda'
2992, 'andy',
9331, 'sille',
2, 'cydo'
In this example, the SQL query should return items to andy
and sille
from Table2
because they are not in Table1
.
source to share
I was curious to know which of the options works best for my use case, and thought it might help others if I shared my results:
In short, pick where not at the fastest (for me!). It's also worth noting that using all options will return duplicates except for exceptions.
Option 0: SELECT {c2} FROM {t2} WHERE {c2} not in (SELECT {c1} FROM {t1});
Entries returned: 1098, Unique entries returned: 357
Average: 1.8680 seconds
Entries returned: 0, Unique entries returned: 0
Average: 0.6664 seconds
Option 1: SELECT {c2} FROM {t2} EXCEPT SELECT {c1} FROM {t1};
Entries returned: 357, Unique entries returned: 357
Average: 3.9455 seconds
Entries returned: 0, Unique entries returned: 0
Average: 3.3074 seconds
Option 2: SELECT {t2}.{c2} FROM {t2} LEFT OUTER JOIN {t1} ON {t1}.{c1} = {t2}.{c2} WHERE {t1}.{c1} IS null;
Entries returned: 1098, Unique entries returned: 357
Average: 2.3330 seconds
Entries returned: 0, Unique entries returned: 0
Average: 1.1982 seconds
Option 3: SELECT {c2} FROM {t2} WHERE NOT EXISTS (SELECT 1 FROM {t1} WHERE {c1} = {t2}.{c2});
Entries returned: 1098, Unique entries returned: 357
Average: 2.6945 seconds
Entries returned: 0, Unique entries returned: 0
Average: 0.9737 seconds
Here's the code I used to run the numbers:
import sqlite3
import timeit
# Database path here
database = "database.db"
# Your table and column names here
t1, c1 = 'Table_1', 'name'
t2, c2 = 'Table_2', 'temp_name'
# Reverse the test
dbs = [{'t1': t1, 'c1':c1, 't2': t2, 'c2': c2},
{'t1': t2, 'c1':c2, 't2': t1, 'c2': c1}]
commands = ["SELECT {c2} FROM {t2} WHERE {c2} not in (SELECT {c1} FROM {t1});",
"SELECT {c2} FROM {t2} EXCEPT SELECT {c1} FROM {t1};",
"SELECT {c2} FROM {t2} LEFT OUTER JOIN {t1} ON {t1}.{c1} = {t2}.{c2} WHERE {t2}.{c2} IS null;",
"SELECT {c2} FROM {t2} WHERE NOT EXISTS (SELECT 1 FROM {t1} WHERE {c1} = {t2}.{c2});",]
for i, c in enumerate(commands):
print("Option {}: {}".format(i, c))
for db in dbs:
co = c.format(**db)
foo = sqlite3.connect(database).execute(co).fetchall()
# Sanity check that entries have been found and how many
print("\tEntries returned: {}, Unique entries returned: {}".format(len(foo), len({a[0] for a in foo})))
# Reconnect to the database each time - I can't remember if there any caching
t = timeit.repeat(lambda: sqlite3.connect(database).execute(co).fetchall(), repeat=5, number=1)
print('\tAverage: {:.4f} seconds'.format(statistics.mean(t)))
source to share