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 −

• (2) table is DEPARTMENT as follows −

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:

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:

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.