Introduction to PostgreSQL (Coding Style) Part-2

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:

• Some data in the Department Table:

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:

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:

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:

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:

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:

---

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.