In relational databases like PostgreSQL, data is often stored across multiple related tables. To query meaningful combined information, you can use JOIN operations. This tutorial teaches you how to perform different types of JOIN operations in PostgreSQL using Python and the psycopg2 library.

Table of Contents

1. What You’ll Learn

2. Prerequisites

3. What is a JOIN?

4. Types of PostgreSQL Joins

5. Setting Up Example Tables

6. Performing JOINs in Python

7. Full Code Example

8. Tips and Common Pitfalls

1. What You’ll Learn

• How JOIN works in PostgreSQL

• The difference between INNER JOIN, LEFT JOIN, and others

• How to perform JOIN operations using Python

• How to use parameterized JOIN queries

• Best practices for joining tables efficiently

✅ 2. Prerequisites

You’ll need:

• PostgreSQL installed and running

• Python installed

• psycopg2 installed:

pip install psycopg2-binary

• Two PostgreSQL tables with a foreign key relationship

3. What is a JOIN?

A JOIN combines rows from two or more tables based on a related column between them.

Example:

SELECT * FROM students
JOIN classes ON students.class_id = classes.id;

This retrieves student data along with their class details.

4. Types of PostgreSQL Joins

5. Setting Up Example Tables

Students Table

Classes Table

6. Performing JOINs in Python

Example: INNER JOIN in Python

import psycopg2

conn = psycopg2.connect(
    dbname="school",
    user="postgres",
    password="your_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

query = """
SELECT students.name, classes.class_name
FROM students
JOIN classes ON students.class_id = classes.id
"""

cur.execute(query)
results = cur.fetchall()

for row in results:
    print(f"Student: {row[0]}, Class: {row[1]}")

cur.close()
conn.close()

Other Join Types in Python

LEFT JOIN:

SELECT students.name, classes.class_name
FROM students
LEFT JOIN classes ON students.class_id = classes.id;

This returns all students, even if they are not assigned to a class.

RIGHT JOIN:

SELECT students.name, classes.class_name
FROM students
RIGHT JOIN classes ON students.class_id = classes.id;

Returns all classes, including those with no students.

7. Full Code Example

import psycopg2

try:
    conn = psycopg2.connect(
        dbname="school",
        user="postgres",
        password="your_password",
        host="localhost",
        port="5432"
    )
    cur = conn.cursor()

    query = """
    SELECT students.name, classes.class_name
    FROM students
    LEFT JOIN classes ON students.class_id = classes.id
    """

    cur.execute(query)
    rows = cur.fetchall()

    print("Student-Class Relationships:")
    for name, class_name in rows:
        print(f"{name} ➝ {class_name if class_name else 'No class assigned'}")

    cur.close()
    conn.close()

except psycopg2.Error as e:
    print("Database error:", e)

8. Tips and Common Pitfalls

Conclusion

JOIN operations are essential for working with relational data. With psycopg2, Python makes it easy to execute JOIN queries and work with normalized tables in PostgreSQL.