Skip to main content

SQL Join Operations using Python

 

Join Operation

We can combine the columns from two or more tables by using some common column among them by using the join statement.



Example

  1. import mysql.connector  
  2.   
  3. #Create the connection object   
  4. mydb = mysql.connector.connect(host = "localhost", user = "root",password = "12345sibi")  
  5.   

  7.   

IDLE :


To create a cursor object  and display existing database we need to follow the below code:




To display the elements inside the employee table we need to execute the following code:


To Display the tables:





SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;



SQL Self Join

A self join is a regular join, but the table is joined with itself.

Self Join Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Right Join

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT JOIN


Example


 As you can see in the above, It returns everything from sales table.since there is no match for the sales column elements, It returns None.

Left Join

The left join covers all the data from the left-hand side table. It has just opposite effect to the right join. 

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT JOIN

Consider the following example.

Example



SQL Self Join

A self join is a regular join, but the table is joined with itself.

Self Join Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;



Comments

Post a Comment

Popular posts from this blog

Is-A and Has-A relationships in python

  In object-oriented programming, the concept of IS-A is a totally based on Inheritance, which can be of two types Class Inheritance or Interface Inheritance. It is just like saying "A is a B type of thing". For example, Apple is a Fruit, Car is a Vehicle etc. Inheritance is uni-directional. For example, House is a Building. But Building is not a House. #Is-A relationship --> By Inheritance class  A:    def   __init__ ( self ):      self .b= 10    def   mym1 ( self ):      print ( 'Parent method' ) class  B(A):    def   mym2 ( self ):      print ( 'Child method' ) d = B() d.mym1() #output: Parent method d.mym2() #output: Child method HAS-A Relationship:  Composition(HAS-A) simply mean the use of instance variables that are references to other objects. For example Maruti has Engine, or House has Bathroom. Let’s understand...
Post a Comment
Read more

Magic Methods in Python

  What Are Dunder Methods ? In Python, special methods are a set of predefined methods you can use to enrich your classes.  They are easy to recognize because they start and end with double underscores, for example  __init__  or  __str__ . Dunder methods let you emulate the behavior of built-in types.  For example, to get the length of a string you can call  len('string') . But an empty class definition doesn’t support this behavior out of the box: These “dunders” or “special methods” in Python are also sometimes called “magic methods.” class NoLenSupport : pass >>> obj = NoLenSupport () >>> len ( obj ) TypeError : "object of type 'NoLenSupport' has no len()" To fix this, you can add a  __len__  dunder method to your class: class LenSupport : def __len__ ( self ): return 42 >>> obj = LenSupport () >>> len ( obj ) 42 Object Initialization:  __init__ "__init __ ...
Post a Comment
Read more

Inheritance and Types in Python

  Inheritance   Creating a new class from existing class is known as inheritance . The class from which features are inherited is known as base class and the class into which features are derived into is called derived class . Syntax: class  derived- class (base  class ):       < class -suite>      Inheritance promotes reusability of code by reusing already existing classes.  Inheritance is used to implement  is-a  relationship between classes.   Following hierarchy is an example representing inheritance between classes:   Single inheritance   When a derived class inherits only from syntax, the base class is called single inheritance. If it has one base class and one derived class it is called single inheritance.   Diagram     Syntax class  A:  #parent class         #some code       class  b(A):...
Post a Comment
Read more