SQL Union and All Union

SQL Union :- 

The UNION operator is used to combine the result-set of two or more SELECT statements. 

Note(1):-Each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. 

Note(2):- The UNION operator selects only distinct values by default. 

Syntax :-

  

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2

SQL Union ALL :-Using UNION ALL, we will get all the rows including duplicates.

Syntax :-

  

SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2

Now, we create two tables named are tblStudents and tblEmployees and insert some records as shown in below. 

  
------------tblStudents--------------

CREATE TABLE tblStudents
(
StuID INT PRIMARY KEY,StuName VARCHAR(100),Address varchar(200),MobileNo bigint,Email NVARCHAR(200)
)


------------tblEmployees--------------

CREATE TABLE tblEmployees
(
EmpID INT PRIMARY KEY,EmpName VARCHAR(100),Address varchar(200),MobileNo bigint,Email NVARCHAR(200)
)


------------Insert records into tblStudents table--------------

INSERT INTO tblStudents VALUES(1001,'Santosh kumar singh','A-13',87878,'sa@gmail.com')
INSERT INTO tblStudents VALUES(1002,'Manorma arya','T-13,Bihar',83878,'ma@gmail.com')
INSERT INTO tblStudents VALUES(1003,'Pooja','G-13',84878,'p@gmail.com')
INSERT INTO tblStudents VALUES(1004,'Arun kumar','U-13,New Delhi',77878,'a@gmail.com')
INSERT INTO tblStudents VALUES(1005,'Sourab kumar','D-89,Bihar',9878,'su@gmail.com')
INSERT INTO tblStudents VALUES(1006,'Suneeta','U-13,New Delhi',74878,'sut@gmail.com')



------------Insert records into tblEmployees table--------------

INSERT INTO tblEmployees VALUES(1001,'Sanjay kuamr','P-13',80878,'sk@gmail.com')
INSERT INTO tblEmployees VALUES(1002,'Prasidh kumar','T-93,Bihar',773878,'pk@gmail.com')
INSERT INTO tblEmployees VALUES(1003,'Reen Tanti','K-13',82878,'rt@gmail.com')
INSERT INTO tblEmployees VALUES(1004,'Chandan kumar','A-16,New Delhi',71878,'ck@gmail.com')
INSERT INTO tblEmployees VALUES(1005,'Basant','D-09,Bihar',9838,'b@gmail.com')
INSERT INTO tblEmployees VALUES(1006,'Brijesh kumar','U-13,New Delhi',70878,'b@gmail.com')
INSERT INTO tblEmployees VALUES(1007,'Pragya','E-89,UP',96078,'pr@gmail.com')

                                                     tblStudents 

tblEmployees

Union Example :-

  

SELECT Address FROM tblStudents UNION SELECT Address FROM tblEmployees ORDER BY Address

Union ALL Example :-

  

SELECT Address FROM tblStudents UNION ALL SELECT Address FROM tblEmployees ORDER BY Address