PostgreSQL Joins

PostgreSQL JOINS are used to retrieve data from multiple tables. A PostgreSQL JOIN is performed whenever two or more tables are joined in a SQL statement.

Types:

1.Basic Join
2.Inner Join
1.Equal Inner Join
2.Non-equal Inner Join
3.Outer Join
1.Left Outer Join
2.Right Outer Join
3.Full Outer Join
4.Cross Join

Examples:

Sample Tables:

pearl=# select * from muthu;
id | dob | place
----+------------+---------
1 | 1992-04-16 | theni
4 | 1991-07-22 | madurai
5 | 1991-07-22 | covai
(3 rows)
pearl=# select * from siva;
id | name | age | salary
----+----------+-----+--------
1 | muthu | 21 | 2000
2 | siva | 26 | 4000
3 | thanigai | 27 | 5000
(3 rows)

Basic Join:

pearl=# select a.id,a.name,b.dob from siva a,muthu b where a.id=b.id;

Inner Join:

Equal Inner Join:

pearl=# select * from siva a inner join muthu b on a.id=b.id;

Non-equal Inner Join:

pearl=# select * from siva a inner join muthu b on a.id!=b.id;

Outer Join:

Left Outer Join:

pearl=# select a.name,b.place from siva a left join muthu b on a.id=b.id;

Right Outer Join:

pearl=# select a.name,b.place from siva a right join muthu b on a.id=b.id;

Full Outer Join:

pearl=# select a.name,b.place from siva a full join muthu b on a.id=b.id;

Cross Join:

pearl=# select a.id,b.id from siva a cross join muthu b;