I would like to know how to do a Select to return non-existent records by comparing two tables with the String field.
Example:
Tabelas Campo1 Campo2
Tab1 Nome Tel
Tab2 Nome email
The result of the comparison (the select) would be only the Tab1 records that do not have the name in Tab2
Best way
SELECT *
FROM tabela1 t1
WHERE NOT EXISTS (SELECT t2.nome FROM tabela2 t2 where t1.nome=t2.nome)
If you only want to include the names of tabela 1 that do not correspond to tabela 2 , you can do this in several different ways
I'll stick only to the syntax conforming to SQL Ansi (valid for all DBMSs listed in the tags)
SELECT T1.*
FROM Tabela1 t1
left join Tabela2 t2 on t1.Nome=t2.Nome
where t2.nome is null
See working from SQLFiddle (MySQL 5.6) .
The predicate Exists is set to:
The EXISTS predicate whether any rows are selected by a subquery. If the subquery finds at least one row that satisfies its search condition, the predicate evaluates to true. Otherwise, if the result table of the subquery is empty, the predicate is false.
Free Translation
The EXISTS predicate determines whether any rows are selected by a subquery. If the subquery finds at least one row that satisfies its search condition, the predicate will be evaluated as true. Otherwise, if the subquery results table is empty, the predicate is false.
In this specific case:
SELECT *
FROM tabela1 t1
WHERE NOT EXISTS (SELECT t2.nome FROM tabela2 t2 where t1.nome=t2.nome)
See worked on SQLFiddle
IN
In this particular case, the operation is NOT IN:
SELECT *
FROM tabela1 t1
WHERE t1.nome NOT IN (SELECT t2.nome FROM tabela2 t2)
See worked on SQLFiddle