Union Operator In MySQL

In MySQL, the "UNION" operator is used to combine the result sets of two or more "SELECT" statements into a single result set.

The UNION operator eliminates duplicate rows from the combined result set.

The `UNION ALL` operator will include duplicate records from one or more record sets.

It's important to note that the "UNION" operator requires that the number of columns in the "SELECT" statements must be the same, and the corresponding columns must have compatible data types.

Rules of UNION Operator In MySQL:

Each SELECT statement within the UNION must have the same number of columns.
The columns defined in the SELECT statement while using UNION must also have similar data types.
The columns in each SELECT statement must be in the same order.

UNION vs UNION ALL In MySQL:

UNION: it removes duplicate `RECORDS` from the result set.
UNION ALL: it allows duplicate records as well in data sets.

UNION Operator Syntax:

SELECT columnName1, columnName2
FROM tableName1
WHERE condition
UNION
SELECT columnName1, columnName2
FROM tableName2
WHERE condition;

columnName1, columnName2: The columns we want to select from the tables.
tableName1, tableName2: The tables from which we are selecting data.
WHERE condition: Optional, specifies a condition for each SELECT statement.

Using UNION Operator:

The "UNION" operator is used to combine the result sets of two "SELECT" statements.

Example:

SELECT employee_id, first_name
FROM employees
WHERE department = 'IT'
UNION
SELECT employee_id, first_name
FROM employees
WHERE department = 'Finance';

The resulting set will contain unique combinations of "employee_id" and "first_name" from the 'IT' department and the 'Finance' department.

Using UNION Operator with 2 different tables:

we can use UNION operator to combine the result sets of two different tables.

Example:

SELECT employee_id, designation_id
FROM employees
UNION
SELECT employee_id, designation_id
FROM designation;

The above query result set will contain unique combinations of "employee_id", and "designation_id" from the "employees" and "designation" tables respectively.

UNION ALL Operator:

We can use UNION ALL Operator to include duplicate rows in our result sets.

Syntax:

SELECT columnName1, columnName2
FROM tableName1
UNION ALL
SELECT columnName1, columnName2
FROM tableName2;

Using UNION ALL Operator with 2 different tables:

The `UNION ALL` operator will include duplicate products from `new_arrival_product` and `products` tables.

Example:

SELECT product_name
FROM products
UNION ALL
SELECT product_name
FROM new_arrival_product;

<-- Like

In & Between -->