UNION - (PostgreSQL Advance)

The UNION operator in PostgreSQL is a set operator that is used to combine the results of two or more SELECT queries into a single result set. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the UNION operator in PostgreSQL.

Syntax

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

Example

Let's take a look at an example of using the UNION operator in PostgreSQL.

SELECT city, state
FROM customers
WHERE country = 'USA'
UNION
SELECT city, state
FROM suppliers
WHERE country = 'USA';

In this example, we want to retrieve a list of all cities and states from both the customers and suppliers tables where the country is 'USA'. We can use the UNION operator to combine the results of these two SELECT queries into a single result set.

Explanation

In the above example, we have two SELECT queries separated by the UNION operator. The first query retrieves all of the cities and states from the customers table where the country is 'USA'. The second query retrieves all of the cities and states from the suppliers table where the country is 'USA'. The UNION operator combines the results of these two queries into a single result set.

It's important to note that the UNION operator removes duplicates from the final result set.

Use

The UNION operator in PostgreSQL is useful for combining the results of two or more SELECT queries into a single result set. This can be useful for a number of scenarios, such as combining data from two different tables or selecting information from two different parts of the same table.

Important Points

• The UNION operator combines the results of two or more SELECT queries into a single result set.
• The SELECT statements within the UNION must have the same number of columns.
• The column names in the final result set are determined by the column names in the first SELECT statement.
• The UNION operator removes duplicates from the final result set.
• The UNION operator can be used with the ORDER BY clause to sort the final result set.

Summary

In this tutorial, we discussed the UNION operator in PostgreSQL. We covered the syntax, example, output, explanation, use, important points, and summary of the UNION operator. With this knowledge, you can now use the UNION operator to combine the results of two or more SELECT queries into a single result set in your PostgreSQL queries.