Introduction to SQL Join

Introduction

SQL is an important tool that every data scientist and analyst should know. Your UNION statement allows you to combine the results of two or more SQL SELECT statements. The SELECT command can be on the same table or a different table. We'll delve into the basics of UNION and explore different ways to use it. Additionally, we will use some sample tables to experiment with UNION commands.

General description:

• Learn more about the SQL UNION statement.
• Gain an understanding of basic SQL UNION syntax and conditions.
• Explore practical examples and sample data.
• Find out how to sort and filter combined results.
• Understand best practices for using SQL UNION.
• Compare SQL UNION with SQL JOIN.

What is SQL JOIN?

The SQL UNION combines two or more SELECT statements, that is, the result sets we get from SELECT statements. We stack them on top of each other to perform the UNION operation. There are some conditions for executing a UNION statement. Each SELECT statement within UNION must have the same number of columns in result sets with similar data types. The SQL UNION operator removes duplicate rows from the result set by default.

Basic syntax

Below is the basic syntax for SQL UNION of two result sets from two SELECT statements.

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

Creating sample data

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO Employees (employee_id, name, department, salary)
VALUES 
(1, 'Alice', 'HR', 60000.00),
(2, 'Bob', 'IT', 75000.00),
(3, 'Charlie', 'Finance', 70000.00),
(4, 'Dana', 'IT', 80000.00);

This will create our first Employees table and insert sample data into our table.

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

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

This will create our second table, Contractors, and insert sample data into our table.

Basic use of SQL UNION

Combining employee and contractor data

SELECT *
FROM Employees
UNION
SELECT *
FROM Contractors;

The column name comes from the Employee table. Note that SQL UNION takes the column names from the first SELECT statement.

Combine data and sort the results.

SELECT name, department
FROM Employees
UNION
SELECT name, department
FROM Contractors
ORDER BY salary;

In our final result set, we have sorted the records by salary. Therefore, you can use ORDER BY to sort records in the final result set. Note that you can only sort on selected columns, not unselected ones.

Using UNION with WHERE clause

SELECT name, department
FROM Employees
WHERE department="IT"
UNION
SELECT name, department
FROM Contractors
WHERE department="IT";

In the image above, only the selected columns and records that satisfy the WHERE clause are present. WHERE clause will help you filter records based on their condition in SQL UNION.

Best practices and considerations

• Column order: Make sure the columns in each SELECT statement are in the same order and have compatible data types.
• Depuration: When troubleshooting, run each SELECT statement independently to verify that it returns the expected results before combining it with SQL UNION.

Comparison between SQL JOIN and SQL UNION

JOIN and UNION are used to combine data from multiple tables. They both have different purposes.

Conclusion

SQL UNION offers you several solutions to combine results of SELECT statements from the same or different tables. With a good understanding of UNION statements and their behavior, we should be able to manage and manipulate data effectively. UNION operators simplify the process and make it easy to combine results.

Also Read: Most Important SQL Queries for Beginners

Frequent questions