I have the following tables:
Where cadastro contains the primary key id_cadastro that
the lotes refers to it.
However, the bank was not normalized, so the foreign key that refers to id_cadastro does not exist.
I'm doing the registration manually, but it's almost impossible to find any references that do not exist.
Is there any way that I can automatically search for non-existent references with SQL commands?
I imagine this should solve
select * from lotes where lotes.id_cadastro not in (select id_cadastro from cadastro)
There are two ways to do this, the first is this:
SELECT * FROM lotes a
WHERE a.id_cadastro NOT IN (
SELECT id_cadastro FROM cadastro
);
and the second is this:
SELECT * FROM lotes a
LEFT JOIN cadastro b ON a.id_cadastro = b.id_cadastro
WHERE b.id_cadastro IS NULL;
segunda opção has a huge performance gain over primeira opção .
I was able to resolve with the following code
SELECT *
FROM lotes l
WHERE NOT EXISTS
(
SELECT null
FROM cadastro c
WHERE c.id_cadastro = l.id_cadastro
)