CODEWITHSUNDEEP
mysqlsql
M-D
M-D

Reputation: 10397

MySQL Outer Join and similar tables

I have a table, say, country_previous and another table country_current. Both are same in structure, only the names are different. See the structure of them below :

CREATE TABLE `country_previous` (
`name` varchar(70) NOT NULL DEFAULT '',
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100000000 AVG_ROW_LENGTH=50

and the other one

CREATE TABLE `country_current` (
`name` varchar(70) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

So as the names indicate, country_previous has all the country names until, say an year ago, and country_current has all the countries until now. So I want to get all the countries that were created after the last update of country_previous. I tried this query, but it has many entries from country_previous.

SELECT count(*) FROM country_previous NATURAL RIGHT OUTER JOIN country_current WHERE   country_previous.name IS NULL;

That gives more count than expected. For eg: my country_previous has 90 countries and country_current has 110 countries. Then, I should get only the new countries since then(country_previous's last update), which is 110 - 90 = 20. But I get more than 20.

so what's wrong with the query and what should I do ?

Thanks in advance.

PS : This country idea is just an example that really helps me to make others understand what I really need and I am not doing anything with country names actually.

Upvotes: 0

Views: 120

Answers (3)

Mansuro
Mansuro

Reputation: 4627

The query returns unexpected results cause there might be some countries in country_previous that doesn't exist in the table country_current. Thus, when you make the verification country_previous.name IS NULL, it doesn't work on these records.

Upvotes: 0

Tom Mac
Tom Mac

Reputation: 9853

You could try using the following query:

select count(*) from country_current cc
where cc.name not in (select cp.name from country_previous cp);

This will give you a count of all country names in country_current that are not in country_previous (which I think is what you are after?).

Or, if you fancy using a join to do this then you could use something like the following:

insert into country_current values ('USA');
insert into country_current values ('China');
insert into country_current values ('Russia');
insert into country_current values ('UK');
insert into country_current values ('France');

insert into country_previous values ('Russia');
insert into country_previous values ('UK');
insert into country_preivous values ('France');

and then run:

select sum(case when cp.name is null then 1 else 0 end) as newCountries
from country_current cc
left outer join country_previous cp on cc.name = cp.name;

Upvotes: 2

Suleman Ahmad
Suleman Ahmad

Reputation: 2113

If the country names are similar then minus query is as follows

SELECT count(*) from (    
SELECT cc.name from country_current cc
MINUS
SELECT cp.name from country_previous cp)

Upvotes: 0

Related Questions