DEV Community

Cover image for Joins in DBMS
Aryan Dev Shourie
Aryan Dev Shourie

Posted on

5 3 3 3 3

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.

cartesian product

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

Project Table -

project

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

Syntax :

SELECT table1.column1, table1.column2, table2.column1,....  
FROM table1   
INNER JOIN table2  
ON table1.matching_column = table2.matching_column;  
Enter fullscreen mode Exit fullscreen mode

Example Query :

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   
FROM EMPLOYEE  
INNER JOIN PROJECT  
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;  
Enter fullscreen mode Exit fullscreen mode

Output :

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

left pic

Syntax :

SELECT table1.column1, table1.column2, table2.column1,....  
FROM table1   
LEFT JOIN table2  
ON table1.matching_column = table2.matching_column;
Enter fullscreen mode Exit fullscreen mode

Example Query :

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   
FROM EMPLOYEE  
LEFT JOIN PROJECT  
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID; 
Enter fullscreen mode Exit fullscreen mode

Output :

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

rightpic

Syntax :

SELECT table1.column1, table1.column2, table2.column1,....  
FROM table1   
RIGHT JOIN table2  
ON table1.matching_column = table2.matching_column;
Enter fullscreen mode Exit fullscreen mode

Example Query :

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   
FROM EMPLOYEE  
RIGHT JOIN PROJECT  
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Enter fullscreen mode Exit fullscreen mode

Output :

right join

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

Syntax :

SELECT table1.column1, table1.column2, table2.column1,....  
FROM table1   
FULL JOIN table2  
ON table1.matching_column = table2.matching_column;
Enter fullscreen mode Exit fullscreen mode

Example Query :

SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT   
FROM EMPLOYEE  
FULL JOIN PROJECT   
ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Enter fullscreen mode Exit fullscreen mode

Output :

outer 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

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 🕒

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (2)

Collapse
 
navyaarora01 profile image
Navya Arora •

Great Content! please try to explain normalization as well for the next time

Collapse
 
aryan_shourie profile image
Aryan Dev Shourie •

Sure!!

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay