SQL Server SET Operators

There are four SQL Server SET operators available:

  • UNION
  • UNION ALL
  • INTERSECT
  • EXCEPT

Syntax

SELECT statement1
UNION|UNION ALL|INTERSECT|EXCEPT
SELECT statement2

Create following two tables(emp department)

emp table

CREATE TABLE [dbo].[emp](
[empname] [varchar](20) Not NULL,
[empno] [numeric](18, 0) NULL,
[job] [varchar](30) NULL,
[joindate] [date] NULL,
[salary] [money] NULL,
[deptno] [nchar](10) NULL,
)

Emp-table

dept table

CREATE TABLE [dbo].[dept](
[deptno] [nchar](10) NULL,
[dname] [varchar](40) NULL,
[dloc] [varchar](40) NULL
)

Dept-Table

1.UNION Operator:

  • It combines the result of two select statements
  • Union operator eliminates duplicates in the output
  • The result set is sorted

Rule: To perform Union operator query1 must be equal to query2 structure. Structure means number of columns and datatype.

Example:
Display the job names in the department 10 and 20
SELECT job FROM emp where deptno = 10
UNION
SELECT job FROM emp where deptno = 20

Output:

Job
Administrator
Clerk
Developer
Manager

2.UNION ALL Operator:

  • This is similar to Union operator
  • With UNION ALL operator duplicates are included and result is not sorted

Example:
Display the job names in the department 10 and 20
SELECT job FROM emp where deptno = 10
UNION ALL
SELECT job FROM emp where deptno = 20

Output:

Job
Administrator
Clerk
Developer
Developer
Manager

3.INTERSECT Operator: This rturns common values from the result of two select statements

SELECT job FROM emp where deptno = 10
INTERSECT
SELECT job FROM emp where deptno = 30

Output:

Job
Manager

4.EXCEPT Operator: It returns values presenting in result of 1st select statement and not in 2nd select statement.

SELECT job FROM emp where deptno = 10
EXCEPT
SELECT job FROM emp where deptno = 20

Output:

Job
Clerk
Manager
Powered by k2schools