SQL UNION Operator

 The UNION operator in SQL is used to combine the results of two or more SELECT queries into a single result set. It eliminates duplicate rows, meaning only unique rows will appear in the final result.

Syntax:

SELECT column1, column2, ...
FROM table1
WHERE condition

UNION

SELECT column1, column2, ...
FROM table2
WHERE condition;

Key Points:

• Each SELECT statement must have the same number of columns.
• The columns must have compatible data types.
• The UNION operator removes duplicate rows from the result set. If you want to include duplicate rows, use UNION ALL.

Example 1: Basic UNION:

SELECT FirstName FROM Employees
UNION
SELECT FirstName FROM Customers;

This query combines the first names from the Employees and Customers tables, returning only unique first names.

Example 2: UNION ALL (including duplicates):

SELECT FirstName FROM Employees
UNION ALL
SELECT FirstName FROM Customers;

Here, duplicates will be included, so if a name appears in both the Employees and Customers tables, it will appear twice in the result.

Example 3: Using UNION with different tables:

SELECT ProductName FROM Products
UNION
SELECT ServiceName FROM Services;

This will combine product and service names into a single result set, excluding duplicates.