SQL | Union Clause

The UNION clause is used in SQL to combine the result sets of two or more SELECT statements. However, for the UNION to work, there are some conditions that must be met:

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

Key Points:

Distinct Rows: By default, UNION removes duplicate rows. If you want to keep duplicates, you can use UNION ALL.
Sorting: If you want to sort the combined result set, you can use the ORDER BY clause at the end of the UNION.

Example:

Suppose you have two tables, Customers and Suppliers, and you want to create a list of all cities where you have either a customer or a supplier:

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

If you want to keep duplicates:

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

Performance Consideration:

UNION does an additional work to remove duplicates, which can be a performance hit especially on large datasets. If you're certain that there won't be duplicates (or if duplicates don't matter), using UNION ALL is more efficient since it doesn't need to check for duplicates.
Remember to always evaluate the need for UNION in the context of your specific data and query requirements. Sometimes, alternative approaches like joins or subqueries might be more efficient or appropriate for the desired outcome.

Remember to test and optimize UNION queries, especially when dealing with large datasets or complex operations.

How to use UNION in SQL:
- UNION is used to combine the result sets of two or more SELECT statements.
```
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
```
Set operations in SQL with UNION:
- UNION performs a set operation, removing duplicate rows from the combined result set.
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```
UNION vs. UNION ALL in SQL:
- UNION ALL includes all rows from the combined result sets, including duplicates.
```
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
```
Using UNION with multiple columns in SQL:
- UNION can be used with multiple columns, and column data types must match.
```
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
```
Handling NULL values with UNION in SQL:
- UNION handles NULL values, and columns with NULL in one SELECT are matched with NULL in the other.
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```
UNION operator with subqueries in SQL:
- UNION can be used with subqueries to combine the results of complex queries.
```
(SELECT column1 FROM table1)
UNION
(SELECT column1 FROM table2);
```
Alternatives to UNION in SQL:
- Use UNION alternatives like UNION ALL, JOIN, or IN depending on the specific requirement.
```
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
```
Set operations and duplicates with UNION:
- UNION eliminates duplicate rows from the combined result set.
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```
UNION vs. INNER JOIN in SQL:
- UNION combines rows vertically, while INNER JOIN combines rows horizontally.
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```
UNION vs. CONCATENATE in SQL:
- UNION is used for combining result sets, while concatenation refers to combining strings.
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```
Combining UNION with other operators in SQL:
- Combine UNION with other operators like INTERSECT or EXCEPT for more complex queries.
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2
INTERSECT
SELECT column1 FROM table3;
```
Practical use cases for UNION in SQL:
- UNION is useful for combining data from multiple tables or queries where duplicate rows need to be removed.
```
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
```