MySQL UNION Clause

The `UNION` clause in MySQL is used to combine the result sets of two or more `SELECT` queries into a single result set. It ensures that the results are distinct, eliminating duplicate rows.

Usage

The `UNION` clause is applied when you need to merge the outputs from multiple `SELECT` statements, which must have the same number of columns and compatible data types. It simplifies the retrieval of data from multiple queries.

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
ขับเคลื่อนโดย

In this syntax, `UNION` is used to concatenate the results of the `SELECT` statements from `table1` and `table2`.

Examples

1. Basic UNION

SELECT city
FROM customers
UNION
SELECT city
FROM suppliers;
ขับเคลื่อนโดย

This example combines cities from both the `customers` and `suppliers` tables, removing any duplicates.

2. UNION with Different Filters

SELECT product_name
FROM products
WHERE stock > 50
UNION
SELECT product_name
FROM discontinued_products
WHERE discontinued_date > '2023-01-01';
ขับเคลื่อนโดย

Here, the union combines product names from the `products` table where stock is greater than 50 with product names from the `discontinued_products` table that were discontinued after January 1, 2023.

3. UNION ALL to Include Duplicates

SELECT employee_id, name
FROM full_time_employees
UNION ALL
SELECT employee_id, name
FROM part_time_employees;
ขับเคลื่อนโดย

In this case, `UNION ALL` includes all duplicates, showing every employee from both `full_time_employees` and `part_time_employees` tables.

4. UNION with ORDER BY

SELECT department
FROM departments
UNION
SELECT department
FROM archived_departments
ORDER BY department;
ขับเคลื่อนโดย

This example demonstrates the use of `ORDER BY` to sort the combined results of departments from both `departments` and `archived_departments`.

Tips and Best Practices

• Ensure column compatibility. The number and data types of columns in each `SELECT` statement should match. Mismatched columns can lead to errors.
• Use UNION ALL for performance. If duplicates are acceptable, use `UNION ALL` as it is faster since it does not remove duplicates.
• Order results appropriately. Use `ORDER BY` only once after the final `SELECT` statement to sort the entire union result.
• Optimize individual queries. Ensure each `SELECT` query is optimized for performance before using `UNION`.
• Consider dataset size. Larger datasets can impact performance; consider breaking down queries or using indexes for optimization.

Error Handling

Be aware of potential errors or warnings if there is a mismatch in the number of columns or data types between the `SELECT` statements. Ensure column compatibility to avoid these issues.