JOINS in PostgreSQL

by

PostgreSQL JOINS

To create a new table with the records as a combination of records from multiple tables, the PostgreSQL Join Query is used.

Types of Joins:

The PostgreSQL database supports mainly five types of Joins. These are:

  • Inner or Simple Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Cross Join

INNER JOIN

The INNER Join being the most popular and common join is often called a SIMPLE Join as it returns all the rows from multiple tables where the join condition is met.
Syntax: 

SELECT expr_1, expr_2, ... expr_n  
FROM table_1   
INNER JOIN table_2  
ON join_predicate;

Parameters:
join_predicate: It is used to specify the joining conditions to be strictly followed for joining.

Example:
Employment Table:

ID STATE RATE
1 A 60
2 B 70
3 C 65
4 D 80
5 E 78

Department Table:

ID NAME PERCENT
1 IT 60
2 SALES 75
3 BANK 50

Query: 

SELECT “EMPLOYMENT”.“ID”, “EMPLOYMENT”.“STATE”, “DEPARTMENT”.“NAME”  
FROM “EMPLOYMENT”   
INNER JOIN “DEPARTMENT”  
ON “EMPLOYMENT”.“ID” = “DEPARTMENT”.“ID”;

Output:

ID STATE NAME
1 A IT
2 B Sales
3 C Bank

Explanation:
The EMPLOYMENT and the DEPARTMENT are the already existing tables that are joined with INNER JOIN query so that the selected fields of the rows satisfying the joining conditions will be displayed as the result.