SQL UNION – Combine Multiple Tables Using UNION

In his tutorial, you will know the complete overview of SQL Union with an Example. This tutorial will be also very helpful to get more information related to it such as union command, syntax, usage & steps to use union so on.

For a better understanding, We have taken two sets like set_1 & set_2 are shown in the figure below. set_1 has four values like 1, 2, 3, and set_2 has six values like 3, 4, 5, 6, 7, 8. Both sets have two duplicate/ common values 3, 4. When we combine both sets, we will get a final result_set with distinct values like 1, 2, 3, 4, 5, 6, 7, 8. Because UNION always gives result sets with distinct values by combining more than one set.

The above example is enough to understand the UNION concept. Now, you will know more about it in detail from the next steps. So, continue reading this complete tutorial.

SQL UNION Operator

UNION  is a set operator in the SQL server. It combines result sets of more than one select statement into a single result set. Even It selects only unique values and leaves duplicate values.

Read Also –

SQL Interview Questions and Answers

Creating a Table in SQL

Rules for using Union –

UNION operation must satisfy the following rules

• Each SELECT Settlement must be declared with the same number of columns.
• All the columns in each SELECT statement must be in the same order.
• Data Types of corresponding columns of each SELECT statement must be the same.
• Corresponding columns of each select statement must be with the same expression and aggregate function.

UNION – Syntax

It has the following syntax –

SELECT column_name1, coumn_name2, column3, column_name4 FROM table1
UNION
SELECT column_name1, coumn_name2, column3, column_name4 FROM table2;
UNION
SELECT column_name1, coumn_name2, column3, column_name4 FROM table3;

The above syntax is only for understanding how to combine more than two tables. I have created this syntax for combining the three tables with four columns. You can combine more tables with more columns as your requirements. But remember that you must declare a UNION operator between two select statements.

UNION – Usage

UNION is used to combine more than one table into a single table with distinct values using SELECT statements.

Important Points – 

• Datatypes of the corresponding column in each table must be the same.
• The number of columns in each table may be the same or different.
• Corresponding column names of each table may be the same or different.
• The number of records in each table may be the same or different.
• Each table may have distinct values, duplicate values or combination of both values

For your better understanding, You will have to read the example in the next step

UNION – Example

Suppose that we have two tables likestudents & employees that have four columns like, name & city. Now, We will get a new table by combining these tables –

Table Name – students

Here the query to create students table –

CREATE TABLE students
(
 id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
 name varchar(50) DEFAULT NULL,
 city varchar(100) DEFAULT NULL
);

The students’ table contains the following values –

Table Name – employees

Here is the query to create employees table –

CREATE TABLE employees
(
 id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
 name varchar(50) DEFAULT NULL,
 city varchar(100) DEFAULT NULL
);

The students’ table contains the following values –

Statement – 

These select statements will select a name by combining both table students & employees.

SELECT name FROM students
UNION
SELECT name FROM employees;

Output – 

SELECT name FROM students
UNION
SELECT name FROM employees 
ORDER BY name;

SELECT COUNT(id), city
FROM students
UNION
SELECT COUNT(id), city FROM employees
GROUP BY city;

SELECT name FROM students
UNION
SELECT name FROM employees 
WHERE city='jaipur';

How to Execute SELECT Statement with SQL UNION

You will have to configure the following steps if you want to combine more than two tables using the UNION operator –

• First of all, Make sure that SQL Server is installed on your computer.
• Create a Database
• Create the two tables. You can create more tables as per your requirement.
• Insert the required values in both tables.
• combine both tables using UNION Operator

mysql>CREATE DATABASE my_db;
mysql>USE my_db;
mysql>CREATE TABLE students
      ->(
      ->id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      ->name varchar(50),
      ->city varchar(100)
      ->);
mysql>CREATE TABLES employees
     ->(
     ->name varchar(50),
     ->city varchar(100)
     ->);
mysql>INSERT INTO students(id, name, city)
     ->VALUES(1, 'Amit Kumar', 'Patna'),
     ->(2, 'Sunil Kumar', 'New Delhi'),
     ->(3, 'Rapson', 'Jaipur'),
     ->(4, 'Mr. Donald', 'Noida');
mysql>INSERT INTO employess(id, name, city)
     ->VALUES(1, 'Rahul Kumar', 'Kanpur'),
     ->(2, 'Sunil Kumar', 'New Delhi'),
     ->(3, 'Mamta Kumari', 'Patna'),
     ->(4, 'Sonali Mishra', 'Locknow'),
     ->(5, 'Avnish Mishra', 'Noida'),
     ->(6, 'ranjeet Singh', 'Chennai'),
     ->(7, 'Sunil Kumar', 'Jaipur'),
     ->(8, 'Mr. Donald', 'Noida'),
     ->(9, 'Kavita Kumari', 'Chennai'),
     ->(10, 'Mr. Donald', 'Noida');
mysql>SELECT name FROM students
     ->UNION
     ->SELECT name FROM employees;

Tutorial Summary

You have known complete information about the SQL UNION operator with the best example. Even You have learned to use a UNION with two tables. Now you can use it with more tables according to your project requirement

If you have any questions related to SQL, Kindly ask me through the comment box. Even you can suggest sharing another web development tutorial.

Thanks for giving time to this tutorial. Keep visiting my blog to learn more & become an expert in the coding field.