SQL Sorting and Joining Using Pymysql
All about SQL Sorting and Joining Queries.
Sorting
- In order to sort a table, we need to use the ORDER BY Clause, in order to get the results sorted based on certain columns, we need to use this.
- Usage of asc or desc has to be defined to have the results in ascending or descending orders. Default value being asc (ascending).
- Command to sort a table is given as:
- SELECT column1, column2, ...FROM table_name ORDER BY column1, column2, ... ASC|DESC;
import pymysql
#Connect to the database
connection = pymysql.connect(host='localhost',
user='root',
password='@sadhguru123',
database='employee',
cursorclass=pymysql.cursors.DictCursor)
with connection:
with connection.cursor() as cursor:
#Create Table
sql = """CREATE TABLE emp5 (EmployeeName VARCHAR(20) COLLATE utf8_bin NOT NULL,
EmployeeID INT(6) NOT NULL,
ManagerName VARCHAR(20) COLLATE utf8_bin NOT NULL,
Division INT(1) NOT NULL)"""
cursor.execute(sql)
connection.commit()
with connection:
with connection.cursor() as cursor:
#Create Table
sql = """CREATE TABLE emp6 (EmployeeName VARCHAR(20) COLLATE utf8_bin NOT NULL,
EmployeeID INT(6) NOT NULL,
Designation VARCHAR(20) COLLATE utf8_bin NOT NULL,
cardNo INT(6) NOT NULL)"""
cursor.execute(sql)
connection.commit()
with connection:
with connection.cursor() as cursor:
sql = """INSERT INTO emp5 (EmployeeName,EmployeeID,ManagerName,Division)
VALUES ('Sita Poudyal', 2212, 'Rabin Silwal',0),
('Ram Khadka', 2211, 'Sabin Devkota', 1),
('Hari Dhakal', 2213, 'Subin Khadka', 0),
('Nabin Adhikari', 2208, 'Rabin Silwal', 1),
('Ramesh Bista', 2209, 'Sabin Devkota', 0)"""
cursor.execute(sql)
connection.commit()
number_of_rows= cursor.execute("""SELECT * FROM emp5""")
rows = cursor.fetchall()
for row in rows:
print (row)
with connection:
with connection.cursor() as cursor:
sql = """INSERT INTO emp6 (EmployeeName,EmployeeID,Designation,CardNo)
VALUES ('Rita Poudyal', 2212, 'Salesgirl',111),
('Rubin Khadka', 2211, 'Technician', 212),
('Ajay Dhakal', 2213, 'Manager', 211),
('Bikash Adhikari', 2208, 'CEO', 777),
('Ramana Bista', 2209, 'Software Engineer', 444)"""
cursor.execute(sql)
connection.commit()
rows= cursor.execute("""SELECT * FROM emp6""")
rows = cursor.fetchall()
for row in rows:
print (row)
with connection:
with connection.cursor() as cursor:
sql = """SELECT * FROM emp5 ORDER BY EmployeeID ASC"""
cursor.execute(sql)
connection.commit()
#Selecting all the rows and columns from table.
rows = cursor.fetchall()
for row in rows:
print (row)
We can see that the results are sorted in ascending order.
Joins
-
In most of the real world problems, we might need data from multiple tables, that’s where Joins comes into picture.
Left Join
- The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
- Left Join syntax is given as: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
with connection:
with connection.cursor() as cursor:
sql = """SELECT emp5.EmployeeName, emp6.EmployeeName FROM emp5 LEFT JOIN emp6 ON emp5.EmployeeID = emp6.EmployeeID"""
cursor.execute(sql)
connection.commit()
#Selecting all the rows and columns from table.
rows = cursor.fetchall()
for row in rows:
print (row)
Right Join
- RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. For the rows for which there is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
-
Syntax for Right Join
- SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;
with connection:
with connection.cursor() as cursor:
sql = """SELECT emp6.EmployeeName, emp5.EmployeeName FROM emp6 RIGHT JOIN emp5 ON emp6.EmployeeID = emp5.EmployeeID"""
cursor.execute(sql)
connection.commit()
#Selecting all the rows and columns from table.
rows = cursor.fetchall()
for row in rows:
print (row)
Inner Join
- The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same.
- Syntax for Inner Join:
- SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;
with connection:
with connection.cursor() as cursor:
sql = """SELECT emp6.EmployeeName, emp6.Designation, emp5.EmployeeName, emp5.ManagerName FROM emp6 INNER JOIN emp5 ON emp6.EmployeeID = emp5.EmployeeID"""
cursor.execute(sql)
connection.commit()
#Selecting all the rows and columns from table.
rows = cursor.fetchall()
for row in rows:
print (row)
Full Join
- FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.
- Syntax for full join:
- SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 FULL OUTER JOIN table2 ON table1.matching_column = table2.matching_column;
with connection:
with connection.cursor() as cursor:
sql = """SELECT * FROM emp5 FULL OUTER JOIN emp6 ON emp5.EmployeeID = emp6.EmployeeID"""
cursor.execute(sql)
connection.commit()