SQL UNION and UNION ALL statements

Let's dive into the UNION and UNION ALL commands in SQL.

These commands are used to combine the result sets of two or more SELECT statements. However, they do have some important differences.

Here's a basic template for both commands:

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

And:

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

Now, let's look at the differences:

UNION

The UNION operator selects only distinct values by default. If there are duplicate rows between the two SELECT statements, UNION will select only one instance of that row.

UNION ALL

UNION ALL will select all values. This includes duplicates. If a record exists in both table1 and table2, it will appear twice in the result set if you use UNION ALL.

Here are a few more important points about these commands:

The SELECT statements must return the same number of columns, and corresponding columns must be of compatible data types.
The column names for the result set are determined by the first SELECT statement.

Here's an example using a hypothetical database that includes a Customers table and an Orders table. Let's say you want to create a list of all cities where your customers live or where orders have been shipped:

SELECT City FROM Customers
UNION
SELECT City FROM Orders
ORDER BY City;

This would give you a list of cities, sorted in alphabetical order, without any duplicate cities.

But if you wanted to retain the duplicates, you could use UNION ALL instead:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Orders
ORDER BY City;

In this version, if a city appears in both the Customers and Orders tables, it will appear twice in the result set.

Difference Between SQL UNION and UNION ALL:

Description: UNION combines and removes duplicate rows, while UNION ALL combines all rows, including duplicates.

Code Example:

-- UNION: Removes duplicates
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- UNION ALL: Retains duplicates
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

Combining Results with UNION in SQL:
- Description: UNION is used to combine the results of two or more SELECT statements.
- Code Example:
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```
Using UNION to Remove Duplicates in SQL:
- Description: UNION automatically removes duplicate rows from the result set.
- Code Example:
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```

SQL UNION Example Queries:

Code Example 1:

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

Code Example 2:

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

UNION ALL in SQL: Retaining Duplicates:
- Description: UNION ALL includes all rows from the combined SELECT statements, retaining duplicates.
- Code Example:
```
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
```
Combining Multiple SELECT Statements in SQL:
- Description: Both UNION and UNION ALL allow combining the results of multiple SELECT statements.
- Code Example:
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2
UNION ALL
SELECT column1 FROM table3;
```
SQL UNION and Sorting Results:
- Description: You can use ORDER BY to sort the results of a UNION or UNION ALL operation.
- Code Example:
```
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2
ORDER BY column1;
```
SQL UNION with Multiple Tables:
- Description: You can use UNION or UNION ALL to combine results from multiple tables with the same structure.
- Code Example:
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2
UNION ALL
SELECT column1 FROM table3;
```

Practical Examples of SQL UNION and UNION ALL:

Example 1 (UNION):

SELECT product_name FROM electronics
UNION
SELECT product_name FROM appliances;

Example 2 (UNION ALL):

SELECT order_id, order_date FROM online_orders
UNION ALL
SELECT order_id, order_date FROM in_store_orders;