Python Tutorials · Python MySQL

MySQL Join

Learn all about MySQL Join in this comprehensive tutorial.

5 min read advanced

MySQL Limit

MongoDB Get Started

•You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.
•In the example above, Hannah, and Michael were excluded from the result, that is because INNER JOIN only shows the records where there is a match.
•If you want to return all products, and the users who have them as their favorite, even if no user have them as their favorite, use the RIGHT JOIN statement:

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 "users" table and a "products" table:

users

python

{ 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

python

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

These two tables can be combined by using users' fav field and products' id field.

python

import mysql.connectormydb = mysql.connector.connect(  host="localhost", 
  user="yourusername",  password="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 val_1 in myresult:  print(val_1)

Note: Note: You can use JOIN instead of INNER JOIN. They will both give you the same result.

LEFT JOIN

In the example above, Hannah, and Michael were excluded from the result, that is because INNER JOIN only shows the records where there is a match.

If you want to show all users, even if they do not have a favorite product, use the LEFT JOIN statement:

python

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

RIGHT JOIN

If you want to return all products, and the users who have them as their favorite, even if no user have them as their favorite, use the RIGHT JOIN statement:

python

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

Note: Note: Hannah and Michael, who have no favorite product, are not included in the result.

Module quiz

2 questions

Which of the following is true about MySQL Join?

It is strictly typedIt is evaluated at runtimeIt requires explicit memory managementIt is deprecated

What is the most common pitfall when working with MySQL Join?

SyntaxErrorIndentationErrorNameErrorTypeError

Answer all questions to submit.

MySQL Limit

MongoDB Get Started

MySQL Join

AI Summary

Join Two or More Tables

users

products

LEFT JOIN

RIGHT JOIN

Module quiz