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 psycopg2conn = psycopg2.connect( database="EMPLOYEE_DATABASE", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = Truecursor = 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 psycopg2conn = psycopg2.connect( database="EMPLOYEE_DATABASE", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = Truecursor = 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 psycopg2conn = psycopg2.connect( database="EMPLOYEE_DATABASE", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = Truecursor = 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 psycopg2conn = psycopg2.connect( database="EMPLOYEE_DATABASE", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = Truecursor = 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 psycopg2conn = psycopg2.connect( database="EMPLOYEE_DATABASE", user='postgres', password='pass', host='127.0.0.1', port='5432')conn.autocommit = Truecursor = 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:
