Mysql - Select in two databases comparing columns

Question

I’m trying to make a select in two different databases by comparing the matrix column.

select A.matricula, B.matricula from banco1.escolas A JOIN banco2.escola B ...

The bank1 is the most complete. I would like to know which license plates are on bank1 and not on bank2.

I tried it this way:

select A.matricula, B.matricula from banco1.escolas A JOIN banco2.escola B where A.matricula <> B.matricula 

But it didn’t work. How can I?

Answer 1

Your query is almost right, for just one detail:

The banco1 is the most complete. I would like to know which plates are listed in Banco1 and not in Banco2.

If one bank has data that the other bank does not have, it should not use the INNER JOIN. That one Join returns the data if it succeeds in both tables. In your example, it was an INNER JOIN because of the WHERE.

Since bank1 has more data, use a LEFT JOIN, because in Command Join it is left (left) and will return the common data as well as the records that exist only in Banco1:

select A.matricula, B.matricula 
  from banco1.escolas A 
  left join  banco2.escola B on A.matricula = B.matricula 

Taking advantage, I suggest reading this excellent question and the responstas that have here on the site about the differences between Ner Join and Uter Join

EDIT: To reply to the comment "which license plates are in bank1 and are not in bank2": Looking at the above query once a LEFT JOIN, "banco1.escolas" records will always be displayed, and if a corresponding is not found in "banco2.escola", the result of the query will bring NULL in "B.matricula".

So, to return what exists in banco1 and does not exist in banco2, taking advantage of this Join, just filter the records where "B.matricula" is null, for example:

select A.matricula, B.matricula 
  from banco1.escolas A 
  left join  banco2.escola B on A.matricula = B.matricula 
 where B.matricula is null;

Another solution would be to "resume bank data1.schools where DOES NOT EXIST a correspondent in bank2.schools", which could be done using a subquery:

select A.matricula
  from escolasA A 
 where not exists (select B.matricula
                      from escolasB B 
                      where B.matricula = A.matricula);