DEV Community

Cover image for Introduction to PostgreSQL (Coding Style) Part-3
Vinay Kumar Talreja
Vinay Kumar Talreja

Posted on

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.

Top comments (0)