PostgreSQL Joins is a query which combines data from multiple tables.A PostgreSQL Joins is performed at whatever points two or more tables are joined in a  statement. PostgreSQL Joins can be classified into the following types, such as.

An inner join, PostgreSQL Joins between two or more tables that fulfills the join cases. Inner joins utilize a correlation administrators like = or to coordinate from two tables in light of the qualities in like manner sections from every table.

[c] SQLDB=# create table customer(customer_id integer,customer_name character varying(50),city character varying(50),country character varying(50)); CREATE TABLE SQLDB=# insert into customer values(1001,'James','Maria Anders','United states'); INSERT 0 1 SQLDB=# insert into customer values(1002,'Mike','Trujillo','Germany'); INSERT 0 1 SQLDB=# insert into customer values(1003,'Kate','Mataderos','France'); INSERT 0 1 SQLDB=# select * from customer; customer_id | customer_name| city | country ------------+--------------+--------------+-------------- 1001 | James | Maria Anders | United states 1002 | Mike | Trujillo | Germany 1003 | Kate | Mataderos | France (3 rows) SQLDB=# create table orders(order_id integer,customer_id integer,employee_id integer,shipper_id integer); INSERT 0 1 SQLDB=#sql> insert into orders values(1101,1002,1011,3); INSERT 0 1 SQLDB=#sql> insert into orders values(1102,12,1012,5); INSERT 0 1 SQLDB=#sql> insert into orders values(1103,21,1033,7); INSERT 0 1 SQLDB=#sql> select * from orders; order_id | customer_id | employee_id | shipper_id ----------+-------------+-------------+------------ 1101 | 100 | 1011 | 3 1102 | 12 | 1012 | 5 1103 | 21 | 1033 | 7 (3 rows) SQLDB=#sql> Select customer.customer_name,orders.order_id from customer INNER JOIN orders on customer.customer_id=orders.customer_id order by customer.customer_name; customer_name | order_id ----------------+---------- Mike | 1101 (1 row) [/c]

The condition is optional for the table on left size and is compulsory for the table on right side.

By viewing the below example, the concept of left outer join can be understand easily. [sql] SQLDB=# select * from e12; emp_id | ename | dept_no -------+--------+--------- 1001 | jack | 10 1002 | maddi | 20 1003 | maddie | 10 1004 | max | 20 1004 | capi | 30 (5 rows) SQLDB=# select * from d12; dept_no | dept_name | city --------+-----------+-------- 10 | finance | texas 20 | capital | new york (5 rows) SQLDB=# select * from d12; dept_no | dept_name | city --------+-----------+---------- 10 | finance | texas 20 | capital | new york (2 rows) SQLDB=# select emp_id,ename,dept_name,city from e12 left join d12 on e12 .dept_no=d12.dept_no; emp_id | ename | dept_name | city --------+--------+-----------+--------- 1001 | jack | finance | texas 1003 | maddie | finance | texas 1002 | maddi | capital | new york 1004 | max | capital | new york 1004 | capi | NULL | NULL (5 rows) [/sql] In the above example, The condition is optional for the table e12 and is compulsory for the table d12.

The condition is optional for the table on right side, where as it is compulsory on the table on left side.

[sql] SQLDB=# select * from e12; emp_id | ename | dept_no --------+--------+--------- 1001 | jack | 10 1002 | maddi | 20 1003 | maddie | 10 1004 | max | 20 1004 | capi | 30 (5 rows) SQLDB=# select * from d12; dept_no | dept_name | city ---------+--------------+---------- 10 | finance | texas 20 | capital | new york 30 | manager | ausralia 40 | applications | usa 50 | technology | canada (5 rows) SQLDB=# select e12.emp_id,e12.ename,d12.dept_name,d12.city from d12 right join e12 on d12.dept_no=e12.dept_no; emp_id | ename | dept_name | city --------+--------+-----------+---------- 1001 | jack | finance | texas 1003 | maddie | finance | texas 1002 | maddi | capital | new york 1004 | max | capital | new york 1004 | capi | manager | ausralia (5 rows) In the above example,The condition is optional for the table d12. And is compulsory for the table e12[/sql]

In order to perform full outer join, inner join as to be performed on each row in the table shouldn't satisfy the join condition with any row of other table.

The syntax for Full outer join is as follows:

By viewing the below example the concept of Full outer join can be easily understand. [sql] SQLDB=# Select Emp_id,Emp_Name,Dept from Employee21 Full outer join Department on Emp_id=Dept.Emp_id; Emp_id | Emp_Name | Dept --------+----------+----------- 1001 | John | Manager 1004 | Mate | Admin 1003 | | Database | Shah | | Maddie | [/sql]

A Cross join is a join of every row of one table to every row of another table.

By viewing the below example,the concept of Cartesian join can be understand easily. [sql]SQLDB=# select * from emp17; emp_id | ename | deptno -------+--------+-------- 1001 | jhon | 10 1002 | shah | 20 1003 | maddi | 10 1004 | maddie | 20 (4 rows) SQLDB=# select * from dept13; deptno | dept_name | dept_city -------+-----------+----------- 10 | leo | texas 20 | vin | newyork (2 rows) SQLDB=# select * from emp17,dept13; emp_id | ename | deptno | deptno | dept_name | dept_city -------+--------+--------+--------+-----------+----------- 1001 | jhon | 10 | 10 | leo | texas 1001 | jhon | 10 | 20 | vin | newyork 1002 | shah | 20 | 10 | leo | texas 1002 | shah | 20 | 20 | vin | newyork 1003 | maddi | 10 | 10 | leo | texas 1003 | maddi | 10 | 20 | vin | newyork 1004 | maddie | 20 | 10 | leo | texas 1004 | maddie | 20 | 20 | vin | newyork (8 rows) [/sql] In the above example, the Cartesian join combines both emp17 table and dept13 table and update the values to all the fields based on depno, so that each and every employee will have completed details with duplicate values.

• PostgreSQL Joins - Join is a query which combines data from multiple tables.
• Inner Join - Joins between two or more table.
• Left outer Join - The condition is optional for the table on the left side and is compulsory for the table on right side.
• Right outer Join - The condition is optional on left side of the table and is compulsory on the table on the right side.
• Cross join - Join every row of a table to every row of another table.