SY Experiment No 16: Implement SQL queries for Inner and Outer Join

 Experiment No 16: Implement SQL queries for Inner and Outer Join

Result

The experiment was successfully executed. Different types of SQL Joins (Equi, Non-Equi, Self, and Outer Joins) were implemented in MySQL and their outputs were verified.

---

Q1. Define Join. List types of Joins.

Answer:
A Join in SQL is used to combine rows from two or more tables based on a related column between them. It helps to retrieve meaningful data from multiple tables.

Types of Joins in MySQL:

1. Equi-Join (Inner Join) – Uses = operator on common columns.

2. Non Equi-Join – Uses operators like <, >, <=, >=, != instead of =.

3. Self-Join – Joins a table with itself.

4. Outer Join – Retrieves matched and unmatched rows:

   • LEFT OUTER JOIN

   • RIGHT OUTER JOIN

   • (FULL OUTER JOIN is not directly supported in MySQL, but can be simulated using UNION).

Example:

CREATE TABLE dept (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

CREATE TABLE emp (
  emp_id INT PRIMARY KEY,
  ename VARCHAR(50),
  dept_id INT,
  salary INT,
  FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);

INSERT INTO dept VALUES
(1, 'HR'), (2, 'IT'), (3, 'Finance');

INSERT INTO emp VALUES
(101, 'Riya', 1, 25000),
(102, 'Pranav', 2, 30000),
(103, 'Mansi', NULL, 22000);

-- Example of Equi-Join
SELECT e.emp_id, e.ename, d.dept_name
FROM emp e
INNER JOIN dept d ON e.dept_id = d.dept_id;

---

Q2. Describe Outer Join.

Answer:
An Outer Join returns all rows from one table and the matched rows from another table. If there is no match, NULL is returned for missing values.

Types in MySQL:

1. LEFT OUTER JOIN – Returns all rows from left table and matched rows from right table.

2. RIGHT OUTER JOIN – Returns all rows from right table and matched rows from left table.

3. FULL OUTER JOIN – Not directly supported in MySQL, but can be implemented using UNION of LEFT and RIGHT JOIN.

Example:

-- LEFT OUTER JOIN
SELECT e.emp_id, e.ename, d.dept_name
FROM emp e
LEFT OUTER JOIN dept d
ON e.dept_id = d.dept_id;

-- RIGHT OUTER JOIN
SELECT e.emp_id, e.ename, d.dept_name
FROM emp e
RIGHT OUTER JOIN dept d
ON e.dept_id = d.dept_id;

-- FULL OUTER JOIN (simulated in MySQL)
SELECT e.emp_id, e.ename, d.dept_name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_id, e.ename, d.dept_name
FROM emp e
RIGHT JOIN dept d ON e.dept_id = d.dept_id;

Output (example):

emp_id	ename	dept_name
101	Riya	HR
102	Pranav	IT
103	Mansi	NULL
NULL	NULL	Finance

---

Exercise:

Step 1: Create Tables

-- Department Table
CREATE TABLE dept (
  deptno INT PRIMARY KEY,
  dname VARCHAR(50),
  loc VARCHAR(50)
);

-- Employee Table
CREATE TABLE emp (
  empno INT PRIMARY KEY,
  ename VARCHAR(50),
  deptno INT,
  salary INT,
  mgr INT,
  FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

---

Step 2: Insert Data

-- Department Data
INSERT INTO dept VALUES
(10, 'HR', 'Mumbai'),
(20, 'Sales', 'Pune'),
(30, 'IT', 'Bengaluru'),
(40, 'Finance', 'Delhi');

-- Employee Data
INSERT INTO emp VALUES
(101, 'Nikhil', 20, 35000, 105),
(102, 'Priya', 10, 28000, 106),
(103, 'Amit', 30, 30000, 106),
(104, 'Riya', 20, 32000, 105),
(105, 'Sumit Patil', 30, 50000, NULL),  -- Manager
(106, 'Meena', 40, 45000, NULL);

---

Step 3: Queries for Given Exercise

Q1. Display employee Nikhil’s employee number, name, department number, and department location.

SELECT e.empno, e.ename, e.deptno, d.loc
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.ename = 'Nikhil';

✅ Output:

empno	ename	deptno	loc
101	Nikhil	20	Pune

---

Q2. Display the list of employees who work in the sales department.

SELECT e.empno, e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.dname = 'Sales';

✅ Output:

empno	ename	dname
101	Nikhil	Sales
104	Riya	Sales

---

Q3. Display the list of employees who do not work in the sales department.

SELECT e.empno, e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.dname <> 'Sales';

✅ Output:

empno	ename	dname
102	Priya	HR
103	Amit	IT
105	Sumit Patil	IT
106	Meena	Finance

---

Q4. Display the employee names and salary of all employees who report to Sumit Patil.

SELECT e.ename, e.salary
FROM emp e
JOIN emp m ON e.mgr = m.empno
WHERE m.ename = 'Sumit Patil';

✅ Output:

ename	salary
Nikhil	35000
Riya	32000

---