UNION

The UNION operator is used to combine the results of two or more SELECT statements into a single result set, removing duplicates by default. It allows you to retrieve data from multiple tables or queries with similar structures. لازم يبقا نفس عدد الـ Columns ولو مش نفس العدد لازم أختار الحاجات المتشابهة بس They must have the same number of columns and compatible data types

---

Key Points:

1. Combines Rows: Combines the rows from multiple SELECT queries into one result set.
2. Duplicate Removal: By default, UNION eliminates duplicate rows. Use UNION ALL to include duplicates.
3. Column Alignment:
   • The number of columns and their data types in each SELECT query must match.
   • Column names are taken from the first SELECT query.

---

Syntax:

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

With UNION ALL (Allow Duplicates):

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

---

Examples:

1. Basic UNION Example

Combine employee names from two tables (Employees_US and Employees_UK):

SELECT employee_name
FROM Employees_US
UNION
SELECT employee_name
FROM Employees_UK;

Result:

employee_name
John
Sarah
Alice
Mike

2. UNION with Duplicate Values

To include duplicates, use UNION ALL:

SELECT employee_name
FROM Employees_US
UNION ALL
SELECT employee_name
FROM Employees_UK;

Result:

employee_name
John
Sarah
Alice
Mike
John
Sarah

---

3. UNION with Different Tables

Combine Product names and Service names into a single list:

SELECT product_name AS item_name
FROM Products
UNION
SELECT service_name AS item_name
FROM Services;

Result:

item_name
Laptop
Printer
Consulting
Maintenance

---

4. UNION with WHERE Clause

Combine employees who work in the “HR” department across two tables:

SELECT employee_name
FROM Employees_US
WHERE department = 'HR'
UNION
SELECT employee_name
FROM Employees_UK
WHERE department = 'HR';

---

Use Cases:

1. Combining Results from Multiple Tables:
   • Consolidate data spread across different tables.
2. Merging Data with Filters:
   • Apply different WHERE conditions in each query.
3. Avoid Duplicates with UNION:
   • Removes redundant rows automatically.
4. Allow Duplicates with UNION ALL:
   • Useful for performance when duplicates don’t matter.

---

Differences Between UNION and UNION ALL:

Feature	UNION	UNION ALL
Duplicates	Removes duplicates.	Includes duplicates.
Performance	Slower (removes duplicates).	Faster (no duplicate removal).
Use Case	When duplicate rows are irrelevant.	When duplicates are acceptable.

---

Summary:

• UNION: Combines results from multiple queries, removing duplicates.
• UNION ALL: Combines results without removing duplicates, improving performance.
• Key Requirements: Matching column count and compatible data types.
• Common Use: Consolidating data from multiple tables or queries into a unified view.