YogeshChauhan . com

UNION and UNION ALL in Postgres

in Postgres on July 25, 2020

The UNION operator is used to combine the result-set of two or more SELECT statements with the same number of columns and same column data types. The columns in each SELECT statement must also be in the same order.

UNION

Syntax:


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

Examples:

I am using the database for all examples. It is available on my Github public repo

UNION Example


SELECT city FROM customers
UNION 
SELECT city FROM suppliers
ORDER BY city;

/Output
city
"Aachen"
"Albuquerque"
"Anchorage"
"Ann Arbor"
...
...
93 rows

If some customers or suppliers have the same city then each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

UNION Example with WHERE


SELECT city FROM customers WHERE country='Germany'
UNION 
SELECT city FROM suppliers WHERE country='Germany'
ORDER BY city;

//Output
city
"Aachen"
"Berlin"
"Brandenburg"
"Cunewalde"
...
...
13 rows

UNION ALL

Syntax:


SELECT column(s) FROM table1
UNION ALL
SELECT column(s) FROM table2;

Examples:

UNION ALL Example


SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers
ORDER BY city;

/Output
city
"Aachen"
"Albuquerque"
"Anchorage"
"Ann Arbor"
...
...
120 rows

UNION ALL Example with WHERE


SELECT city FROM customers WHERE country='Germany'
UNION ALL
SELECT city FROM suppliers WHERE country='Germany'
ORDER BY city;

//Output
city
"Aachen"
"Berlin"
"Brandenburg"
"Cunewalde"
...
...
14 rows
amazon

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

What is React? Learn the basicsReactHow to position an image on top of another image using CSS?CSSMicroservices vs Monolithic ArchitectureMiscNATURAL JOIN in PostgresPostgresWordPress: How to add a Search Icon in Menus with toggle effect using jQuery?jQueryHow to create a sidebar using pure CSS?CSS