UNION operator

The UNION operator in SQL is used to combine the result of two or more SELECT statements into a single result set. It allows you to retrieve data from multiple tables or queries and present it as a single result set.

Usage

The UNION operator is used when you want to combine the result sets of two or more SELECT statements into a single result set. The result set of a UNION operation includes all the rows from the individual SELECT statements without any duplicates.

Syntax

The basic syntax of the UNION operator is as follows:

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

The SELECT statements within the UNION operator can have different columns, but the number of columns and their data types must be compatible.

Functionality

The UNION operator works by concatenating the result sets of the individual SELECT statements. It removes any duplicate rows from the final result set.

When using the UNION operator, the columns in the SELECT statements must be in the same order. The data types of the corresponding columns must also be compatible, otherwise, an error will occur.

The UNION operator also requires that the number of columns in each SELECT statement is the same. If the number of columns is different, an error will be thrown.

Example

Let's consider two tables, "Customers" and "Suppliers", and we want to combine the result of two SELECT statements:

```
SELECT customer_name, city
FROM Customers
UNION
SELECT supplier_name, city
FROM Suppliers;
```

This query will return a result set that includes customer names and cities from the "Customers" table and supplier names and cities from the "Suppliers" table. Any duplicate rows will be removed from the final result set.

In conclusion, the UNION operator in SQL is used to combine the result of two or more SELECT statements into a single result set. It allows you to retrieve data from multiple tables or queries and present it as a unified result.