Sql Union

SQL union

Created By: chatGPT

In SQL, the UNION operator is used to combine the results of two or more SELECT statements. It removes duplicate records by default, ensuring that the result set contains unique rows only. For the UNION to work, the SELECT statements must have the same number of columns in the result sets, and the columns must have similar data types.

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

If you want to include duplicate records in your result set, you can use UNION ALL instead of just UNION. The syntax and structure remain the same, but this allows duplicate rows to be part of your final output.

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

Here’s an example utilizing the UNION operator. Let’s say you have two tables: employees and contractors. Both tables contain columns for first_name and last_name. You can combine the lists into a single result.

SELECT first_name, last_name
FROM employees
UNION
SELECT first_name, last_name
FROM contractors;

When working with UNION, it's important to note that the column names in the result set will be taken from the first SELECT statement. If the columns in the SELECT statements have different names, the name of the first referenced column will be used in the output.

SELECT id AS employee_id, first_name
FROM employees
UNION
SELECT id AS contractor_id, first_name
FROM contractors;

You can also sort the resulting set using the ORDER BY clause, but this must be specified after the last SELECT statement.

SELECT column1
FROM table1
UNION
SELECT column1
FROM table2
ORDER BY column1;