JOINclause is used to combine rows from two or more tables based on a related column. It allows you to query data from multiple tables and return the result as a single set. There are several types of joins in SQL, each providing different results based on how data is combined.
JOINTables used in this article:
student table of college database
studentdetails table of college databaseTypes of Joins:
1. INNER JOIN
Returns records that have matching values in both tables. If no match is found, no record is returned.
Syntax:
SELECT column1, column2...
FROM table1
JOIN table2 ON table1.column = table2.column;
Example: Using INNER JOIN
In this example, we are using the INNER JOIN to retrieve the names of students who are present in both the Student table and the Student table (you can change the table names based on your requirements). This query returns the records where the Roll_no matches in both tables.
Pythonimportmysql.connector# Connecting to the databasemydb=mysql.connector.connect(host='localhost',database='College',user='root',# Replace with your MySQL usernamepassword='your_password'# Replace with your MySQL password)cs=mydb.cursor()# INNER JOIN: only students with matching detailsstatement="""SELECT s.Name, d.Branch FROM Student s INNER JOIN StudentDetails d ON s.Roll_no = d.Roll_no"""cs.execute(statement)# Fetch and print the resultsresult_set=cs.fetchall()forxinresult_set:print(x)# Disconnecting from the databasemydb.close()
Output:
output in the terminal2. LEFT JOIN
Returns all records from the left table and the matched records from the right table. If no match is found, the result is NULL on the right side.
Syntax:
SELECT column1, column2...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
Example: Using LEFT JOIN
In this example, we use LEFT JOIN to fetch all student names from the STUDENT table and any matching records from the Student table. If a record doesn't have a match, the result will display NULL for columns from the right table.
Pythonimportmysql.connector# Connecting to the databasemydb=mysql.connector.connect(host='localhost',database='College',user='root',password='your_password')cs=mydb.cursor()# Left joinstatement="""SELECT s.Name, d.Section FROM Student s LEFT JOIN StudentDetails d ON s.Roll_no = d.Roll_no"""cs.execute(statement)forrowincs.fetchall():print(row)mydb.close()
Output:
output of left join in terminal3. RIGHT JOIN
Returns all records from the right table and the matched records from the left table. If no match is found, the result is NULL on the left side.
Syntax:
SELECT column1, column2...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;
Example: Using RIGHT JOIN
In this example, we demonstrate the RIGHT JOIN, which returns all records from the Student table and the matching records from the STUDENT table. If no match is found, the result will show NULL for columns from the left table.
Pythonimportmysql.connectormydb=mysql.connector.connect(host='localhost',database='College',user='root',password='your_password')cs=mydb.cursor()#Right Joinstatement="""SELECT d.Branch, s.Name FROM Student s RIGHT JOIN StudentDetails d ON s.Roll_no = d.Roll_no"""cs.execute(statement)forrowincs.fetchall():print(row)mydb.close()
Output:
output after performing right join