Python PostgreSQL – Join

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: Employee 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: