Open In App

Python MySQL – Join

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

A connector is employed when we have to use mysql with other programming languages. The work of mysql-connector is to provide access to MySQL Driver to the required language. Thus, it generates a connection between the programming language and the MySQL Server.

Python-MySQL-Connector

This is a MySQL Connector that allows Python to access MySQL Driver and implement SQL queries in its programming facility. Here we will try implementing Join clause on our Database and will study the output generated.

JOIN Clause Of SQL

Join allows you to combine two or more tables in SQL, based on related column between them. Based on this application of join there are three types of join:

  • INNER JOIN gives the records that are produced by matching columns. JOIN and INNER JOIN both work the same. Syntax:
SELECT column1, column2...
FROM tablename
JOIN tablename ON condition;
SELECT column1, column2...
FROM tablename
INNER JOIN tablename ON condition;
  • LEFT JOIN gives those records from table 1 removing exclusive contents of 2 Syntax:
SELECT column1, column2...
FROM tablename
LEFT JOIN tablename ON condition;
  • RIGHT JOIN gives all records from table 2 after removing exclusive records of 1. Syntax:
SELECT column1, column2...
FROM tablename
RIGHT JOIN tablename ON condition;

The following programs will help you understand this better. DATABASE IN USE: python-join-db1 python-join-db21 PROGRAM 1: Use of inner join 

Python3




import mysql.connector
  
# Connecting to the database
mydb = mysql.connector.connect(
  host ='localhost',
  database ='College',
  user ='root',
)
  
cs = mydb.cursor()
 
# STUDENT and STudent are
# two different database
statement ="SELECT S.NAME from Student S JOIN \
Student on S.Roll_no = Student.Roll_no"
 
cs.execute(statement)
result_set = cs.fetchall()
 
for x in result_set:
    print(x)


OUTPUT: python-join-1 PROGRAM 2: use of LEFT JOIN 

Python3




import mysql.connector
  
# Connecting to the database
mydb = mysql.connector.connect(
  host ='localhost',
  database ='College',
  user ='root',
)
  
cs = mydb.cursor()
 
# STUDENT and STudent are
# two different database
statement ="SELECT S.Name from STUDENT S\
 LEFT JOIN Student s ON S.Roll_no = s.Roll_no"
 
cs.execute(statement)
result_set = cs.fetchall()
 
for x in result_set:
    print(x)


OUTPUT: python-join-2 PROGRAM 3 : use of RIGHT JOIN 

Python3




import mysql.connector
  
# Connecting to the database
mydb = mysql.connector.connect(
  host ='localhost',
  database ='College',
  user ='root',
)
  
cs = mydb.cursor()
 
# STUDENT and STudent are
# two different database
statement ="SELECT S.Name from STUDENT S RIGHT \
JOIN Student s ON S.Roll_no = s.Roll_no"
 
cs.execute(statement)
result_set = cs.fetchall()
 
for x in result_set:
    print(x)


OUTPUT: python-join-3



Last Updated : 13 Jun, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads