Joins in DBMS

Database Management System (DBMS) is a software or technology used to manage data from a database. DBMS provides an interface to perform various operations like database creation, storing data in it, updating data, creating a table in the database which is used in different applications.

A DBMS Join is defined as the combination of a Cartesian Product followed by a selection process.

A Join operation pairs two tuples from different relations, if and only if a given Join condition is satisfied.
Joins are an essential operation in Database Management Systems and are used to combine data from multiple tables in order to perform complex queries and data analysis.

Types of Joins in DBMS

• INNER JOIN

• LEFT JOIN

• RIGHT JOIN

• FULL OUTER JOIN

Consider the two tables below as follows :

Employee Table -

Project Table -

1. INNER JOIN

In DBMS, the Inner Join is such type of Join that returns all rows from both the participating tables where the key record of one table is equal to key records in another table. It basically selects the records that have matching values in both tables.

Syntax :

SELECT table1.column1, table1.column2, table2.column1,....  
FROM table1   
INNER JOIN table2  
ON table1.matching_column = table2.matching_column;  

Example Query :

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   
FROM EMPLOYEE  
INNER JOIN PROJECT  
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  

Output :

LEFT JOIN

In DBMS, the Left Join includes all rows from the left table of the Join clause and the unmatched rows from the right table with NULL values for the selected columns. It basically selects all records from the left table and the matching records from the right table.

Syntax :

SELECT table1.column1, table1.column2, table2.column1,....  
FROM table1   
LEFT JOIN table2  
ON table1.matching_column = table2.matching_column;

Example Query :

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   
FROM EMPLOYEE  
LEFT JOIN PROJECT  
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID; 

Output :

RIGHT JOIN

In DBMS, the Right Join includes all rows from the right table of the Join clause and the unmatched rows from the left table with NULL values for the selected columns. It basically selects all records from the right table and the matching records from the left table.

Syntax :

SELECT table1.column1, table1.column2, table2.column1,....  
FROM table1   
RIGHT JOIN table2  
ON table1.matching_column = table2.matching_column;

Example Query :

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   
FROM EMPLOYEE  
RIGHT JOIN PROJECT  
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output :

FULL OUTER JOIN

In DBMS, the Full Outer Join includes the matching rows from the left table and the right table of the Join clause and the unmatched rows from both the left and right tables with NULL values for selected columns.

Syntax :

SELECT table1.column1, table1.column2, table2.column1,....  
FROM table1   
FULL JOIN table2  
ON table1.matching_column = table2.matching_column;

Example Query :

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   
FROM EMPLOYEE  
FULL JOIN PROJECT   
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output :

And thats it! You have successfully learnt all about the Joins in DBMS!

Connect with me on Linkedin :- Linkedin

Do check out my Github for amazing projects:- Github

View my Personal Portfolio :- Aryan's Portfolio