Join Operations in SQL with Python

Join Operations in SQL with Python

In a relational database, information is commonly distributed across different tables. To work with related information from these tables, SQL provides JOIN operations. A join connects rows from two or more tables by using a related column.

In this tutorial, we will work with an Employee table and a Departments table. Using Python with MySQL, we will see how Inner Join, Right Join, and Left Join can be used to retrieve related records.

Creating Tables

First, create a Departments table containing a department ID and department name.

CREATE TABLE Departments (
    Dept_id INT(20) PRIMARY KEY NOT NULL,
    Dept_Name VARCHAR(20) NOT NULL
);

Next, add department records to the table:

INSERT INTO Departments VALUES (201, 'CS');
INSERT INTO Departments VALUES (202, 'IT');

The Employee table should contain a Dept_id column so that employee records can be related to department records.

Join Operations in Action

Inner Join

An Inner Join returns only those records for which the joining condition is satisfied in both tables. Here, the Dept_id column connects employees with their corresponding departments.

Python Code for Inner Join

import mysql.connector

# Create the connection object
myconn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="yourpassword",
    database="PythonDB"
)

# Create the cursor object
cur = myconn.cursor()

try:
    cur.execute("""
        SELECT Employee.id, Employee.name, Employee.salary,
               Departments.Dept_id, Departments.Dept_Name
        FROM Departments
        JOIN Employee
        ON Departments.Dept_id = Employee.Dept_id
    """)

    print("ID    Name    Salary    Dept_Id    Dept_Name")

    for row in cur:
        print(
            f"{row[0]}    {row[1]}    {row[2]}    "
            f"{row[3]}    {row[4]}"
        )

except Exception as e:
    print("Error:", e)
    myconn.rollback()

myconn.close()

Output

The result contains employees whose department IDs have matching entries in the Departments table.

Right Join

A Right Join keeps every record from the right-side table and adds matching information from the left-side table. In this example, Employee is the right table.

To demonstrate a record without a matching department, add an employee whose department information is unavailable:

INSERT INTO Employee (id, name, salary, branch_name)
VALUES (108, 'Alex', 29900, 'Mumbai');

Python Code for Right Join

try:
    cur.execute("""
        SELECT Employee.id, Employee.name, Employee.salary,
               Departments.Dept_id, Departments.Dept_Name
        FROM Departments
        RIGHT JOIN Employee
        ON Departments.Dept_id = Employee.Dept_id
    """)

    print("ID    Name    Salary    Dept_Id    Dept_Name")

    for row in cur:
        print(
            f"{row[0]}    {row[1]}    {row[2]}    "
            f"{row[3]}    {row[4]}"
        )

except Exception as e:
    print("Error:", e)
    myconn.rollback()

myconn.close()

Output

The NULL values show that Alex does not have a matching department record.

Left Join

A Left Join preserves every row from the left-side table and attaches matching rows from the right-side table. In this query, all departments remain part of the result.

Python Code for Left Join

try:
    cur.execute("""
        SELECT Employee.id, Employee.name, Employee.salary,
               Departments.Dept_id, Departments.Dept_Name
        FROM Departments
        LEFT JOIN Employee
        ON Departments.Dept_id = Employee.Dept_id
    """)

    print("ID    Name    Salary    Dept_Id    Dept_Name")

    for row in cur:
        print(
            f"{row[0]}    {row[1]}    {row[2]}    "
            f"{row[3]}    {row[4]}"
        )

except Exception as e:
    print("Error:", e)
    myconn.rollback()

myconn.close()

Output

The Left Join ensures that department records are retained while employee information is included whenever a matching Dept_id exists.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here
YT:- DecodeIT

Conclusion

SQL JOIN operations provide a practical way to retrieve related information stored in separate tables. By connecting tables through a common column such as Dept_id, applications can produce meaningful combined results.

In this example, Python and MySQL were used to demonstrate three important joins: Inner Join, Right Join, and Left Join. Understanding how each join handles matching and unmatched records makes it easier to work with relational database data in Python applications.

FAQs

1. What is a JOIN in SQL?

A JOIN combines related rows from two or more database tables using a common or related column.

2. What does an Inner Join return?

An Inner Join returns records where the join condition has a matching value in both tables.

3. What is the purpose of a Right Join?

A Right Join keeps all records from the right table and includes matching records from the left table.

4. What does a Left Join do?

A Left Join retains all records from the left table and adds matching information from the right table.

5. Why is Dept_id used in this example?

Dept_id acts as the related column that connects employee records with department records.

Keywords: Join Operations in SQL with Python, joins in sql with examples, self join in sql, types of joins in sql, cross join in sql, sql join 3 tables, inner join in sql, natural join in sql, left join in sql, Join Operations in SQL, set operations in sql, aggregate functions in sql, join operations in sql w3schools, join operations in sql server, join operations in sql oracle, join operations in sql server with example,Join Operations in SQL with Python