CODEWITHSUNDEEP
mysqlsql
Keith Palmer Jr.
Keith Palmer Jr.

Reputation: 27962

SQL - Get "records" that do NOT have matches in a list of strings I have

This is a very common scenario I run into that I'm wondering if there's an easy way to solve with SQL --

I have a table with 2,000 records in it: 

UNIVERSITY
id, name

So, for example:

1, UConn
2, Eastern
3, Western
4, Southern

Then someone gives me an Excel sheet with some university names in it:

Eastern
Western
UMass
MIT

I need to find out which university names from the Excel sheet do not exist in the SQL table. i.e. Filter the universities that ARE in the table out of the Excel sheet. e.g.: 

UMass
MIT

If I did this: 

SELECT * FROM university WHERE name IN ( 'Eastern', 'Western', 'UMass', 'MIT' )

I would get back: 

2, Eastern
3, Western

But what I really what back is the stuff in the spreadsheet that wasn't in the table. e.g.: 

UMass
MIT

I there any easy way to do this with an SQL query?

Upvotes: 0

Views: 100

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

The easiest way is upload your excel sheet in a db table and do a LEFT JOIN

SQL DEMO

SELECT newUniv.name
FROM newUniv
LEFT JOIN University
  ON newUniv.name = University.name
WHERE University.name IS NULL;

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Out of laziness I would probably just paste the names from sql into a new sheet and use an excel formula to find out which ones are not in the database.

This returns TRUE when a value is not in the reference list. 

=ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE))

Where A2 is a name you are looking up and Sheet2!A:A is where you pasted the names from the database

Upvotes: 0

Related Questions