SQL query select return result not in ONE table-CodePudding

Say I have a table people with a name field. In that table are 3 rows with names: Steve, Mark, Ricky

Then I have a list of names: [Shane, Ricky, Mark]

I want to execute one query that will return the values that aren't already in the table.

I know I can do this with the following:

names = [Shane, Ricky, Mark]

for name in names:
    sql = '''SELECT * FROM people WHERE name=%s'''
    db.execute(sql, args=name)
    results = db.fetchall()
    
    # If no results are returned for a name 
    # this means the name isn't already in the table.
    if not results:
        print(name) # This would return 'Shane' as it's not already in the table.

Is there a way I could do this more efficiently with one SQL statement, rather than looping over each name in the list?

CodePudding user response：

You can use: SELECT * FROM people WHERE name NOT IN(...)

CodePudding user response：

You can use the SQL IN operator, selecting the values already in the table;

SELECT * FROM people WHERE name IN (...your values...)

and then finding the difference between the two lists:

not_in_db = list(set(names).difference(results))