- Machine Learning
- Machine Learning Tutorial
- Getting Started
- Mean Median Mode
- Standard Deviation
- Percentile
- Data Distribution
- Normal Data Distribution
- Scatter Plot
- Linear Regression
- Polynomial Regression
- Multiple Regression
- Scale
- Train-Test
- Decision Tree
- Confusion Matrix
- Hierarchical Clustering
- Logistic Regression
- Grid Search
- Categorical Data
- K-means
- Bootstrap Aggregation
- Cross Validation
- AUC - ROC Curve
- K-nearest neighbors
- Python MySQL
- Python - MySQL
- MySQL Get Started
- MySQL Create Database
- MySQL Create Table
- MySQL Insert
- MySQL Select
- MySQL Where
- MySQL Order By
- MySQL Delete
- MySQL Drop Table
- MySQL Update
- MySQL Limit
- MySQL Join
- Python MongoDB
- Python - MongoDB
- MongoDB Get Started
- MongoDB Create DB
- MongoDB Collection
- MongoDB Insert
- MongoDB Find
- MongoDB Query
- MongoDB Sort
- MongoDB Delete
- MongoDB Drop Collection
- MongoDB Update
- MongoDB Limit
- Selected Reading
- Q&A
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 "users" table and a "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' }
These two tables can be combined by using users' fav
field and products'
id
field.
Example
Join users and products to see the name of the users favorite product:
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)
Run example »
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:
Example
Select all users and their favorite product:
sql = "SELECT \
users.name AS user,
\
products.name AS favorite \
FROM users \
LEFT JOIN
products ON users.fav = products.id"
Run example »
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:
Example
Select all products, and the user(s) who have them as their favorite:
sql = "SELECT \
users.name AS user,
\
products.name AS favorite \
FROM users \
RIGHT JOIN
products ON users.fav = products.id"
Run example »
Note: Hannah and Michael, who have no favorite product, are not included in the result.