Python MySQL Join

  • Join Two or More Tables
  • You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.
  • Consider you have a "customers" table and a "product" table:

Create Table Name Product

In [29]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE product (id int, prodname VARCHAR(255))")

sql = "INSERT INTO product (id, prodname) VALUES (%s, %s)"
val = [
  ('1', 'iPhone'),
  ('2', 'Charger'),
  ('3', 'Data Cable'),
  ('4', 'Laptop'),
  ('5', 'Pen Drive'),
]

mycursor.executemany(sql, val)

mydb.commit()
print(mycursor.rowcount, "record inserted.")

5 record inserted.

Inner Join is same as Join

In [45]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 1, 'iPhone')
(4, 'Varun', 'Ontario', 4, 'Laptop')
(5, 'Priya', 'Delhi', 5, 'Pen Drive')

Select Columns as per require

In [36]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL Inner Join

In [37]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers INNER JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL Left Join

In [38]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers LEFT JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')
(6, 'Atharv', 'Goa', None)
(7, 'Sia', 'Dubai', None)

Python MySQL Right Join

In [39]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers RIGHT JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 'iPhone')
(None, None, None, 'Charger')
(None, None, None, 'Data Cable')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL OUTER or FULL Join

  • You don't have FULL JOINS on MySQL, but you can sure emulate them using Union
In [47]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT * from customers as t1 LEFT JOIN product as t2 ON t1.id = t2.id \
 UNION SELECT * from customers as t1 RIGHT JOIN product as t2 ON t1.id = t2.id "

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

(1, 'Karan', 'Detroit', 1, 'iPhone')
(4, 'Varun', 'Ontario', 4, 'Laptop')
(5, 'Priya', 'Delhi', 5, 'Pen Drive')
(6, 'Atharv', 'Goa', None, None)
(7, 'Sia', 'Dubai', None, None)
(None, None, None, 2, 'Charger')
(None, None, None, 3, 'Data Cable')