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);
Hello @Riccardo! What would the code look like comparing the license plates and printing the ones in bank1 (updated bank) and not in bank2? That is, which plates are on bench 1 and are not on bench 2. I did several tests using the sent example and in all cases I can not get the desired result.
– SysrQw
edited the answer, see there :)
– Ricardo Pontual
killed! was just that! thank you.
– SysrQw