PostgreSQL - SPLessons

PostgreSQL Joins

Chapter 19

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Joins

PostgreSQL Joins

shape Description

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.

Inner join

shape Description

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.

shape Syntax

Select <table_name1>.<column_name1>,<table_name2>.<column_name2> from <table_name1> Inner join <table_name2> on <table_name1>=<table_name2>;

Table_name =>Any accurate table.

shape Examples

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)

Left outer join

shape Description

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

shape Syntax

SELECT *FROM left [ OUTER ] JOIN table2
ON table1.column_name=table2.column_name;

Table_name =>Any accurate table.

shape Examples

By viewing the below example, the concept of left outer join can be understand easily.

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)

In the above example, The condition is optional for the table e12 and is compulsory for the table d12.

Right outer join

shape Description

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

shape Syntax

SELECT * FROM table1 Right [ OUTER ] JOIN table2
ON table1.column_name=table2.column_name;

Table_name =>Any accurate table.

shape Examples

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

Full outer join

shape Description

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.

shape Syntax

The syntax for Full outer join is as follows:

Select * from <table_name1>Full outer join <table_name2> on conditional_expression;

Table_name =>Any accurate table.

shape Examples

By viewing the below example the concept of Full outer join can be easily understand.

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   |  

Cross join

shape Description

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

shape Syntax

Select * from <table_name1>,<table_name2>;

Table_name => Any accurate table.

shape Examples

By viewing the below example,the concept of Cartesian join can be understand easily.

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)

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.

Summary

shape Key Points

  • 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.