Introduction to PostgreSQL (Coding Style) Part-2
Tags: postgres, apacheage, database, agenssql
Published: 2023-10-05 18:13:17

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.

**Introduction to Joins**

- The PostgreSQL Joins clause is used to combine records from **two or more tables** in a database. 

- A **JOIN** is a means for combining fields from two tables by using values common to each.

- Before we proceed, let us consider two tables, **COMPANY** and **DEPARTMENT**. We already have seen INSERT statements populate the COMPANY table. So just let us assume the list of records available in the COMPANY table:

`id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13`

- Some data in the **Department** Table:

`id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7`

**PostgreSQL - JOINS**

> **The CROSS JOIN**

- A CROSS JOIN combines every row from the first table with every row from the second table, resulting in a table with x+y columns, but should be used cautiously due to the potential for generating massive tables.

**Syntax:** `SELECT ... FROM table1 CROSS JOIN table2 ...`

**Query:** `SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;`

**Result:**

`emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance` 

> **The INNER JOIN**

- An INNER JOIN combines rows from two tables based on a specified condition, creating a result table with columns from both tables when the condition is met for each pair of rows.

- An INNER JOIN is the most common type of join and is the default type of join. You can use the **INNER** keyword optionally.

**Syntax:** `SELECT table1.column1, table2.column2...
FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;`

**Query:** `SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;`

**Result:** 

`emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering`

> **The LEFT OUTER JOIN**

- A LEFT OUTER JOIN combines rows from two tables based on a specified condition, including all rows from the left table and matching rows from the right table, filling in null values for unmatched rows from the right table.

**Syntax:** `SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...`

**Query:** `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
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |`

> **The RIGHT OUTER JOIN**

- A RIGHT OUTER JOIN combines rows from two tables based on a specified condition, including all rows from the right table and matching rows from the left table, filling in null values for unmatched rows from the left table.

**Syntax:** `SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...`

**Query:** `SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;`

**Result:** 

`emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance`

> **The FULL OUTER JOIN**

- A FULL OUTER JOIN combines rows from two tables based on a specified condition, including all rows from both tables, filling in null values for unmatched rows in either table.

**Syntax:** `SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...`

**Query:** `SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;`

**Result:** 

`emp_id | name  | dept
--------+-------+---------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |`

----------------------------------------------------------------

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