EXCEPT operator in SQL server

The except operator will give the unique rows from the left query which are not presented in the right query.

Let us create two tables blog1 and blog2.
The two tables, column names and datatypes should be same.
--------------------------------------------------------------------------------------------------------------------------
Create Table blog1
(
    Id int primary key,
    Name nvarchar(100),
    Gender nvarchar(100)
)
Go

Insert into blog1 values (1, 'sai', 'M')
Insert into blog1 values (2, 'xxx', 'F')
Insert into blog1 values (3, 'dummu', 'M')
Insert into blog1 values (4, 'vijendhar', 'M')
Insert into blog1 values (5, 'yyy', 'F')
Go
-------------------------------------------------------------------------------------------------------------------
Create Table blog2
(
    Id int primary key,
    Name nvarchar(100),
    Gender nvarchar(100)
)
Go

Insert into blog2 values (4, 'vijendhar', 'M')
Insert into blog2 values (5, 'yyy', 'F')
Insert into blog2 values (6, 'sindu', 'F')
Insert into blog2 values (7, 'vinni', 'F')
Insert into blog2 values (8, 'kalyan', 'M')
Go
---------------------------------------------------------------------------------------------------------------------
Now execute both the tables.

select * from blog1
select * from blog2

------------------------------------------------------------------------------------------------------------------

Now it's time to apply except operator, as discussed in the first step it will return the unique rows from the left query which are not placed in right query. From the above tables blog1 is the left table and blog 2 is the right table.

-------------------------------------------------------------------------------------------------------------

Apply except operator.

select id, name, gender from blog1
except
select id, name, gender from blog2

OR

Select Id, Name, Gender From blog1

Where Id NOT IN (Select Id from blog2)

The difference between Except and NOT IN operator is that NOT IN does not filter duplicates where as Except filters.

Now the result will be as follows. Where 1,2,3 are unique which are not in blog2 table.

----------------------------------------------------------------------------------------------------------------------
If you want right query data, then write the query as follows.

select id, name, gender from blog2
except
select id, name, gender from blog1

Now the result will be as follows. Where 6,7,8 are unique which are not in blog1 trouble.