Mysql - Select between 2 tables with "String" field

Asked

Viewed 332 times

1

I would like to know how to do a Select to return the nonexistent records, comparing two tables with String field.

Example:

Tabelas Campo1   Campo2
Tab1    Nome     Tel
Tab2    Nome     email

The result of the comparison (select) would be only Tab1 records that do not have the name in Tab2

mysql sql join

2 answers

5


Best way

SELECT *
FROM tabela1 t1
WHERE NOT EXISTS (SELECT t2.nome FROM tabela2 t2 where t1.nome=t2.nome)

  • I tried using this example but gave error.

  • I used this code here and it worked: SELECT * FROM tab1 a WHERE NOT EXISTS(SELECT nome FROM tab2 b WHERE a.nome = b.nome);&#Thanks for all your help! Big hug!

  • Thanks for the constructive review @Williamjohnadamtrindade. I corrected the reply. At the time of writing I ended up confusing NOT IN with NOT EXIST and I messed up in query.

4

If what you want is to bring only the names of tabela 1 which do not correspond to tabela 2, Voce can do this several different ways

I will stick to SQL Ansi compliant syntax only (valid for all Dbms listed in tags)

Using the "left Join"

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).

Using the Exists predicate

The predicate Exists is defined as:

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 evaluates to true. Otherwise, if the result table of the subquery is Empty, the predicate is false.

In free translation

The EXISTS predicate determines whether a row is selected by a sub-volume. If the subconsultation finds at least one line that satisfies its search condition, the predicate will be evaluated as true. Otherwise, if the table of results of the subconsulta 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

Using the Operator 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

Browser other questions tagged mysql sql join

You are not signed in. Login or sign up in order to post.