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
Top comments (2)
Great Content! please try to explain normalization as well for the next time
Sure!!