- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can you perform self join on two tables using MySQL in Python?
We can join two tables in SQL based on a common column between them or based on some specified condition. There are different types of JOIN available to join two SQL tables.
Here, we will discuss about the SELF join on two tables.
As the name signifies, SELF join is the join with the table itself. This join is performed between two copies of the same table. The rows of the table are matched with the other rows of the same table based on some condition.
SELECT a.coulmn1 , b.column2 FROM table_name a, table_name b WHERE condition;
a and b are the two aliases of the same table.
table_name is the name of the table on which self join is to be performed. The same table name is used at both places since we are performing self join.
Let there be a table named “Students” as folllows −
+----------+--------------+-----------+ | id | Student_name | Dept_id | +----------+--------------+-----------+ | 1 | Rahul | 120 | | 2 | Rohit | 121 | | 3 | Kirat | 121 | | 4 | Inder | 123 | +----------+--------------+-----------+
We will perform self join on the above table based on the condition a.Dept_id<b.Dept_id. This means that each row in the table will be matched with the other rows in the table whose Dept_id are less than that particular row.
Steps to perform self join on two tables using MySQL in python
import MySQL connector
establish connection with the connector using connect()
create the cursor object using cursor() method
create a query using the appropriate mysql statements
execute the SQL query using execute() method
close the connection
import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="SELECT a.Dept_id,b.Student_name FROM Students a, Students b WHERE a.Dept_id<b.Dept_id" cursor.execute(query) rows=cursor.fetchall() for x in rows: print(x) db.close()
- How can you perform inner join on two tables using MySQL in Python?
- How can you perform left join on two tables using MySQL in Python?
- How can you perform right join on two tables using MySQL in Python?
- How can you perform full join on two tables using MySQL in Python?
- Perform MySQL LEFT JOIN on two tables?
- MySQL join two tables?
- How to use SELF JOIN in MySQL?
- Compare two tables and return missing ids using MySQL LEFT OUTER JOIN
- How can I merge two MySQL tables?
- How to write PHP script by using MySQL JOINS inside it to join two MySQL tables?
- How can we compare data in two MySQL tables?
- Get MAX() on column in two MySQL tables?
- How to perform cartesian join for two data.table objects in R?
- How can you select data from a table based on some criteria using MySQL in Python?
- C++ Program to Perform Searching Using Self-Organizing Lists