In SQL, The "UNION" operator is used to concat 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.
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: Removes duplicate `RECORDS` from the result set.
UNION ALL: allow duplicates records as well in data sets.
SELECT column1, column2 FROM tableName1 WHERE condition UNION SELECT column1, column2 FROM tableName2 WHERE condition;
column1, column2: The columns you want to select from the tables.
tableName1, tableName2: The tables from which you are selecting data.
WHERE condition: Optional, specifies a condition for each SELECT statement.
The "UNION" operator is used to combine the result sets of two "SELECT" statements.
SELECT employee_id, emp_name FROM employees WHERE department = 'IT' UNION SELECT employee_id, emp_name FROM employees WHERE department = 'Finance';
The resulting set will contain unique combinations of "employee_id" and "emp_name" from the 'IT' department and the 'Finance' department.
we can use UNION operator to combine the result sets of two different tables.
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".
We can use UNION ALL Operator to include duplicate rows in our result sets.
SELECT column1, column2 FROM tableName1 UNION ALL SELECT column1, column2, FROM tableName2;
The `UNION ALL` operator will include duplicate products from new_arrival_product and products tables.
SELECT product_name FROM products UNION ALL SELECT product_name FROM new_arrival_product;