SQL Server UNION, UNION ALL, EXCEPT and INTERSECT Operator

SQL Server UNION, UNION ALL, EXCEPT and INTERSECT Operator Sql Set Statements are used to combines the results of two or more queries into a single result set.

The following are basic rules for combining the result sets of two or more queries:

1) The number and the order of the columns must be the same in all queries.

2) The data types must be compatible.

For more you might visit:

Different ways to insert data into Sql Server table

Lets create first two table with data.

CREATE TABLE dbo.tbFirstStudentList ( StudentId int, StudentName varchar(100) ) Go INSERT INTO dbo.tbFirstStudentList VALUES(1,'Santiago'),(2,'Daniel'),(3,'Jayden'),(4,'Agustín'),(5,'Sofía'),(6,'Alysha') Go CREATE TABLE dbo.tbSecondStudentList ( StudentId int, StudentName varchar(100) ) Go INSERT INTO dbo.tbSecondStudentList VALUES(1,'John'),(2,'Daniel'),(3,'Robert'),(4,'Agustín'),(5,'William'),(6,'Alysha') Go SELECT * FROM dbo.tbFirstStudentList SELECT * FROM dbo.tbSecondStudentList Go

OUTPUT:
StudentId   StudentName
----------- ---------------------------
1           Santiago
2           Daniel
3           Jayden
4           Agustín
5           Sofía
6           Alysha

(6 rows affected)

StudentId   StudentName
----------- --------------------------
1           John
2           Daniel
3           Robert
4           Agustín
5           William
6           Alysha

(6 rows affected)

SQL UNION Operator:

Used to combining two or more select queries back to back to get one result set. UNION will returns only unique rows set.

SELECT * FROM dbo.tbFirstStudentList UNION SELECT * FROM dbo.tbSecondStudentList

OUTPUT:
StudentId   StudentName
----------- ------------------------
1           John
1           Santiago
2           Daniel
3           Jayden
3           Robert
4           Agustín
5           Sofía
5           William
6           Alysha

(9 rows affected)

SQL UNION ALL Operator:

Used to combining two or more select queries back to back to get one result set. UNION will returns only unique rows set, while UNION All returns all rows from selected tables. i.e. even duplicate also if same rows are present in other table too.

SELECT * FROM dbo.tbFirstStudentList UNION ALL SELECT * FROM dbo.tbSecondStudentList

OUTPUT
StudentId   StudentName
----------- -------------------------
1           Santiago
2           Daniel
3           Jayden
4           Agustín
5           Sofía
6           Alysha
1           John
2           Daniel
3           Robert
4           Agustín
5           William
6           Alysha

(12 rows affected)

SQL INTERSECT Operator:

INTERSECT returns only common rows with in selected tables.

SELECT * FROM dbo.tbFirstStudentList INTERSECT SELECT * FROM dbo.tbSecondStudentList Go

OUTPUT
StudentId   StudentName
----------- -----------------------
2           Daniel
4           Agustín
6           Alysha

(3 rows affected)

SQL EXCEPT Operator:

EXCEPT returns all those rows in first table which are not present in second table.

SELECT * FROM dbo.tbFirstStudentList EXCEPT SELECT * FROM dbo.tbSecondStudentList Go

OUTPUT
StudentId   StudentName
----------- ----------------------
1           Santiago
3           Jayden
5           Sofía

(3 rows affected)

OUTPUT
StudentId   StudentName
----------- ----------------------
1           John
3           Robert
5           William

(3 rows affected)

Drop dummy tables