UNION in DBMS

The UNION operator in DBMS is used to combine the result sets of two or more SELECT statements. However, it will only select distinct values. The UNION operator selects only distinct values by default. If you want to allow duplicate values, you can use UNION ALL.

Here's the basic syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

For instance, let's assume we have two tables, Customers and Suppliers, and we want to find all cities where we have either a customer or a supplier. If the Customers table has a column City and the Suppliers table also has a column City, we can use a UNION to get a list of all cities:

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

This would return a list of cities, with each city listed only once, even if it appears in both the Customers and Suppliers tables.

Remember, the number and order of the columns, as well as the data types of the corresponding columns, must be the same in all the SELECT statements that you're combining with UNION.

If you wanted to include duplicates, you would use UNION ALL:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

In this case, a city would be listed once for every time it appears in either the Customers or Suppliers table.