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.

✨ Inner Join

Consider you have a "users" table and a "products" table. The users table has a "fav" column that refers to the "id" of the product.

We can use INNER JOIN to select records that have matching values in both tables.

python

import mysql.connector

mydb = 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 x in myresult:
  print(x)

🔄 Left Join

In the example above, users with no favorite product were not listed.

If you want to list 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"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

➡️ Right Join

If you want to list all products, and the users who have them as their favorite, even if no user has 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"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

✨ Pro Tip

JOIN is one of the most powerful features of SQL, allowing you to query data from multiple tables as if they were a single table.