Python MySQL Join

Combining two or more tables

You can use the JOIN statement to combine rows from two or more tables based on their related columns.

Assuming you have the "users" table and the "products" table:

users

{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

products

{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

You can use the users fav field and products id field to combine these two tables.

Example

Combine users and products to view the product names that users like the most:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT \",
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Run Instance

Note:You can use JOIN instead of INNER JOIN. You will get the same result.

LEFT JOIN

In the above example, Hannah and Michael are excluded from the results because INNER JOIN only displays matching records.

If you want to display all users, even those who do not have favorite products, please use the LEFT JOIN statement:

Example

Select all users and their favorite products:

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

Run Instance

RIGHT JOIN

If you want to return all products and the users who like them, even if there are no users who like these products, please use the RIGHT JOIN statement:

Example

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

Run Instance

Note:Hannah and Michael, who are not interested in any products, are not included in the results.