In This Article, You Will Know About Python MySQL Limit JOIN.
Python MySQL JOIN – Before moving ahead, let’s know a bit about Python MySQL Limit Table.
Table of Contents
JOIN Two Tables
To JOIN or combine two tables, use the “JOIN” statement. “JOIN” statement returns the data only related to both tables.
Syntax:
SELECT * FROM table1
JOIN table2
ON relation;
Here we have the first table named: totable
Id | name | class | section_id |
1 | Alex | 5 | 1 |
2 | Rolex | 6 | 2 |
3 | Salex | 7 | 2 |
4 | Lelex | 8 | 3 |
5 | Kelex | 9 | 5 |
Here we have the second table named: latesttable
Id | Gender |
1 | M |
2 | M |
3 | M |
4 | F |
5 | M |
Here we know the “Gender” of the students with the help of “section_id.”
In this case, we would SELECT the column section_id FROM the totable table (and rename it x). Then we would SELECT the Gender column FROM the latesttable table and rename it y. That would look like this: SELECT totable.section_id AS x, latesttable.Gender AS y.
We would use FROM to say that the columns are FROM the totable table, and JOIN to say that you want to JOIN it with the latesttable table, using this syntax: FROM totable JOIN latesttable.
And finally, we would say that we want to JOIN two rows together when the section_id column in the totable table is equal to the Gender column in the latesttable table with ON totable.section_id = latesttable.Id
See the code below-
import mysql.connector
connect_server = mysql.connector.connect(host = "localhost",
username = " your_MySQL_username",
password = " your_MySQL_password",
database = " your_MySQL_Database")
cursor_object = connect_server.cursor()
data_store = "SELECT \
totable.section_id as x, \
latesttable.Gender as y \
FROM totable JOIN latesttable \
on totable.section_id = latesttable.Id"
cursor_object.execute(data_store)
a = cursor_object.fetchall()
for u in a:
print(u)
Output -
(1, 'M')
(2, 'M')
(2, 'M')
(3, 'M')
(5, 'M')
Note: INNER JOIN and JOIN will return the same answer.
LEFT JOIN
The LEFT statement will return the data not related to the second table (Right).
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON relation;
See the code below-
import mysql.connector
connect_server = mysql.connector.connect(host = "localhost",
username = " your_MySQL_username",
password = " your_MySQL_password",
database = " your_MySQL_database")
cursor_object = connect_server.cursor()
data_store = "SELECT \
totable.section_id as x, \
latesttable.Gender as y \
FROM totable LEFT JOIN latesttable \
on totable.section_id = latesttable.Id"
cursor_object.execute(data_store)
a = cursor_object.fetchall()
for u in a:
print(u)
Output -
(1, 'M')
(2, 'M')
(2, 'M')
(3, 'M')
(5, 'M')
RIGHT JOIN
The RIGHT statement will return the data not related to the first table (Left).
SELECT columns
FROM table1
RIGHT JOIN table2
ON relation;
See the code below-
import mysql.connector
connect_server = mysql.connector.connect(host = "localhost",
username = " your_MySQL_username",
password = " your_MySQL_password",
database = " your_MySQL_database")
cursor_object = connect_server.cursor()
data_store = "SELECT \
totable.section_id as x, \
latesttable.Gender as y \
FROM totable RIGHT JOIN latesttable \
on totable.section_id = latesttable.Id"
cursor_object.execute(data_store)
a = cursor_object.fetchall()
for u in a:
print(u)
Output -
(1, 'M')
(2, 'M')
(2, 'M')
(3, 'M')
(None, 'F')
(5, 'M')
It seems there is a student with no section_id therefore returned None.
FULL JOIN
The FULL statement will return the data FROM both tables.
SELECT columns FROM table1 FULL JOIN table2 ON relation;
See the code below-
import mysql.connector
connect_server = mysql.connector.connect(host = "localhost",
username = " your_MySQL_username",
password = " your_MySQL_password",
database = " your_MySQL_database")
cursor_object = connect_server.cursor()
data_store = "SELECT \
totable.section_id as x, \
latesttable.Gender as y \
FROM totable FULL JOIN latesttable \
on totable.section_id = latesttable.Id"
cursor_object.execute(data_store)
a = cursor_object.fetchall()
for u in a:
print(u)
