Table Joins – MySQL

JOIN Clause Of SQL:

Join allows you to combine two or more tables in SQL, based on the related column between them. It’s important to keep in mind that for this operation to work, there needs to be a common column in both tables. Based on this application of join there are three types of join:

• Records produced by matching columns are returned by an INNER JOIN. Both JOIN and INNER JOIN function the same way.Syntax:

SELECT column1, column2...
FROM tablename
JOIN tablename ON condition;

• LEFT JOIN gives those records from table 1 removing exclusive contents of 2. Syntax:

SELECT column1, column2...
FROM tablename
LEFT JOIN tablename ON condition;

• RIGHT JOIN gives all records from table 2 after removing exclusive records of 1. Syntax:

SELECT column1, column2...
FROM tablename
RIGHT JOIN tablename ON condition;

Example:  Inner join 

import mysql.connector

# Connecting to the database
mydb = mysql.connector.connect(
host ='localhost',
database ='College',
user ='root',
)

cs = mydb.cursor()

# STUDENT and STudent are
# two different database
statement ="SELECT S.NAME from Student S JOIN \
Student on S.Roll_no = Student.Roll_no"

cs.execute(statement)
result_set = cs.fetchall()

for x in result_set:
	print(x)

Example:  Left join 

import mysql.connector

# Connecting to the database
mydb = mysql.connector.connect(
host ='localhost',
database ='College',
user ='root',
)

cs = mydb.cursor()

# STUDENT and STudent are
# two different database
statement ="SELECT S.Name from STUDENT S\
LEFT JOIN Student s ON S.Roll_no = s.Roll_no"

cs.execute(statement)
result_set = cs.fetchall()

for x in result_set:
	print(x)

Example:  Right join 

import mysql.connector

# Connecting to the database
mydb = mysql.connector.connect(
host ='localhost',
database ='College',
user ='root',
)

cs = mydb.cursor()

# STUDENT and STudent are
# two different database
statement ="SELECT S.Name from STUDENT S RIGHT \
JOIN Student s ON S.Roll_no = s.Roll_no"

cs.execute(statement)
result_set = cs.fetchall()

for x in result_set:
	print(x)

The above codes will output the result according to the used join type.

Note: also read about Limit Clause – MySQL

Follow Me

Please follow me to read my latest post on programming and technology if you like my post.

https://www.instagram.com/coderz.py/

https://www.facebook.com/coderz.py