MySQL Inner Join Query To Get Records Not Present in Other 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 would give me result that includes all countries that ARE NOT supported

So as per above 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 supported_countries table is empty, it doesn't show any records. How to achieve this result?

score 43 · Accepted Answer · answered Mar 31 '12 at 09:43

43

A LEFT JOIN will do that elegantly;

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

Demo here.

answered Mar 31 '12 at 09:43

Joachim Isaksson

score 5 · Answer 2 · edited Mar 31 '12 at 12:03

5

Try something like the following:

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

edited Mar 31 '12 at 12:03

Abel

answered Mar 31 '12 at 09:40

IT ppl

8

Subqueries are often [less performant](http://stackoverflow.com/questions/3856164/sql-joins-vs-sql-subqueries-performance) than explicit joins. They are easier to read, but do not scale well. – terrabruder Mar 03 '14 at 22:55

score 2 · Answer 3 · edited Dec 12 '21 at 05:45

2

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

edited Dec 12 '21 at 05:45

toonice

answered Mar 31 '12 at 09:50

Nilesh

score 0 · Answer 4 · answered Dec 21 '18 at 00:30

While @Joachim Isaksson gave me the clue, I tested this very similar query and worked on my database by replacing variables.

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

I gave the question and that Joachim's answer my thumbs up. Cheers !

4 Answers4