Python SQLite JOIN Clause (original) (raw)

In this article, we will explore the **JOIN clause in SQLite using Python's **sqlite3 module. The JOIN clause combines records from two tables based on a related column, allowing us to perform complex queries.

Types of SQL Joins

• **INNER JOIN (OR JOIN): Gives the records that have common attributes in both tables.
• **LEFT JOIN: Gives all records from the left table and only the common records from the right table.
• **RIGHT JOIN: Gives all records from the right table and only the common records from the left table.
• **FULL OUTER JOIN: Gives all records when there is a common attribute in either the left or the right table.
• **CROSS JOIN: Gives records of one table with all other records of another table.

**Note:

• Unlike other types of joins, it does not include a join condition.
• SQLite does not directly support the **RIGHT JOIN and **FULL OUTER JOIN.

Creating a Database

Here, we will create a simple database having two tables Advisor(AdvisorID, AdvisorName) and Student(StudentID, StudentName, AdvisorID) where AdvisorID of the Student table is the foreign key referencing AdvisorID of the Advisor table.

Python `

Import required libraries

import sqlite3

Connect to SQLite database

New file created if it doesn't already exist

conn = sqlite3.connect(r'C:\Users\SQLite\Geeks.db')

Create cursor object

cursor = conn.cursor()

Create and populate tables

cursor.executescript(''' CREATE TABLE Advisor( AdvisorID INTEGER NOT NULL, AdvisorName TEXT NOT NULL, PRIMARY KEY(AdvisorID) );

CREATE TABLE Student( StudentID NUMERIC NOT NULL, StudentName NUMERIC NOT NULL, AdvisorID INTEGER, FOREIGN KEY(AdvisorID) REFERENCES Advisor(AdvisorID), PRIMARY KEY(StudentID) );

INSERT INTO Advisor(AdvisorID, AdvisorName) VALUES (1,"John Paul"), (2,"Anthony Roy"), (3,"Raj Shetty"), (4,"Sam Reeds"), (5,"Arthur Clintwood");

INSERT INTO Student(StudentID, StudentName, AdvisorID) VALUES (501,"Geek1",1), (502,"Geek2",1), (503,"Geek3",3), (504,"Geek4",2), (505,"Geek5",4), (506,"Geek6",2), (507,"Geek7",2), (508,"Geek8",3), (509,"Geek9",NULL), (510,"Geek10",1);

''')

#Commit changes to database conn.commit()

Closing the connection

conn.close()

`

**Tables Created:

Advisor Table

Student Table

Now, let's perform different types of join on the above-created database.

INNER JOIN

Inner join also represented as join which gives the records that have common attributes in both tables.

**Syntax:

SELECT columns
FROM table1
[INNER] JOIN table2
ON table1.column = table2.column;
INNER keyword is optional

Python `

Import required libraries

import sqlite3

Connect to SQLite database

conn = sqlite3.connect('Geeks.db')

Create cursor object

cursor = conn.cursor()

Query for INNER JOIN

sql = '''SELECT StudentID, StudentName, AdvisorName FROM Student INNER JOIN Advisor ON Student.AdvisorID = Advisor.AdvisorID;'''

Executing the query

cursor.execute(sql)

Fetching rows from the result table

result = cursor.fetchall() for row in result: print(row)

Closing the connection

conn.close()

`

**Output:

LEFT JOIN

Gives all records from the left table, and only the common records from the right table.

**Syntax:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
OUTER keyword is optional

Python `

import sqlite3

Connect to SQLite database

conn = sqlite3.connect(r'C:\Users\SQLite\Geeks.db')

Create cursor object

cursor = conn.cursor()

Query for LEFT JOIN

sql = '''SELECT StudentID, StudentName, AdvisorName FROM Student LEFT JOIN Advisor USING(AdvisorID) ;'''

Executing the query

cursor.execute(sql)

Fetching rows from the result table

result = cursor.fetchall() for row in result: print(row)

conn.close()

`

Since the column name (AdvisorID) of joined tables is same, the clause USING(AdvisorID) can be used instead of ON Student.AdvisorID = Advisor.AdvisorID.

**Output:

RIGHT JOIN

Gives all records from the right table, and only the common records from the left table. As mentioned before, SQLite does not directly support RIGHT JOIN. However, it can be emulated using LEFT JOIN by switching the positions of the student and advisor table.

**Syntax:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
OUTER keyword is optional

Python `

import sqlite3

Connect to SQLite database

conn = sqlite3.connect('Geeks.db')

Create cursor object

cursor = conn.cursor()

Query for RIGHT JOIN

sql = '''SELECT StudentID, StudentName, AdvisorName FROM Advisor LEFT JOIN Student USING(AdvisorID);'''

Executing the query

cursor.execute(sql)

Fetching rows from the result table

result = cursor.fetchall() for row in result: print(row)

conn.close()

`

**Output:

FULL OUTER JOIN

SQLite **doesn’t support FULL OUTER JOIN directly either. However, you can emulate it using a combination of LEFT JOIN and RIGHT JOIN with UNION ALL to combine the results.

**Syntax:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
USING (common_column)
UNION ALL
SELECT column1, column2, ...
FROM table2
LEFT JOIN table1
USING (common_column)
WHERE table1.common_column IS NULL;

Steps to simulate Full Outer Join in SQLite.

• **LEFT JOIN: returns all records from the left table and the matching records from the right table.
• **LEFT JOIN with NULL filter: handles the right table by using the LEFT JOIN and filters out records that have a match in the left table.
• **UNION ALL: combines the results of both operations, giving you the **equivalent of a FULL OUTER JOIN. Python `

import sqlite3

Connect to SQLite database

conn = sqlite3.connect('Geeks.db')

Create cursor object

cursor = conn.cursor()

Query for FULL OUTER JOIN

sql = '''SELECT StudentID, StudentName, AdvisorName FROM Student LEFT JOIN Advisor USING(AdvisorID) UNION ALL SELECT StudentID, StudentName, AdvisorName FROM Advisor LEFT JOIN Student USING(AdvisorID) WHERE Student.AdvisorID IS NULL;'''

Executing the query

cursor.execute(sql)

Fetching rows from the result table

result = cursor.fetchall() for row in result: print(row)

conn.close()

`

**Output:

CROSS JOIN

It combines all records of one table with all other records of another table, that is, it creates a Cartesian product of records from the join tables.

**Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Python `

import sqlite3

Connect to SQLite database

conn = sqlite3.connect('Geeks.db')

Create cursor object

cursor = conn.cursor()

Query for CROSS JOIN

sql = '''SELECT StudentID, StudentName, AdvisorName FROM Student CROSS JOIN Advisor;'''

Executing the query

cursor.execute(sql)

Fetching rows from the result table

result = cursor.fetchall() for row in result: print(row)

conn.close()

`