Introduction to SQL Union

Introduction

SQL is a vital device that each information scientist and information analyst ought to know. Its UNION assertion permits you to mix the outcomes of two or extra SQL SELECT statements. The SELECT command could also be on the identical desk or a special desk. We’ll delve into the fundamentals of UNION and discover alternative ways to make use of it. Moreover, we’ll use some pattern tables to experiment with UNION instructions.

Overview: 

• Be taught concerning the SQL UNION assertion.
• Achieve an understanding of the essential syntax and circumstances for SQL UNION.
• Discover sensible examples and pattern information.
• Uncover find out how to type and filter mixed outcomes.
• Perceive finest practices for utilizing SQL UNION.
• Examine SQL UNION with SQL JOIN.

What’s SQL UNION?

The SQL UNION combines two or extra SELECT statements, i.e., the end result units we get from SELECT statements. We stack them on one different to get the UNION operation accomplished. There are some circumstances to execute a UNION assertion. Every SELECT assertion throughout the UNION should have the identical variety of columns within the end result units with comparable information sorts. The SQL UNION operator removes duplicate rows from the end result set by default.

Primary Syntax

Beneath is the essential syntax for the SQL UNION of two end result units from two SELECT statements.

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

Creation of Pattern information

CREATE TABLE Workers (
    employee_id INT PRIMARY KEY,
    identify VARCHAR(50),
    division VARCHAR(50),
    wage DECIMAL(10, 2)
);

INSERT INTO Workers (employee_id, identify, division, wage)
VALUES 
(1, 'Alice', 'HR', 60000.00),
(2, 'Bob', 'IT', 75000.00),
(3, 'Charlie', 'Finance', 70000.00),
(4, 'Dana', 'IT', 80000.00);

It will create our first desk of Workers and insert pattern information into our desk.

CREATE TABLE Contractors (
    contractor_id INT PRIMARY KEY,
    identify VARCHAR(50),
    division VARCHAR(50),
    hourly_rate DECIMAL(10, 2)
);

INSERT INTO Contractors (contractor_id, identify, division, hourly_rate)
VALUES 
(1, 'David', 'IT', 50.00),
(2, 'Eve', 'Finance', 45.00),
(3, 'Frank', 'HR', 40.00),
(4, 'Grace', 'IT', 55.00);

It will create our second desk, Contractors, and insert pattern information into our desk.

Primary Utilization of SQL UNION

Combining information from Workers and Contractors

SELECT *
FROM Workers
UNION
SELECT *
FROM Contractors;

The column identify comes from the Worker desk. Notice that SQL UNION takes the column names from the primary SELECT assertion.

Combining information and Sorting the outcomes

SELECT identify, division
FROM Workers
UNION
SELECT identify, division
FROM Contractors
ORDER BY wage;

In our last end result set, we’ve got sorted the data by wage. Therefore, you should utilize ORDER BY to type data within the last end result set. Notice you could solely order primarily based on the chosen columns, not these not chosen.

Utilizing UNION with WHERE clause

SELECT identify, division
FROM Workers
WHERE division="IT"
UNION
SELECT identify, division
FROM Contractors
WHERE division="IT";

Within the above picture, solely the chosen columns and data that fulfill the WHERE clause are current. The WHERE clause will assist you filter data as your situation in SQL UNION.

Finest Practises and Concerns

• Column Order: Be sure that the columns in every SELECT assertion are in the identical order and have appropriate information sorts.
• Debugging: When troubleshooting, run every SELECT assertion independently to confirm that it returns the anticipated outcomes earlier than combining it with SQL UNION.

Comparability between SQL JOIN and SQL UNION

JOIN and UNION are each used to mix information from a number of tables. They each have totally different functions.

	JOIN	UNION
Function	JOIN combines columns from two or extra tables primarily based on a associated column between them.	Combines the outcomes of two or extra SELECT statements right into a single end result set, stacking them vertically.
Construction	Merges tables horizontally by including columns from the second desk to the columns of the primary desk.	Merges tables vertically by including rows from the second SELECT assertion to the rows of the primary SELECT assertion.

Conclusion

SQL UNION offers you various options for combining outcomes from SELECT statements from the identical or totally different tables. With understanding of UNION statements and their conduct, we should always be capable of successfully handle and manipulate information. UNION operators simplify the method and make it simple to mix outcomes.

Additionally Learn: Most Essential SQL Queries for Newbies

Regularly Requested Questions