SQL show difference between 2 tables

Question

I know there are similar questions, but I can't find solution to what I need to do.

First of all I have 2 tables :

I need to find which records form SD.AirlineRatings do I have in SD.Airlines, I did this :

SELECT b.Name AS Airline FROM SD.Airlines b
LEFT JOIN SD.AirlineRatings a ON a.AirlineName = b.Name
WHERE a.AirlineName IS NOT NULL;

Works fine, showed me 249/405 records. Now... If I need to compare those 249 records towards SD.AirlineRatings and check which ones I don't have.

I bet answer is simple but I don't know SQL that much.

Thanks

Gordon Linoff · Accepted Answer · 2016-05-05 11:00:55Z

2

If you want records in AirLines ratings that are also in Airlines, then I would recommend EXISTS or IN:

select a.*
from sd.Airlines a
where exists (select 1 from sd.AirlineRatings ar where ar.AirlineName = a.Name);

If you want unrated airlines:

select a.*
from sd.Airlines a
where not exists (select 1 from sd.AirlineRatings ar where ar.AirlineName = a.Name);

And, if you want ratings on airlines that don't exist, you would swap the two tables in the query.

answered May 5, 2016 at 11:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1