Example Table Creation and Data Insertion

1. Create the employees_usa table:

CREATE TABLE employees_usa (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

2. Create the employees_uk table:

CREATE TABLE employees_uk (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

3. Insert sample data into the employees_usa table:

INSERT INTO employees_usa (first_name, last_name, salary)
VALUES 
('John', 'Doe', 70000),
('Jane', 'Smith', 80000);

4. Insert sample data into the employees_uk table:

INSERT INTO employees_uk (first_name, last_name, salary)
VALUES 
('Alice', 'Johnson', 75000),
('Jane', 'Smith', 80000);

Performing Union Operations

Combine Data from Two Tables and Remove Duplicates:

SELECT first_name, last_name, salary
FROM employees_usa
UNION
SELECT first_name, last_name, salary
FROM employees_uk;

Combine Data from Two Tables and Include Duplicates:

SELECT first_name, last_name, salary
FROM employees_usa
UNION ALL
SELECT first_name, last_name, salary
FROM employees_uk;

Using ORDER BY with UNION

To sort the combined result set, use the ORDER BY clause after the last SELECT statement:

SELECT first_name, last_name, salary
FROM employees_usa
UNION
SELECT first_name, last_name, salary
FROM employees_uk
ORDER BY salary DESC;

Using UNION with Different Data

If the columns being combined have different names or data types, you might need to use type casting or aliases to ensure compatibility.

Example:

Assume employees_usa has a full_name column and employees_uk has separate first_name and last_name columns. You can use a UNION as follows:

SELECT full_name, salary
FROM employees_usa
UNION
SELECT CONCAT(first_name, ' ', last_name) AS full_name, salary
FROM employees_uk;

This covers the basics of the UNION operator in SQL. By following these examples, you can efficiently combine result sets from multiple SELECT statements into a single result set.