Answered! SQL Question: I have 2 tables, EMPLOYEE and EMP, they have the same structure as EMPLOYEE was backed up then and changed…

You can use except statement in following manner :

[select query 1] except [select query 2]

This is what you can do :

select * from EMPLOYEE EXCEPT select * from EMP;

this will return all rows from EMPLOYEE table which are different or not in EMP table.

unfortunately MySql doesn’t support EXCEPT operator.

Alternative way to implement except :

– Assuming there are only 2 attributes in both tables and these are : Id and Name.

two tables :

EMPLOYEE(Id, Name) where Id is primary key.

EMP(Id, Name) where Id is primary key.

query :

select *
from EMPLOYEE as e1
where not exists (select *
from EMP as e2
where e1.Id = e2.Id and e1.Name = e2.Name);

if there are 10 columns in both tables (lets say column names are a1,a2,a3,…a10),

then compare all 10 columns in where clause of subquery.

eg :

select *
from EMPLOYEE as e1
where not exists (select *
from EMP as e2
where e1.a1 = e2.a1 and e1.a2 = e2.a2 and e1.a3 = e2.a3 and e1.a4 = e2.a4 and e1.a5 = e2.a5 and

e1.a6 = e2.a6 and e1.a7 = e2.a7 and e1.a8 = e2.a8 and e1.a9 = e2.a9 and e1.a10 = e2.a10);

If you have any doubts you can ask in comment section.