To study this lesson well, you need to read these 2 articles first:
Note: we are using MySQL integrated into XAMPP. You need to read the article Installing PHP Web programming environment with XAMPP to know how to install XAMPP and run MySQL.
1. Create tables to illustrate the JOIN clause in MySQL
Using the JOIN clause, we can combine the rows (called records) of two or more tables based on the related columns in MySQL. There are different types of JOIN clauses:
(INNER) JOIN: Returns records with matching values in both tables.LEFT (OUTER) JOIN: Returns all records from the left table and matching records from the right table.RIGHT (OUTER) JOIN: Returns all records from the right table and matching records from the left table.
In this lesson, we will create and insert data for 2 tables gochocit_posts and gochocit_comments in the database gochocit to illustrate how to use the JOIN clause.
1.1. Create and insert data for the gochocit_posts table
import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="",
database = "gochocit"
)
mycursor = mydb.cursor()
# create table gochocit_posts
sql_string = """CREATE TABLE IF NOT EXISTS gochocit_posts (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
post_author VARCHAR(255),
post_title TEXT,
post_content TEXT,
post_date DATETIME
)
"""
mycursor.execute(sql_string)
# insert data for table gochocit_posts
sql = """INSERT INTO gochocit_posts (post_author, post_title, post_content, post_date)
VALUES (%s, %s, %s, %s)
"""
val = [
("Vinh Le", "Connect MySQL", "How to connect MySQL in Python", "2022-03-27 19:46:05"),
("Vinh Le", "Insert data into table", "Using the INSERT INTO statement", "2022-03-27 19:47:06"),
("Vinh Le", "Drop table", "Delete a table in MySQL", "2022-03-27 19:48:07")
]
mycursor.executemany(sql, val)
mydb.commit()
# disconnect from MySQL
mydb.close()
The ID in the gochocit_posts table will automatically increase. The added IDs of the 3 rows are 6, 7, and 8 instead of 1, 2, and 3. Because there were 5 rows that were inserted but then were deleted.
1.2. Create and insert data for the gochocit_comments table
import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="",
database = "gochocit"
)
mycursor = mydb.cursor()
# create table gochocit_comments
sql_string = """CREATE TABLE IF NOT EXISTS gochocit_comments (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
post_ID INT NOT NULL,
comment_author VARCHAR(255),
comment_content TEXT,
comment_date DATETIME
)
"""
mycursor.execute(sql_string)
# insert data for table gochocit_comments
sql = """INSERT INTO gochocit_comments (post_ID, comment_author, comment_content, comment_date)
VALUES (%s, %s, %s, %s)
"""
val = [
("7", "SuperMan", "Good job, thanks.", "2022-03-29 10:46:05"),
("7", "Ahihi", "Good.", "2022-03-29 11:47:06"),
("8", "Nam Tran", "Easy to understand.", "2022-03-29 11:48:07")
]
mycursor.executemany(sql, val)
mydb.commit()
# disconnect from MySQL
mydb.close()
We can use the ID column of the gochocit_posts table and the post_ID column of the gochocit_comments table to combine these 2 tables.
2. INNER JOIN in MySQL with Python
Use INNER JOIN to see which posts have which comments. Posts without comments will not be displayed.
import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="",
database = "gochocit"
)
mycursor = mydb.cursor()
sql = """SELECT
gochocit_posts.ID,
gochocit_posts.post_title,
gochocit_comments.comment_author,
gochocit_comments.comment_content
FROM gochocit_posts
INNER JOIN gochocit_comments ON gochocit_posts.ID = gochocit_comments.post_ID
"""
mycursor.execute(sql)
myresult = mycursor.fetchall()
print("Displaying ID, Post Title, Comment Author and Comment Content:")
for x in myresult:
print(x)
# disconnect from MySQL
mydb.close()Result
Displaying ID, Post Title, Comment Author and Comment Content:
(7, 'Insert data into table', 'SuperMan', 'Good job, thanks.')
(7, 'Insert data into table', 'Ahihi', 'Good.')
(8, 'Drop table', 'Nam Tran', 'Easy to understand.')Note: We can use JOIN instead of INNER JOIN. These 2 commands will give the same result.
3. LEFT JOIN in MySQL with Python
If we want to see all posts, even the post has comments or not, use LEFT JOIN.
sql = """SELECT
gochocit_posts.ID,
gochocit_posts.post_title,
gochocit_comments.comment_author,
gochocit_comments.comment_content
FROM gochocit_posts
LEFT JOIN gochocit_comments ON gochocit_posts.ID = gochocit_comments.post_ID
"""Result
Displaying ID, Post Title, Comment Author and Comment Content:
(7, 'Insert data into table', 'SuperMan', 'Good job, thanks.')
(7, 'Insert data into table', 'Ahihi', 'Good.')
(8, 'Drop table', 'Nam Tran', 'Easy to understand.')
(6, 'Connect MySQL', None, None)The post with ID=6 without any comment will also be displayed.
4. RIGHT JOIN in MySQL with Python
Suppose, we add a row of data to the gochocit_comments table as shown below.
We want to see all comments and which comments belong to which posts, even including comments that do not belong to any posts, use RIGHT JOIN.
sql = """SELECT
gochocit_posts.ID,
gochocit_posts.post_title,
gochocit_comments.comment_author,
gochocit_comments.comment_content
FROM gochocit_posts
RIGHT JOIN gochocit_comments ON gochocit_posts.ID = gochocit_comments.post_ID
"""Result
Displaying ID, Post Title, Comment Author and Comment Content:
(7, 'Insert data into table', 'SuperMan', 'Good job, thanks.')
(7, 'Insert data into table', 'Ahihi', 'Good.')
(8, 'Drop table', 'Nam Tran', 'Easy to understand.')
(None, None, 'Kane', 'Thank you for sharing.')The comment (None, None, 'Kane', 'Thank you for sharing.') that does not belong to any post is also displayed.
Note: It will be unreasonable when a comment does not belong to any post. However, there may be cases where a post has comments but the post itself has been deleted without deleting the comments (without primary key and foreign key constraints). Therefore, this is just an example to illustrate RIGHT JOIN.