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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #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
You might also like these
What’s new in Constructor in PHP 8?PHPHow to import a CSS file using PHP code and not HTML code?PHPUse inline if to make a shorter conditional syntax in ReactReactWordPress: How to setup and get values from an ACF options page?WordPressHow to create a cross-browser smooth scrolling with jQuery?jQueryHow to make a curtain slider using jQuery and CSS?CSS