Intersect Two Tables In SQL Server
This article describes how to intersect two tables.
It is similar to UNION command. It operates on two SQL statements. Difference between UNION and INTERSECT is UNION selects those values which appears in either the first or the second statement and INTERSECT returns those value which appears in both statements.
Syntax
(SQL Statement 1)
INTERSECT
(SQL Statement 2)
Example
Create first table
CREATE TABLE employee
(
[id] int NULL,
[name] char(20) NULL,
[department] varchar(45) NULL
) ON [PRIMARY]
Insert some values
insert into employee
select 1,'arvind','HR' union all
select 2,'ravi','Manager' union all
select 3,'raushan','Finance' union all
select 4,'aman','Sales'
select * from employee
Output:
Create second table
CREATE TABLE emp
(
[id] int NULL,
[name] char(20) NULL,
[salary] int NULL
) ON [PRIMARY]
Now insert some values and select table.
select * from emp
Output:
Intersect both tables
SELECT name FROM emp
INTERSECT
SELECT name FROM employee as CommonName
Output: