PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
EXECUTING QUERY JOIN:
MySQL JOINS are used with SELECT statement. It is used to retrieve data from multiple
tables. It is performed whenever you need to fetch records from two or more tables.
There are three types of MySQL joins:
MySQL INNER JOIN (or sometimes called simple join) MySQL LEFT OUTER JOIN (or sometimes
called LEFT JOIN)
MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
MySQL Inner JOIN (Simple Join):
The MySQL INNER JOIN is used to return all rows from multiple tables where the join
condition is satisfied. It is the most common type of join.
Syntax:
SELECT columns FROM table1 INNER JOIN table2
ON [Link] = [Link];
Image representation:
Example: Consider two tables "officers" and "students", having the following data.
Execute the following query:
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 1
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
SELECT officers.officer_name, [Link], students.course_name FROM officers
INNER JOIN students ON officers.officer_id = students.student_id;
Output:
The MySQL Inner Join is used to returns only those results from the tables that match the
specified condition and hides other rows and columns. MySQL assumes it as a default Join, so
it is optional to use the Inner Join keyword with the query.
MySQL Inner Join Example:
Let us first create two tables "students" and "technologies" that contains the following data:
Table: student
Table: technologies
To select records from both tables, execute the following query:
SELECT students.stud_fname, students.stud_lname, [Link], [Link] FROM
students
INNER JOIN technologies
ON students.student_id = technologies.tech_id;
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 2
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
After successful execution of the query, it will give the following output.
MySQL Left Outer Join:
The LEFT OUTER JOIN returns all rows from the left hand table specified in the ON
condition and only those rows from the other table where the join condition is fulfilled.
Syntax:
SELECT columns FROM table1
LEFT [OUTER] JOIN table2
ON [Link] = [Link];
Image representation:
Let's take an example:
Consider two tables "officers" and "students", having the following data.
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 3
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
Execute the following query:
SELECT officers.officer_name, [Link], students.course_name FROM officers
LEFT JOIN students
ON officers.officer_id = students.student_id;
Output:
MySQL Right Outer Join:
The MySQL Right Outer Join returns all rows from the RIGHT-hand table specified in the
ON condition and only those rows from the other table where he join condition is fulfilled.
Syntax:
SELECT columns FROM table1
RIGHT [OUTER] JOIN table2
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 4
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
ON [Link] = [Link];
Image representation:
Let's take an example:
Consider two tables "officers" and "students", having the following data.
Execute the following query:
SELECT officers.officer_name, [Link], students.course_name, students.student_name
FROM officers
RIGHT JOIN students
ON officers.officer_id = students.student_id;
Output:
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 5
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
***Joins Query in Python***
Create Two Tables for Staff and Stud in one db
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 6
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
Example1: Inner Join
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 7
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
Output
Example2: Outter Join (or) Left Join
Output
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 8
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
CRUD (Create Read Update Delete) Application
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 9
PYTHON –Data Manipulation Joins- Inner Join, Left, Right Join & CRUD appl
Output
By E. V. VIJAYARAJ [Link]., [Link]., M.C.A., [Link]., [Link]., Page 10