LAB Manual-04
Database Management System Lab
Topic SQL JOIN Operations
Definition: What is a JOIN?
A JOIN clause is used in SQL to combine rows from two or more tables
based on a related column between them.
Types of JOINs
Type of JOIN Description
INNER JOIN Returns only the matching rows from both tables.
LEFT JOIN Returns all rows from the left table, and matched rows from the right table. Unmatched r
will have NULLs.
RIGHT JOIN Returns all rows from the right table, and matched rows from the left table.
FULL JOIN Returns all rows from both tables. Unmatched rows get NULLs.
(Some databases like MySQL do not support it directly.)
CROSS JOIN Returns the Cartesian product of both tables
(every row of A with every row of B).
Example Schema Used
Employee(Employee_ID, Employee_Name, Department_ID)
Department(Department_ID, Department_Name)
Project(Project_ID, Project_Title, Department_ID)
Lab Work: Join Queries
INNER JOIN – Employee with Department Name
Display each employee along with their department name.
SELECT e.Employee_ID, e.Employee_Name, d.Department_Name
FROM Employee e
INNER JOIN Department d ON e.Department_ID = d.Department_ID;
LEFT JOIN – All Employees (Including those with No Department)
Show all employees and their department names. Show NULL for those
without departments.
SELECT e.Employee_ID, e.Employee_Name, d.Department_Name
FROM Employee e
LEFT JOIN Department d ON e.Department_ID = d.Department_ID;
RIGHT JOIN – All Departments (Including those without Employees)
Show all departments and any employees they may have.
SELECT e.Employee_ID, e.Employee_Name, d.Department_Name
FROM Employee e
RIGHT JOIN Department d ON e.Department_ID = d.Department_ID;
FULL OUTER JOIN – All Employees and Departments
Combine both left and right joins to show all employees and all
departments.
MySQL doesn't support FULL OUTER JOIN directly. You can
simulate it using UNION.
SELECT e.Employee_ID, e.Employee_Name, d.Department_Name
FROM Employee e
LEFT JOIN Department d ON e.Department_ID = d.Department_ID
UNION
SELECT e.Employee_ID, e.Employee_Name, d.Department_Name
FROM Employee e
RIGHT JOIN Department d ON e.Department_ID = d.Department_ID;
CROSS JOIN – Employee with Project
Show every possible combination of employees and projects (Cartesian
product).
SELECT e.Employee_Name, p.Project_Title
FROM Employee e
CROSS JOIN Project p;
JOIN with 3 Tables – Employees working on a Project through Department
Show all employees, their department, and projects in the same
department.
SELECT
e.Employee_Name,
d.Department_Name,
p.Project_Title
FROM Employee e
JOIN Department d ON e.Department_ID = d.Department_ID
JOIN Project p ON d.Department_ID = p.Department_ID;
Lab Instructions:
● Insert sample data in each table.
● Run the queries and observe the output.
● Try modifying JOIN conditions and note changes.
● Test with NULL values in foreign keys to see join behavior.
Lab Report Task
Experiment Queries:
● Experiment-01: List of employees working in the same project
● Experiment-02: List of employees along with the department names
● Experiment-03: Total hourly rate for each project
● Experiment-04: Find the number of projects in each departments
● Experiment-05: Find the total project budget in each departments
