SQL UNION

SQL UNION is a clause used to combine the results of two or more SELECT statements into a single result set. The result set of a UNION query consists of all the rows returned by each SELECT statement, with duplicates removed.

The syntax for a UNION query is as follows:

SELECT column1, column2, ... FROM table1 WHERE condition1
UNION
SELECT column1, column2, ... FROM table2 WHERE condition2;

Here, column1, column2, and so on represent the columns you want to retrieve from the tables, table1 and table2 are the names of the tables you want to retrieve data from, condition1 and condition2 are optional clauses that specify conditions for filtering data.

The SELECT statements must have the same number of columns, with the same or compatible data types, in the same order. The columns are matched by their positions in the SELECT statements, not by their names.

For example, the following statement retrieves the names of all employees and customers from the "employees" and "customers" tables, respectively:

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

This statement combines the results of two SELECT statements, one retrieving the names of all employees and the other retrieving the names of all customers. The result set contains all the distinct names, with duplicates removed.

You can also use the UNION ALL clause instead of UNION to include duplicates in the result set. This can be useful if you want to combine the results of two SELECT statements that may have some overlapping rows. However, UNION ALL is generally less efficient than UNION, as it requires more processing and memory.