UNION vs JOIN

The UNION operator is used in SQL to combine the result sets of 2 or more SELECT statements. However, it only selects distinct values. Each SELECT statement within UNION must have the same number of columns and those columns must be of similar data type. The columns in each SELECT statement must also be in the same order.

The basic syntax of a UNION is as follows:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

If you want to allow duplicate values, you can use UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

UNION vs JOIN

The main difference between UNION and JOIN is that UNION combines rows from different tables, provided they have the same structure (i.e., same number of columns, similar data types), while JOIN combines columns from different tables based on a related column.

JOIN is used to combine rows from two or more tables based on a related column between them. For instance, when you want to list all the orders and the customers who made them, you would JOIN the Orders and Customers tables on the CustomerID column.

UNION, on the other hand, is used to combine the result set of two or more SELECT queries into a single result set. Each SELECT query within the UNION must have the same structure, meaning the same number of columns and data types.

Let’s consider two tables:

Customers_NY (Customers from New York)

CustomerID | CustomerName
---------- | ------------
1          | John
2          | Maria
3          | Steve

Customers_CA (Customers from California)

CustomerID | CustomerName
---------- | ------------
4          | Michael
5          | Jenny
6          | Brad

Example Test Question

Prepare a list of all customers, both from New York and California.

Answer

To solve this, we need to use the UNION operator on the Customers_NY and Customers_CA tables.

Here is the SQL query to achieve this:

SELECT CustomerID, CustomerName FROM Customers_NY
UNION
SELECT CustomerID, CustomerName FROM Customers_CA;

This would return the following result:

CustomerID | CustomerName
---------- | ------------
1          | John
2          | Maria
3          | Steve
4          | Michael
5          | Jenny
6          | Brad

In the result, you can see that we have a list of all customers, both from New York and California. This is exactly what UNION does – it combines the result sets of two or more SELECT statements into a single result set.

This is how a UNION works in SQL. It’s important to remember that while UNION combines rows from different tables, JOIN combines columns from different tables based on a related column.