INTERSECT says “give me what’s common to both” as the word itself implies. It is the intersection of the two. If the first set was the numbers {1, 2, 3, 4} and the second set was the numbers {2, 3, 7, 8} then the intersection would be {2, 3}. Contrast UNION and EXCEPT. Microsoft’s books online says: “INTERSECT returns distinct rows that are output by both the left and right input queries operator.”

Have a look at the T-SQL code below.

select * from tblDepartment;

select * -- create a new table
into tblDepartmentNew
from tblDepartment  -- has 7 rows with 2 sets of duplicates.

delete from tblDepartmentNew where Department = 'HR';
insert into tblDepartmentNew (Department, DepartmentHead)
    values ('New Dept', 'Jack Jr.');

select * from tblDepartmentNew

select * from tblDepartment -- has 5 rows
intersect
select * from tblDepartmentNew  -- has 4 rows
order by DepartmentHead;
-- there is only one Accounts, James row

Here are the results in SSMS. Notice that after the intersect we have only those that are common to each, the duplicates have been eliminated and the order by works at the end of the statement.