Introduction to PostgreSQL (Coding Style) Part-3

This blog aims to assist you in understanding the concepts of PostgreSQL with complete coding as query language.

PostgreSQL SQL commands

Note: Make sure you have Postgres installed on your system to proceed to this tutorial.

PostgreSQL - UNIONS Clause

Union

• The PostgreSQL UNION operator merges the results of multiple SELECT statements into a single result set while removing duplicate rows.

• The SELECT statements in a UNION must have matching column count, data types, and order, but their lengths can differ.

Example:

• Consider the following two tables, (1) COMPANY table is as follows −

id	name	age	address	salary
1	Paul	32	California	20000
2	Allen	25	Texas	15000
3	Teddy	23	Norway	20000
4	Mark	25	Rich-Mond	65000
5	David	27	Texas	85000
6	Kim	22	South-Hall	45000
7	James	24	Houston	10000

• (2) table is DEPARTMENT as follows −

id	dept	emp_id
1	IT Billing	1
2	Engineering	2
3	Finance	7
4	Engineering	3
5	Finance	4
6	Engineering	5
7	Finance	6

Syntax:

`SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]`

Query:

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

Result:

emp_id	name	dept
5	David	Engineering
6	Kim	Finance
2	Allen	Engineering
3	Teddy	Engineering
4	Mark	Finance
1	Paul	IT Billing
7	James	Finance

The UNION ALL Clause

• The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

Note: The same rules that apply to UNION apply to the UNION ALL operator as well.

Syntax:

`SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]`

Query:

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;

Result:

emp_id	name	dept
1	Paul	IT Billing
2	Allen	Engineering
7	James	Finance
3	Teddy	Engineering
4	Mark	Finance
5	David	Engineering
6	Kim	Finance
1	Paul	IT Billing
2	Allen	Engineering
7	James	Finance
3	Teddy	Engineering
4	Mark	Finance
5	David	Engineering
6	Kim	Finance

---

I hope this blog has helped you understand the concepts of PostgreSQL with complete coding as a query language.

Check out a summary of Part-4.