Post
Ask Question

Union and Union All

Create Table By executing the query and inserting data

CREATE TABLE employeeslist
( 
    id INT IDENTITY(1,1) PRIMARY KEY,
    [Name] VARCHAR(100) NOT NULL,
    Gender VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employeeslist VALUES ('Jitendra Maurya', 'M', 'Chandigarh', 30000);
INSERT INTO employeeslist VALUES ('Shalinee Maddheshiya', 'F', 'Chandigarh', 35000);
INSERT INTO employeeslist VALUES ('Sristi Maurya', 'F', 'Chandigarh', 33000);
INSERT INTO employeeslist VALUES ('Gaurav Pathak', 'M', 'Chandigarh', 38000);
INSERT INTO employeeslist VALUES ('Amisha Gupta', 'F', 'Kanpur', 36000);
INSERT INTO employeeslist VALUES ('Shahin Quershi', 'F', 'Kanpur', 32000);
INSERT INTO employeeslist VALUES ('Pooja Yadav', 'F', 'Kanpur', 35000);
INSERT INTO employeeslist VALUES ('Arushi Pal', 'F', 'Kanpur', 31000);
INSERT INTO employeeslist VALUES ('Bhavya Gupta', 'F', 'Lucknow', 37000);
INSERT INTO employeeslist VALUES ('Rohit Maurya', 'M', 'Lucknow', 35000);

CREATE TABLE employeeslist1
( 
    id INT IDENTITY(1,1) PRIMARY KEY,
    [Name] VARCHAR(100) NOT NULL,
    Gender VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employeeslist1 VALUES ('Jitendra Maurya', 'M', 'Chandigarh', 30000);
INSERT INTO employeeslist1 VALUES ('Shalinee Maddheshiya', 'F', 'Chandigarh', 35000);
INSERT INTO employeeslist1 VALUES ('Pragati Pal', 'F', 'Kanpur', 12000);
INSERT INTO employeeslist1 VALUES ('Bhavya Gupta', 'F', 'Lucknow', 37000);
INSERT INTO employeeslist1 VALUES ('Anjali Pathak', 'M', 'Dehradun', 38000);

Questions

1. Fetch a resultant table that includes all records from both the table as one table.

Answers

select * from employeesGH
union all
select * from employeesGH1

2. Fetch a resultant table that includes all records from both tables as one table but no duplicate values.

Answer

select * from employeesGH
union
select * from employeesGH1

3. Fetch a resultant table that has the id, name, and city from the first table and the id and name from the second table in the same order.

Answer

select Id,name,city from employeesGH
union
select id,name from employeesGH1
* this will result in error as no. of colums should be equal in both select statements.

4. Fetch a resultant table that has id and city from the first table and name and id from the second table in the same order.

Answer

select name,city,Id from employeesGH
union
select id,name,city from employeesGH1
* this will result in error as the datatype of id and city and name and city is not same.

5. Fetch a resultant table that has the id, name, and city from the first table and the id, city, and name from the second table in the same order.

Answer

select id,[name],city from employeesGH
union
select id,city,[name] from employeesGH1
this will not result in error as the datatype of city and name is same but it will not be the proper result.

Union vs Union All

  • Union removes duplicate rows, whereas Union All does not
  • union use distinct sorts to remove duplicates which makes it faster than Union All

Note. Number of columns, Data types, and order of columns in the select statement should be the same.

Summary

Union and Union all -> Combines the result set of two or more select Query.


Similar Articles