Skip to content
Related Articles

Related Articles

Improve Article

Python PostgreSQL – Join

  • Last Updated : 14 Sep, 2021
Geek Week

In this article, we are going to see join methods in PostgreSQL using pyscopg2 in Python. Let’s see the type of joins supported in PostgreSQL.

Types of join:

  • Inner join
  • Full join (outer join)
  • Left join
  • Right join
  • Cross join

Tables for demonstration:

Table 1: Employe table

Table 2: Dept table



The psycopg2.connect() method is used to connect to the database and cursor() and fetchall() methods to retrieve data from the database. we use to execute() method to execute our SQL command then further retrieve it through fetchall() method.

Inner Join

The inner join is one of the most common types of joins. Inner join is used to join two tables based on common characteristics among the rows. it returns a table that has common row characteristics.

Execute a SQL statement:

SELECT table1.col1, table2.col2…

FROM table1

INNER JOIN table2



ON table1.common_field = table2.common_field;

Code:

Python3




import psycopg2
  
conn = psycopg2.connect(
    database="EMPLOYEE_DATABASE", user='postgres'
  password='pass', host='127.0.0.1', port='5432'
)
  
conn.autocommit = True
cursor = conn.cursor()
  
sql = '''SELECT * from employee INNER JOIN dept\
ON employee.deptno =dept.deptno '''
  
cursor.execute(sql)
results = cursor.fetchall()
for i in results:
    print(i)
conn.commit()
conn.close()

Output:

Full Join

It is also called ‘Full Outer Join’,.it returns all those data which either have a match in the left or right tables. if rows in both the tables do not match, the resulting data frame will replace NaN with every column of the tables that deficits a matching row.

Execute a SQL statement:

SELECT table1.col1, table2.col2…

FROM table1



FULL JOIN table2

ON table1.common_field = table2.common_field;

Code:

Python3




import psycopg2
  
conn = psycopg2.connect(
    database="EMPLOYEE_DATABASE", user='postgres'
  password='pass', host='127.0.0.1', port='5432'
)
  
conn.autocommit = True
cursor = conn.cursor()
  
sql = '''SELECT * from employee FULL JOIN dept\
ON employee.deptno =dept.deptno '''
  
cursor.execute(sql)
results = cursor.fetchall()
for i in results:
    print(i)
conn.commit()
conn.close()

Output:

Left Join

It is also known as Left Outer Join, returns a table containing all the rows of the left data frame. if there are non-matching rows of the left table then the unmatched data in the right table is replaced by NaN.

Execute a SQL statement:

SELECT table1.col1, table2.col2…



FROM table1

LEFT JOIN table2

ON table1.common_field = table2.common_field;

Code:

Python3




import psycopg2
  
conn = psycopg2.connect(
    database="EMPLOYEE_DATABASE", user='postgres',
  password='pass', host='127.0.0.1', port='5432'
)
  
conn.autocommit = True
cursor = conn.cursor()
  
sql = '''SELECT * from employee left JOIN dept\
ON employee.deptno =dept.deptno '''
  
cursor.execute(sql)
results = cursor.fetchall()
for i in results:
    print(i)
conn.commit()
conn.close()

Output:

Right Join

Right join is the exact opposite of left join. returns a table containing all the rows of the right table. if there are non-matching rows of the right table then the unmatched data in the left table is replaced by NaN.

Execute a SQL statement:



SELECT table1.col1, table2.col2…

FROM table1

RIGHT JOIN table2

ON table1.common_field = table2.common_field;

Code:

Python3




import psycopg2
  
conn = psycopg2.connect(
    database="EMPLOYEE_DATABASE", user='postgres'
  password='pass', host='127.0.0.1', port='5432'
)
  
conn.autocommit = True
cursor = conn.cursor()
  
sql = '''SELECT * from employee RIGHT JOIN dept\
ON employee.deptno =dept.deptno '''
  
cursor.execute(sql)
results = cursor.fetchall()
for i in results:
    print(i)
conn.commit()
conn.close()

Output:

Cross Join

A cross join matches every row of the first table with every row of the second table. If the input tables have A and B columns, respectively, then our final output table will have A+B columns

Execute a SQL statement:



SELECT  COLUMNS… FROM table1 CROSS JOIN table2 

Code:

Python3




import psycopg2
  
conn = psycopg2.connect(
    database="EMPLOYEE_DATABASE", user='postgres',
  password='pass', host='127.0.0.1', port='5432'
)
  
conn.autocommit = True
cursor = conn.cursor()
  
sql = '''SELECT employee.empno,employee.ename,
dept.deptno from employee cross JOIN dept  '''
  
cursor.execute(sql)
results = cursor.fetchall()
for i in results:
    print(i)
conn.commit()
conn.close()

Output:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :