- Python Basic Tutorial
- Python - Home
- Python - Overview
- Python - Environment Setup
- Python - Basic Syntax
- Python - Comments
- Python - Variables
- Python - Data Types
- Python - Operators
- Python - Decision Making
- Python - Loops
- Python - Numbers
- Python - Strings
- Python - Lists
- Python - Tuples
- Python - Dictionary
- Python - Date & Time
- Python - Functions
- Python - Modules
- Python - Files I/O
- Python - Exceptions
- Python Advanced Tutorial
- Python - Classes/Objects
- Python - Reg Expressions
- Python - CGI Programming
- Python - Database Access
- Python - Networking
- Python - Sending Email
- Python - Multithreading
- Python - XML Processing
- Python - GUI Programming
- Python - Further Extensions
How can you perform left 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 LEFT join on two tables. The tables can be joined using the LEFT join. In the LEFT JOIN, all the records from the first table or the left table are always included in the result. From the right table, the matching records are joined to the records of the left table. If there is no matching record found for a row in left table, then None is joined with that record.
The tables are joined on basis of some condition. But all the records of the left table will always be included in the result irrespective of the condition.
SELECT column1, column2... FROM table_1 LEFT JOIN table_2 ON condition;
Let there be two tables, “Students” and “Department” as folllows −
+----------+--------------+-----------+ | id | Student_name | Dept_id | +----------+--------------+-----------+ | 1 | Rahul | 120 | | 2 | Rohit | 121 | | 3 | Kirat | 125 | | 4 | Inder | 123 | +----------+--------------+-----------+
+----------+-----------------+ | Dept_id | Department_name | +----------+-----------------+ | 120 | CSE | | 121 | Mathematics | | 122 | Physics | +----------+-----------------+
We will perform left join on the above tables based on the dept_id which is common in both the tables.
Steps to perform left 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 Students.Id,Students.Student_name,Department.Department_name FROM Students LEFT JOIN Department ON Students.Dept_Id=Department.Dept_Id" cursor.execute(query) rows=cursor.fetchall() for x in rows: print(x) db.close()
(1, ‘Rahul’, ‘CSE’) (2, ‘Rohit’, ‘Mathematics’) (3, ‘Kirat’, None) (4, ‘Inder’, None)
Notice, all the records from the left table are included in the result even though there are no matching records for the last 2 rows.
- Related Articles
- How can you perform inner 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?
- How can you perform self join on two tables using MySQL in Python?
- Perform MySQL LEFT JOIN on two tables?
- Compare two tables and return missing ids using MySQL LEFT OUTER JOIN
- MySQL join two tables?
- How to write PHP script by using MySQL JOINS inside it to join two MySQL tables?
- How to perform a left outer join using linq extension methods in C#?
- How can I merge two MySQL tables?
- How can we create a MySQL view with LEFT JOIN?
- How can we compare data in two MySQL tables?
- How MySQL LEFT JOIN can be used to simulate the MySQL MINUS query?
- What is MySQL LEFT JOIN and how can we write MySQL query for it?
- Get MAX() on column in two MySQL tables?