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 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 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to create ‘share on LinkedIn’ link using just HTML?HTMLSQL Inner JoinSQL/MySQLLIKE and ILIKE Operators in PostgresPostgresAdvanced Array Methods in JavaScript (with examples)JavaScriptHow to Use password_hash and password_verify to Secure Your User’s Data (Especially Passwords)?PHPWhat is iFrame in HTML? Why do we need it?HTML