Introduction to PostgreSQL (Coding Style) Part-3
Tags: postgres, apacheage, database, agenssql
Published: 2023-10-05 18:57:49

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](https://dev.to/vkt1271/introduction-to-postgresql-coding-style-part-4-4m61).