What is join?

In PostgreSQL, a join is a database operation that combines rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables as if they were a single table, enabling you to create meaningful relationships between the data.

Joins are typically performed using the JOIN keyword in combination with specific join conditions. The join condition specifies how the tables should be connected and determines which rows from each table are matched together.

PostgreSQL supports different types of joins, including:

1-Equi Join:
An equi join is a type of join that uses the equality operator (=) to match rows between tables based on a common column.
It returns the rows where the join condition is satisfied, i.e., where the values in the specified columns are equal.
Equi joins are commonly used to retrieve related data from two or more tables.

SELECT *
FROM table1 t1, table2 t2
WHERE t1.id = t2.id;

2-Theta Join:
A theta join, also known as a non-equijoin, is a join that uses comparison operators other than equality (such as >, <, >=, <=, or <>) to match rows between tables.
It allows you to join tables based on conditions other than strict equality.
Theta joins offer more flexibility than equi joins but require careful consideration of the join conditions to avoid unintended results.

SELECT *
FROM table1 t1, table2 t2
WHERE t1.id > t2.id;

3-Inner Join:
An inner join returns only the rows where the join condition is satisfied between the tables being joined.
It matches rows based on the specified join condition, whether it's an equi join or a theta join.
Inner joins exclude rows where there is no match between the tables.

SELECT *FROM table1 t1
INNER JOIN table2 t2 
ON t1.id=t2.id;

4-Left Join (or Left Outer Join):
A left join returns all the rows from the left table and the matching rows from the right table based on the join condition.
If there is no match, NULL values are used for the columns of the right table.
The left join ensures that all rows from the left table are included in the result, regardless of matching rows in the right table.

SELECT *FROM table1 t1
LEFT JOIN table2 t2 
ON t1.id=t2.id;

5-Right Join (or Right Outer Join):
A right join returns all the rows from the right table and the matching rows from the left table based on the join condition.
If there is no match, NULL values are used for the columns of the left table.
The right join ensures that all rows from the right table are included in the result, regardless of matching rows in the left table.

SELECT *FROM table1 t1 
RIGHT JOIN table2 t2 
ON t1.id=t2.id;

6-Full Join (or Full Outer Join):
A full join returns all rows from both the left and right tables.
It includes all the rows from both tables, regardless of whether there is a match or not.
If there is no match, NULL values are used for the columns of the table that lacks a matching row.

SELECT *FROM table1 t1 
FULL OUTER JOIN table2 t2
ON t1.id=t2.id;

7-Cross Join:
A cross join, also known as a Cartesian join, returns the Cartesian product of the two tables.
It combines each row from the first table with every row from the second table, resulting in all possible combinations of rows.
Cross joins can generate a large number of rows, so they should be used with caution.

SELECT *
FROM table1
CROSS JOIN table2;

8-Natural Join:
A natural join is a join that automatically matches columns with the same names and data types in the tables being joined.
It eliminates the need to specify the join condition explicitly based on column equality.
However, natural joins can be risky to use because changes in column names or additional columns can lead to unexpected results.

SELECT *
FROM table1
NATURAL JOIN table2;

9-Self Join
A self-join is a specific type of join operation where a table is joined with itself. In other words, it involves using a single table multiple times within the same query. This can be useful when you need to create a relationship or compare records within the same table.

SELECT *FROM employees e 
JOIN employees m
ON e.empoyee_id=m.manager_id;

REF:
PostgreSQL tutorial
The Internals of PostgreSQL