SQL UNION: Combining and Comparing Data from Multiple Tables

Welcome to our comprehensive guide on the SQL UNION operator! This tutorial is designed for both beginners and intermediate learners, so let's dive right in.

What is SQL UNION? 🎯

In SQL, the UNION operator is used to combine the results of two or more SELECT statements. It allows us to perform comparisons and aggregations across multiple tables, making it a powerful tool for data analysis.

Basic Syntax 📝

The basic syntax for using UNION is simple:

sql

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

In this example, column1 and column2 are the columns you want to combine from table1 and table2, respectively.

Union All vs Union ✅

It's important to note the difference between UNION ALL and UNION. UNION ALL returns all records from each SELECT statement without removing duplicates, while UNION removes any duplicate records.

Practical Example 💡

Let's consider two tables, Students and Teachers, with a common column Name.

sql

CREATE TABLE Students (
    ID INT,
    Name VARCHAR(50)
);

CREATE TABLE Teachers (
    ID INT,
    Name VARCHAR(50)
);

INSERT INTO Students VALUES (1, 'Alice');
INSERT INTO Students VALUES (2, 'Bob');
INSERT INTO Students VALUES (3, 'Charlie');

INSERT INTO Teachers VALUES (4, 'David');
INSERT INTO Teachers VALUES (5, 'Alice');

Now, let's use the UNION operator to combine the names from both tables:

sql

SELECT Name FROM Students
UNION
SELECT Name FROM Teachers;

This will return:

Alice
Bob
Charlie
David

Notice that Alice is only listed once, even though she appears in both tables. If we used UNION ALL, Alice would appear twice in the result set.

Advanced Example 🎯

Let's say we have a table Orders with columns OrderID, Product, and Price. We want to find all unique products and their total price.

sql

CREATE TABLE Orders (
    OrderID INT,
    Product VARCHAR(50),
    Price DECIMAL(10, 2)
);

INSERT INTO Orders VALUES (1, 'Laptop', 800);
INSERT INTO Orders VALUES (2, 'Laptop', 850);
INSERT INTO Orders VALUES (3, 'Phone', 500);
INSERT INTO Orders VALUES (4, 'Phone', 550);
INSERT INTO Orders VALUES (5, 'Tablet', 300);
INSERT INTO Orders VALUES (6, 'Tablet', 320);

To achieve this, we can use the UNION operator in combination with the COUNT(*) and SUM(Price) functions:

sql

SELECT Product, COUNT(*) AS Quantity, SUM(Price) AS TotalPrice FROM Orders
GROUP BY Product
UNION
SELECT Product, 0 AS Quantity, -1 AS TotalPrice FROM Orders;

This query will return:

Product | Quantity | TotalPrice
------- | ------- | -----------
Laptop  |       3 |    1650.00
Phone   |       2 |    1050.00
Tablet  |       2 |    620.00

This result set tells us the unique products, their quantity, and total price. The final row with 0 and -1 is used to ensure the product count includes every unique product.

Quiz 🎯

By the end of this tutorial, you should have a solid understanding of the SQL UNION operator and be able to use it effectively in your own projects. Happy coding! 🚀