Get Started: Python MySQL JOIN

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)

				
			

If you find anything incorrect in the above-discussed topic and have further questions, please comment below.

Connect on:

Leave a Comment

Stay in the loop

codingstreets