DATABASE
MANAGEMENT
SYSTEMS
Data Manipulation
SQL Joins (Inner, Left,
Right and Full Join)
Information Technology
Science and Technology
Department
Ilocos Sur Community College
Overview
Data Manipulation?
• Data manipulation in Database Management Systems (DBMS) refers to the
Database Management Systems
process of retrieving, inserting, updating, and deleting data in a database
W h y i t i s i m p o r t a n t?
• data manipulation in DBMS is essential for managing and leveraging
data assets effectively, enabling organizations to make informed
decisions, improve efficiency, and drive innovation.
• Data Retrieval: It allows users to extract specific information from large
datasets efficiently.
• Data Modification: DBMS enables users to add, update, or delete data
Data Manipulation
as needed.
• Data Integrity: Proper data manipulation operations help maintain the
integrity of the database.
• Data Security: DBMS often provides mechanisms to control access to
data and perform operations based on user privileges.
ILOCOS SUR COMMUNITY COLLEGE
Overview
W h y i t i s i m p o r t a n t?
• Data Analysis: By manipulating data within the database, users can
Database Management Systems
perform various analytical tasks, such as aggregating data, calculating
statistics, and identifying patterns.
• Data Maintenance: Data manipulation operations are essential for
maintaining the quality of data over time.
• Data Integration: In modern applications, data is often distributed
across multiple databases or systems.
Data Manipulation
ILOCOS SUR COMMUNITY COLLEGE
SQL Joins (Inner, Left, Right and Full Join)
SQL Join operation combines data or rows from two or more tables based on a
common field between them.
Database Management Systems
SQL JOIN
SQL JOIN clause is used to query and access data from multiple tables by
establishing logical relationships between them. It can access data from
multiple tables simultaneously using common key values shared across
different tables.
Data Manipulation
ILOCOS SUR COMMUNITY COLLEGE
SQL JOIN Example
Consider the two tables below as follows:
-- Create the Student table -- Insert sample data into the Student
Database Management Systems
table
CREATE TABLE Student ( INSERT INTO Student (StudentID,
StudentID INT PRIMARY KEY, FirstName, LastName, Age)
FirstName VARCHAR(50), VALUES
(1, 'John', 'Doe', 20),
LastName VARCHAR(50), (2, 'Jane', 'Smith', 22),
Age INT (3, 'Michael', 'Johnson', 21),
); (4, 'Emily', 'Brown', 23);
-- Insert sample data into the
-- Create the StudentCourse table StudentCourse table
CREATE TABLE StudentCourse ( INSERT INTO StudentCourse
EnrollmentID INT PRIMARY KEY, (EnrollmentID, StudentID, CourseID,
Data Manipulation
Grade)
StudentID INT, VALUES
CourseID INT, (1, 1, 101, 'A'),
Grade VARCHAR(2) (2, 2, 102, 'B'),
); (3, 3, 103, 'C'),
(4, 4, 101, 'B');
ILOCOS SUR COMMUNITY COLLEGE
SQL JOIN Example
We can perform a JOIN operation using the given SQL query:
SELECT s.FirstName, s.LastName, s.Age, sc.Grade
Database Management Systems
FROM Student s
INNER JOIN StudentCourse sc ON s.StudentID = sc.StudentID;
Output:
Data Manipulation
ILOCOS SUR COMMUNITY COLLEGE
Types of JOIN in SQL
We can perform a JOIN operation using the given SQL query:
There are many types of Joins in SQL. Depending on the use case, you can
Database Management Systems
use different type of SQL JOIN clause. Here are the frequently used SQL JOIN
types:
1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
5. NATURAL JOIN
Data Manipulation
ILOCOS SUR COMMUNITY COLLEGE
SQL 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
Database Management Systems
rows from both the tables where the condition satisfies i.e value of the common
field will be the same.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
Data Manipulation
ILOCOS SUR COMMUNITY COLLEGE
INNER JOIN Example
Let’s look at the example of INNER JOIN clause, and understand it’s working.
This query will show SELECT s.FirstName, s.LastName, s.Age, sc.CourseID
Database Management Systems
the names and age FROM Student s
of students enrolled INNER JOIN StudentCourse sc ON s.StudentID =
in different courses. sc.StudentID;
Output:
Data Manipulation
ILOCOS SUR COMMUNITY COLLEGE
SQL LEFT JOIN
LEFT JOIN returns all the rows of the table on the left side of the join and
matches rows for the table on the right side of the join. For the rows for which
there is no matching row on the right side, the result-set will contain null. LEFT
JOIN is also known as LEFT OUTER JOIN.
Database Management Systems
SELECT table1.column1,table1.column2,table2.column1,....
Syntax FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Explanation
1. table1: First table.
2. table2: Second table
Data Manipulation
3. matching_column: Column common to both the tables.
ILOCOS SUR COMMUNITY COLLEGE
SQL LEFT JOIN
SQL query:
SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade
FROM Student s
Database Management Systems
LEFT JOIN StudentCourse sc ON s.StudentID = sc.StudentID;
Output:
Data Manipulation
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN,
both are the same. ILOCOS SUR COMMUNITY COLLEGE
SQL RIGHT JOIN | EXAMPLE
SQL query:
SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade
FROM StudentCourse sc
Database Management Systems
RIGHT JOIN Student s ON sc.StudentID = s.StudentID;
Output:
Data Manipulation
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN,
both are the same. ILOCOS SUR COMMUNITY COLLEGE
SQL 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.
Database Management Systems
Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
explanation
Data Manipulation
1. table1: First table.
2. table2: Second table
3. matching_column: Column common to
both the tables.
ILOCOS SUR COMMUNITY COLLEGE
SQL FULL JOIN | EXAMPLE
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.
Database Management Systems
Example
SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade
FROM Student s
FULL JOIN StudentCourse sc ON s.StudentID = sc.StudentID;
explanation
Data Manipulation
1. table1: First table.
2. table2: Second table
3. matching_column: Column common to
both the tables.
ILOCOS SUR COMMUNITY COLLEGE
SQL FULL JOIN | EXAMPLE
Example
SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade
Database Management Systems
FROM Student s
LEFT JOIN StudentCourse sc ON s.StudentID = sc.StudentID
UNION
SELECT s.FirstName, s.LastName, s.Age, sc.CourseID, sc.Grade
FROM Student s
RIGHT JOIN StudentCourse sc ON s.StudentID = sc.StudentID;
Data Manipulation
ILOCOS SUR COMMUNITY COLLEGE
