Yogesh Chauhan's Blog

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

Most Read

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

Recently Posted

#Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface #Jan 14 How to display a student’s individual transcript in Colleague? #Jan 11 How to install PuTTY on a MacOS?
You might also like these
Introduction to components and templates Part 2: Templates and viewsAngularHow to create ‘share on LinkedIn’ link using just HTML?HTMLWhat is React? Learn the basicsReactOOP, Class and Objects Strategies For Beginners (PHP)PHPRelative Length Units in CSSCSSTwo ways we can use colon(:) in Envision BasicEnvision Basic