Joins in DBMS
Tags: sql, dbms, database
Published: 2023-05-30 16:47:50

**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.


![cartesian product](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3wf9qniq2id0wvrpq7p7.png)

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 -**

![employee](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nepocmy8dqzhm99i9t6f.png)

**Project Table -**

![project](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/psp9hyjaef02by5ago7c.png)


## 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.**


![inner pic](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ys7qbwoah93839ee8qx9.png)

**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 :**

![inner output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8fb224eheno4dhpoih11.png)

## 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.**


![left pic](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fv2xpiaju7u2ljuuiqwz.png)

**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 :**

![left output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t8v61v26eckbl0oblvfr.png)

## 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.**


![rightpic](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8bst69howghj89ovpbus.png)

**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 :**

![right join](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ajebc23yja95k23e8zn.png)

## 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.


![outerpic](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5f7dnholxqm2zzsqslns.png)

**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 :**

![outer output](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ax5by6expafh9imxkxb.png)

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

Connect with me on Linkedin :- [Linkedin](https://www.linkedin.com/in/aryandev-shourie-175025229/)

Do check out my Github for amazing projects:- [Github](https://github.com/Aryan2727-debug)

View my Personal Portfolio :- [Aryan's Portfolio](https://personal-portfolio-aryan.netlify.app/)