SQL - Union vs Join

SQL: UNION vs. JOIN

In SQL, both the UNION and JOIN clauses are used to combine data from two or more tables. However, they do so in fundamentally different ways. Understanding their distinction is crucial for effective database querying and data manipulation.

The simplest way to think about the difference is: UNION appends rows from one table to another (vertical combination), while JOIN combines columns from different tables based on a related column between them (horizontal combination).

Working of UNION and UNION ALL

The UNION operator is used to combine the result-set of two or more SELECT statements. It works by appending rows from one query result to another. However, for a UNION to work, certain conditions must be met:

• Each SELECT statement within the UNION must have the same number of columns.
• The columns must also have similar data types.
• The columns in each SELECT statement must be in the same order.

There are two variants of the UNION operator:

• UNION: This operator combines the results and then performs a distinct sort to remove any duplicate rows. This operation can be resource-intensive.
• UNION ALL: This operator combines the results but includes all duplicate rows. It is significantly faster than UNION because it doesn’t need to check for duplicates. In most cases where you are certain the combined datasets are unique or duplicates are acceptable, UNION ALL is preferred for performance.

Syntax

SELECT column1, column2 FROM table1
UNION -- or UNION ALL
SELECT column1, column2 FROM table2;

Example: Combining Active and Archived Users

Imagine you have two tables, ActiveUsers and ArchivedUsers, with identical structures. We want to get a single list of all user emails.

First, the ActiveUsers table:

CREATE TABLE ActiveUsers (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO ActiveUsers (user_id, user_name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com');

Next, the ArchivedUsers table, which might contain a user who is also still active (for demonstration purposes):

CREATE TABLE ArchivedUsers (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO ArchivedUsers (user_id, user_name, email) VALUES
(2, 'Bob', 'bob@example.com'),
(3, 'Charlie', 'charlie@example.com');

Now, let’s use UNION to get a unique list of emails:

SELECT email FROM ActiveUsers
UNION
SELECT email FROM ArchivedUsers;

Output (UNION)

The result will have duplicates removed. Note that ‘bob@example.com’ appears only once.

email
alice@example.com
bob@example.com
charlie@example.com

Using UNION ALL will produce a different result:

SELECT email FROM ActiveUsers
UNION ALL
SELECT email FROM ArchivedUsers;

Output (UNION ALL)

The result includes all rows from both queries.

email
alice@example.com
bob@example.com
bob@example.com
charlie@example.com

Working of JOIN

The JOIN clause is used to combine rows from two or more tables based on a related column between them. This is a horizontal combination, creating a new, wider result set that includes columns from all joined tables.

There are several types of JOINs:

• INNER JOIN: Returns records that have matching values in both tables. This is the most common type of join.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL on the left side.
• FULL OUTER JOIN: Returns all records when there is a match in either the left or right table. It combines the functionality of both LEFT and RIGHT JOINs.

Syntax (INNER JOIN)

The modern, explicit JOIN syntax is highly recommended over the old comma-based syntax.

SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

Example: Combining Customers and Orders

Let’s create a Customers table and an Orders table.

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO Customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO Orders VALUES (101, 1, '2023-10-26', 150.00), (102, 2, '2023-10-26', 75.50), (103, 1, '2023-10-27', 25.00);

We can join these tables to see which customer placed which order. We use INNER JOIN because we only want to see customers who have actually placed an order.

SELECT
    c.customer_name,
    o.order_id,
    o.amount
FROM
    Customers c
INNER JOIN
    Orders o ON c.customer_id = o.customer_id;

Output

The result combines columns from both tables. Notice that ‘Charlie’, who has no orders, does not appear in the result.

customer_name	order_id	amount
Alice	101	150.00
Bob	102	75.50
Alice	103	25.00

UNION vs. JOIN: Key Differences

Aspect	UNION / UNION ALL	JOIN
Purpose	Appends rows from multiple tables (vertical combination).	Combines columns from multiple tables based on a relational key (horizontal combination).
Result Set	The number of columns remains the same; the number of rows increases.	The number of columns increases (sum of columns from tables, unless specified); the number of rows depends on the matches.
Column Requirements	Tables must have the same number of columns with compatible data types.	Tables must have at least one common, joinable column (the key). The data types of other columns can be different.
Duplicate Handling	UNION removes duplicate rows. UNION ALL includes them.	JOIN operations can produce duplicate values if the join condition matches multiple rows.
Syntax	Placed between two SELECT statements.	Used in the FROM clause with an ON condition to specify the relationship.
Use Case	Consolidating data from tables with the same structure, e.g., combining Sales_Q1 and Sales_Q2.	Querying related data from different entities, e.g., retrieving Users and their Profiles.