Introduction to SQL Union

Introduction

SQL is a crucial 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 distinct desk. We are going to delve into the fundamentals of UNION and discover other 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 fundamental syntax and circumstances for SQL UNION.
• Discover sensible examples and pattern information.
• Uncover the best way to type and filter mixed outcomes.
• Perceive greatest practices for utilizing SQL UNION.
• Evaluate SQL UNION with SQL JOIN.

What’s SQL UNION?

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

Primary Syntax

Under is the fundamental syntax for the SQL UNION of two outcome 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. Be aware 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 closing outcome set, we’ve sorted the information by wage. Therefore, you should utilize ORDER BY to type information within the closing outcome set. Be aware which you could solely order based mostly 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 information that fulfill the WHERE clause are current. The WHERE clause will aid you filter information as your situation in SQL UNION.

Greatest Practises and Concerns

• Column Order: Make 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 completely different functions.

Conclusion

SQL UNION offers you numerous options for combining outcomes from SELECT statements from the identical or completely different tables. With an excellent understanding of UNION statements and their habits, we must always be capable to successfully handle and manipulate information. UNION operators simplify the method and make it simple to mix outcomes.

Additionally Learn: Most Essential SQL Queries for Inexperienced persons

Continuously Requested Questions