Skip to main content

Joins​

The join statement is an SQL statement used to combine rows from two or more tables based on related columns. This is important in relational databases, where data is spread across different tables. By using JOINs, we can retrieve data across tables in a single query.

Refer to the details here and get the setup ready before testing the samples

INNER JOIN​

An INNER JOIN returns only the rows where there is a match in both tables.

Example:

SELECT EMP.EMP_ID, EMP.NAME, EMP.DEPARTMENT, 
MENTOR.SKILLS, MENTOR.MENTOR_ID 
FROM EMP 
INNER JOIN MENTOR ON EMP.EMP_ID = MENTOR.EMP_ID;

Explanation: This query retrieves only employees (EMP) who are also mentors (MENTOR). If an employee exists in EMP but does not have a corresponding entry in MENTOR, they will not appear in the result.

LEFT JOIN (or LEFT OUTER JOIN)​

A LEFT JOIN returns all rows from the left table (EMP), and matching rows from the right table (MENTOR). If there is no match, NULL values are returned for columns from the right table.

Example

SELECT EMP.EMP_ID, EMP.NAME, EMP.DEPARTMENT, 
MENTOR.SKILLS, MENTOR.MENTOR_ID  
FROM EMP 
LEFT JOIN MENTOR ON EMP.EMP_ID = MENTOR.EMP_ID;

Explanation: This query retrieves all employees from EMP, even if they are not mentors. If an employee has no corresponding record in MENTOR, the SKILLS column will show NULL.

RIGHT JOIN (or RIGHT OUTER JOIN)​

A RIGHT JOIN returns all rows from the right table (MENTOR), and matching rows from the left table (EMP). If there is no match, NULL values are returned for columns from the left table. Example

SELECT EMP.EMP_ID, EMP.NAME, EMP.DEPARTMENT, 
MENTOR.SKILLS, MENTOR.MENTOR_ID  
FROM EMP 
RIGHT JOIN MENTOR ON EMP.EMP_ID = MENTOR.EMP_ID;

Explanation: This query retrieves all mentors from MENTOR, even if they are not employees. If a mentor does not have a matching entry in EMP, the NAME and DEPARTMENT columns will show NULL.

FULL OUTER JOIN​

A FULL OUTER JOIN returns all rows when there is a match in either table. If there is no match, NULL values are returned for missing columns.

SELECT EMP.EMP_ID, EMP.NAME, EMP.DEPARTMENT, 
MENTOR.SKILLS, MENTOR.MENTOR_ID   
FROM EMP 
FULL OUTER JOIN MENTOR ON EMP.EMP_ID = MENTOR.EMP_ID;

Explanation: This query retrieves all employees and all mentors. If an employee is not a mentor, the SKILLS column will be NULL. If a mentor does not have an employee record, the NAME and DEPARTMENT columns will be NULL.

Preparation Data Before Running queries​

First insert 10 rows in EMP table

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5001, 'Alice', '9876543210', 50000, 'HR');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5002, 'Bob', '8765432109', 60000, 'Research');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5003, 'Charlie', '7654321098', 55000, 'Sales');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5004, 'David', '6543210987', 62000, 'Admin');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5005, 'Eve', '5432109876', 58000, 'IT');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5006, 'Frank', '4321098765', 61000, 'HR');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5007, 'Grace', '3210987654', 57000, 
'Research');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5008, 'Hank', '2109876543', 63000, 
'Sales');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5009, 'Ivy', '1098765432', 52000, 
'Admin');

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY, DEPARTMENT) 
VALUES (5010, 'Jack', '1987654321', 59000, 'IT');

Add 10 rows to mentor table. With 2 Rows using same EMP. This is ensure we don't have all EMP mapped to all Mentors

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5001, 'Java');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5002, 'Python');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5003, 'SQL');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5004, 'C++');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5005, 'JS');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5005, 'DevOps');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5006, 'Java');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5007, 'ML');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5008, 'JS');

INSERT INTO MENTOR (EMP_ID, SKILLS) 
VALUES (5008, 'Cloud');

Once mentor rows are created we will delete first 2 rows in EMP to have some orphaned rows of Mentor.

DELETE FROM EMP WHERE EMP_ID = 5001;
DELETE FROM EMP WHERE EMP_ID = 5002;