Q: Not exists statement when trying to select a row I am trying to insert a row and prevent duplication. I used not exists to do the filtering. However, the results always contain the ones I don't need. My question is how to select rows that data from all column match?
for example:
insert into TB1 (Id, Name) VALUES (1, 'Job')
insert into TB1 (Id, Name) VALUES (2, 'Bob')
insert into TB1 (Id, Name) VALUES (3, 'Cob')

insert into TB2 (Id, Name) VALUES (1, 'Fob')
insert into TB2 (Id, Name) VALUES (2, 'Bob')
insert into TB2 (Id, Name) VALUES (3, 'Job')
insert into TB2 (Id, Name) VALUES (4, 'Mob')


select b.Id, b.Name
from TB2 as b
where not exists 
(
    select 1
    FROM TB1 as a
    where a.Id = b.Id and
    a.Name = B.Name
)

Here, the return is going to be: 1, fob; 3, job; 4, mob.
Though my intention was only to get 4.mob, as 4.mob is the one not in table 1.

A: There are several ways:
Using EXCEPT
SELECT Id FROM TB2 EXCEPT ALL SELECT Id FROM TB1

Using NOT EXIST
SELECT Id, Name FROM TB2 A WHERE NOT EXISTS (SELECT ID FROM TB1 WHERE Id=A.Id)

Using LEFT JOIN
select A.Id,A.Name FROM TB2 A LEFT JOIN TB1 B USING(Id) WHERE B.Id IS NULL

Using NOT IN
However, the best way to prevent duplicates would be to add a unique index on ID and to use INSERT .. ON DUPLICATE KEY.
