Ways to compare and find differences for SQL Server tables data

We got requirement to compare tables data from 2 different sources to know what was changed.

To do same we have used except command, you can also use left join

For example:

/*** To find extra rows********/

select * from emp1 left join emp2 on emp1.id =emp2.id

/*** OR  ********/

select * from emp1

except

select * from emp2

Below is demo to achieve same along source code:

/*************Creating dummy data for demo***********************/

USE tempdb

GO

CREATE TABLE [dbo].[emp1](  [id] [nchar](10) NOT NULL,  [name] [nchar](10) NULL,  [age] [nchar](10) NULL )

GO

insert into [dbo].[emp1] values

(1,'Saurabh',30), (2,'Pradeep',25),(3,'Rupa',26),(4,'Parul',30)

GO

----(3 row(s) affected)

CREATE TABLE [dbo].[emp2](  [id] [nchar](10) NOT NULL, [name] [nchar](10) NULL, [age] [nchar](10) NULL )

GO

insert into [dbo].[emp2] values

(1,'Saurabh',30), (2,'Pradeep',25),(3,'Rupa',26)

GO

/*** To find extra rows********/

select * from emp1 left join emp2 on emp1.id =emp2.id

/*** To find extra rows********/

select * from emp1

except

select * from emp2

/************************************/