In Python, the “JOIN” statement is used to combine rows from two or more tables, based on a related column they share.

Let’s say we have two tables. The “users” table and the “cars” table.

Users:

• { id: 1, name: ‘Marty’, fav: 122}
• { id: 2, name: ‘Johnny’, fav: 267}
• { id: 3, name: ‘Bobby’, fav:}

Cars:

• { id: 122, car: ‘Maserati’ }
• { id: 154, car: ‘Porsche’ }
• { id: 267, car: ‘Alfa Romeo’ }

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="myusername",
    password="mypassword",
    database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT \
    users.name AS user, \
    cars.car AS favorite \
    FROM users \
    INNER JOIN cars ON users.fav = cars.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

Output:

('Marty', 'Maserati')
('Johnny', 'Alfa Romeo')

“LEFT JOIN” is used to show all users (all rows from the first table) even if there is no match in the second table.

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="myusername",
    password="mypassword",
    database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT \
    users.name AS user, \
    cars.car AS favorite \
    FROM users \
    LEFT JOIN cars ON users.fav = cars.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

Output:

('Marty', 'Maserati')
('Johnny', 'Alfa Romeo')
('Bobby', None)

“RIGHT JOIN” is used to show all cars (all rows from the second table) even if there is no match in the first table.

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="myusername",
    password="mypassword",
    database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT \
    users.name AS user, \
    cars.car AS favorite \
    FROM users \
    RIGHT JOIN cars ON users.fav = cars.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

Output:

('Marty', 'Maserati')
(None,'Porsche')
('Johnny', 'Alfa Romeo')

---