Combining data from multiple tables is a fundamental part of working with relational databases. In SQL, we use JOIN operations to bring together data that’s logically related but stored in separate tables. In this article, you’ll learn how to perform various types of SQL JOINs in Microsoft SQL Server (MSSQL) using Python and the pyodbc library.

We’ll cover:

• Different types of SQL JOINs

• When and how to use each

• Full Python examples using pyodbc

✅ Prerequisites

Tools Required

• Python 3.7 or later

• Microsoft SQL Server (local or remote)

• ODBC Driver 17 or 18 for SQL Server

Install pyodbc

pip install pyodbc

Sample Tables

To demonstrate JOIN operations, let’s use two related tables: Employees and Departments.

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    DepartmentID INT FOREIGN KEY REFERENCES Departments(DepartmentID)
);

Step 1: Connect to MSSQL Using Python

import pyodbc

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=CompanyDB;"
    "UID=your_username;"
    "PWD=your_password;"
)
cursor = conn.cursor()

Step 2: Perform an INNER JOIN

Returns only rows with matching values in both tables.

query = """
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
"""

cursor.execute(query)
for row in cursor.fetchall():
    print(row.FirstName, row.LastName, '-', row.DepartmentName)

✅ Use INNER JOIN when you only want records that exist in both tables.

⬅️ LEFT JOIN: All Employees, Even Without Department

Returns all records from the left (Employees) table and matching records from the right (Departments).

query = """
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
"""

cursor.execute(query)
for row in cursor.fetchall():
    print(row.FirstName, row.LastName, '-', row.DepartmentName or "No Department")

Useful for identifying orphaned records.

➡️ RIGHT JOIN: All Departments, Even Without Employees

Returns all records from the right (Departments) table and the matched records from the left (Employees).

query = """
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID
"""

cursor.execute(query)
for row in cursor.fetchall():
    name = f"{row.FirstName} {row.LastName}" if row.FirstName else "No Employee"
    print(name, '-', row.DepartmentName)

FULL OUTER JOIN: All Records, Matching or Not

Combines the results of LEFT and RIGHT joins.

query = """
SELECT e.FirstName, e.LastName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID
"""

cursor.execute(query)
for row in cursor.fetchall():
    name = f"{row.FirstName} {row.LastName}" if row.FirstName else "No Employee"
    dept = row.DepartmentName or "No Department"
    print(name, '-', dept)

⚠️ FULL OUTER JOIN may not be supported in some legacy SQL Server versions — test it before using.

Full Example: Function with JOIN Type Parameter

def fetch_employee_department(join_type="INNER"):
    join_type = join_type.upper()
    if join_type not in ("INNER", "LEFT", "RIGHT", "FULL OUTER"):
        raise ValueError("Unsupported JOIN type")

    query = f"""
    SELECT e.FirstName, e.LastName, d.DepartmentName
    FROM Employees e
    {join_type} JOIN Departments d ON e.DepartmentID = d.DepartmentID
    """

    cursor.execute(query)
    for row in cursor.fetchall():
        fname = row.FirstName or "No Employee"
        lname = row.LastName or ""
        dname = row.DepartmentName or "No Department"
        print(f"{fname} {lname} - {dname}")

# Usage
fetch_employee_department("LEFT")

Tips for Using JOINs in Python with MSSQL

⚠️ Common Pitfalls

Best Practices

• Use foreign keys to ensure relational integrity.

• Create indexes on the columns used in joins for faster performance.

• Prefer INNER JOIN unless you specifically need unmatched data.

• Use tools like ER diagrams or schema visualizers to understand table relationships.

• Abstract repeated queries into reusable Python functions or use an ORM (like SQLAlchemy) if complexity grows.

Conclusion

SQL JOINs are essential for working with normalized databases. With pyodbc in Python, you can easily connect to MSSQL and perform INNER, LEFT, RIGHT, and FULL OUTER joins. Always make sure your joins are logically sound, efficient, and secured against injection risks.