Python MySQL Join

Combineren van twee of meer tabellen

Je kunt een JOIN-statement gebruiken om rijen uit twee of meer tabellen te combineren op basis van de gerelateerde kolommen tussen hen.

Aannemen dat je de tabellen "users" en "products" hebt:

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' }

Je kunt de velden van users gebruiken om fav velden en products van id velden om deze twee tabellen te combineren.

Example

Combineren van gebruikers en producten om te zien welke producten de gebruikers het meest leuk vinden:

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 users who like them, even if there are no users who like these products, please use the RIGHT JOIN statement:

Example

Select all products and users who like them:

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.