1

I have two tables in a SQLITE DB (Table 1 and Table 2). I need to determine which items in table 1 are not in table two, and then print the results. I think I may have a working prototype, but am not familiar with SQL too much. 

import sqlite3

sqlite_file = 'data.db'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()


c.execute("SELECT PK FROM Table1 WHERE NOT EXISTS(SELECT 1 FROM Table2 WHERE PK = Table1.pk)")
results = list(c)
count = len(results)


print(results)
print(count)
  • Can someone confirm that this looks right?
  • Is there a better way to do this? If not, any help would be much appreciated
Andy K
  • 4,944
  • 10
  • 53
  • 82
grigs
  • 1,140
  • 3
  • 15
  • 28

1 Answers1

1

I think you are almost there

import sqlite3

sqlite_file = 'data.db'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()

#using a NOT IN is better than the NOT EXISTS
#it gets straight to what you are looking for

c.execute("SELECT col1 FROM Table1 WHERE col1 NOT IN (SELECT col1 FROM Table2)")
results = list(c)
count = len(results)

print(results)
print(count)

As they said in this answer, EXISTS will tell you whether a query returned any results as IN is used to compare one value to several, and can use literal values.

Andy K
  • 4,944
  • 10
  • 53
  • 82
  • Thank you! And thanks for explaining the "NOT IN" vs "EXISTS", it makes total sense now. – grigs Oct 18 '17 at 17:37
  • One quick question, if I wanted to display the entire row rather than simply col1, how would I write that? – grigs Oct 18 '17 at 17:38
  • hi @grigs, if you are using the col1 in the WHERE condition, I would do something like this `SELECT * FROM table1 WHERE col1 NOT IN (SELECT col1 FROM Table2)`. If want to be sure that they are data, not columns specifically, I would do this `SELECT * FROM table1 WHERE NOT EXISTS (SELECT col1 FROM Table2)` – Andy K Oct 18 '17 at 17:42