MySQL Inner Join Query to get records not present in another table

Question

I have table 1, all_countries, as follows -

id | country ------------------ 1 | USA 2 | China 3 | India 4 | France 5 | UK 6 | Australia 

and I also have table 2, supported_countries, as -

 id | country ------------------ 1 | USA 2 | China 

Now I need a query that will give me a result that includes all countries that are NOT supported

As in the previous example, I should get

 India France UK Australia 

I am using the following query -

SELECT ac.country FROM all_countries ac INNER JOIN supported_countries sc ON sc.country_name! = Ac.country_name

It works fine, except when the supported_countries table is empty, there are no records in it. How to achieve this result?

Answer 1

A LEFT JOIN will do it elegantly;

 SELECT a.* FROM all_countries a LEFT JOIN supported_countries s ON a.country = s.country WHERE s.id IS NULL; 

Demo is here .

Answer 2

Try the following:

 SELECT * FROM all_countries WHERE country NOT IN (SELECT country FROM supported_countries) 

Answer 3

SELECT ac.country FROM all_countries ac LEFT JOIN supported_countries sc ON sc.country_name = ac.country_name WHERE ISNULL (sc.country_name)

Answer 4

Although @joachim Isaacson gave me a hint, I tested this very similar query and worked on my database, replacing the variables.

 SELECT * FROM all_countries LEFT JOIN supported_countries ON all_countries.id = supported_countries.id WHERE supported_countries.id IS NULL 

I asked a question, and Joachim answered my thumbs. Hurrah!