Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is Joins in DBMS?
3.
Types of Joins in DBMS
4.
Inner Join
4.1.
1. Equi-join
4.2.
2. Self Join
4.3.
3. Non-equi Join
5.
Outer Join
5.1.
1. Left outer join
5.2.
2. Right outer join
5.3.
3. Full outer join
6.
Frequently Asked Questions
6.1.
What are JOINs in DBMS?
6.2.
What are the 4 types of database JOINs?
6.3.
What is a full join in DBMS?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

Joins in DBMS

Author Soumya Pandey
2 upvotes
gp-icon
Dbms - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up

Introduction

The need for database management arises because data is critical to business operations in most modern organizations. Databases store, organize, and manage large amounts of data, making it easier to access and use in decision-making processes.

Joins in DBMS is essential because it allows the users to combine data from multiple tables. For example, suppose an organization has a customer and order tables. In that case, we can combine information from both tables to see which customers have placed orders and what products they have ordered.

joins in dbms

In this article, we will be learning more about Joins in DBMS.

What is Joins in DBMS?

Joins are the statements that are used to combine the rows of multiple tables based on the related column between them. In SQL, JOIN is the clause that can be used to achieve the feature of joins.

Here is an example to understand the joins in DBMS:

TABLE A

STUDENT_ID AGE GENDER
8080 20 Male
8081 19 Female
8082 21 Male

TABLE B

STUDENT_ID STUDENT_NAME PHONE
1 Kevin XXXXXXXXXX
2 Sarah XXXXXXXXXX
3 Bryan XXXXXXXXXX

In the table, A and B, 'Student_ID' are the same means this column of Table A refers to Table B. So If we want to join these tables, we can join on the basis of ‘STUDENT_ID’. Here is what a result table will be looks like after joining:

STUDENT_ID STUDENT_NAME GENDER
8080 Kevin Male
8081 Sarah Female
8082 Bryan Male

In the above result table, we joined Table A and B on the basis of the ‘STUDENT_ID’ column and took some of the other columns, such as STUDENT_NAME and GENDER.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Types of Joins in DBMS

In a relational database management system, combining different types of tables is done using different Joins. The main types of joins are:

Inner Join

An Inner Join returns only the matching rows between two tables based on the specified join condition. In other words, it returns only those records with a match in both tables.

Inner join

Here is an example of Inner Join

Suppose we have two tables, one for customers and one for orders, with the following data:

Customers Table

Customer_ID Customer_Name customer_email
101 John john@email.com
102 Jane jane@email.com
103 Parag  parag@email.com
104 Bob bob@email.com

Orders Table

Order_ID ID Order_Date
1 101 2023-01-01
2 102 2023-01-02
3 103  2023-01-03
4 104 2023-01-04

If we want to see which customers have placed orders and what products they ordered, we can use an Inner Join on the CustomerID column

SELECT Customers.customer_Name, Orders.order_date FROM Customers INNER JOIN Orders ON Customers.Customer_ID = Orders.ID;


 Result of the above query is:

Output

This inner join returns only the matching rows with a match in the Customers and Orders table based on the CustomerID column. As a result, we get a list of customers who have placed orders and what products they ordered.

The further division of Inner join is as follows.

1. Equi-join

An equi-join operation matches rows between two tables based on the equality of values in a standard column. It is the most common join type and is useful when combining data from two tables based on a specific condition. The basic format of an Equi-join is as follows.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;


In this format, 'columns' refer to the specific columns we want to retrieve from the tables. 'table1' and 'table2' are the names of the tables we want to join. 'column' refers to the common column between the two tables that we want to use to match rows. The 'ON' clause specifies the join condition, the equality of values in the common column. 

The 'inner join' keyword specifies that we want to perform an inner join, which will only return matching rows from both tables. 

2. Self Join

Self-join is a join operation where a table is joined with itself using a common column. This feature can be useful when we need to compare data within a single table, such as when we want to find all pairs of employees with the same manager.

The basic format of Self-join is as follows.

SELECT columns
FROM table1 t1
INNER JOIN table1 t2
ON t1.column = t2.column;


In this format, 'columns' refer to the specific columns we want to retrieve from the table. 'table1' is the name of the table we want to join. We give the table two different aliases, 't1' and 't2', to distinguish between the two instances of the same table.

'column' refers to the common column between the two instances of the table that we want to use to match rows. The 'on' clause specifies the join condition, which can include other conditions besides matching the common column. 

We use an 'INNER JOIN' to combine rows from the same table, where a row from 't1' is matched with a row from 't2' based on the join condition. This step creates a virtual table that combines data from two instances of the same table. 

3. Non-equi Join

Non-equi join is a join operation where rows from two tables are matched based on a condition that is not an equality comparison. This feature can be useful when we need to join tables on a range of values or other conditions that cannot be used for a simple equality comparison.

The basic format of Non-equi join is as follows.

SELECT [column(s)]
FROM [table1]
INNER JOIN [table2]
ON [table1.column_name] [comparison_operator] [table2.column_name];


In this format, 'column(s)' refers to the column or columns we want to select from the resulting table. 'table1' and 'table2' are the names of the tables you want to join. The 'INNER JOIN' keyword specifies that we want to perform an inner join on the two tables. 

The join condition is specified using the 'ON' keyword, which includes the non-equality operator as a comparison operator. The comparison operator can be any of the following operators: '<,' '<=,' '>,' '>=,' '<>,' '!='.

Recommended Topic - Specialization and Generalization in DBMS and Checkpoint in DBMS

Outer Join

An outer join is a join that returns all the rows from one table and matching rows from another. When there is no match in the second table, the result will have NULL values for all the columns of the second table. 

The further divisions into outer join are as follows.

1. Left outer join

Left outer join

Supposing we have two tables, "Customers" and "Orders." The "Customers" table contains customer information such as CustomerID, name, and email. In contrast, the "Orders" table contains order information such as order ID, customer ID, and order date.

Customers Table

Customer_ID Customer_Name Customer_Email
101 John john@email.com
102 Jane jane@email.com
103 Parag parag@email.com

Orders Table

Order_ID ID Order_Date
1 101 2023-01-01
2 102 2023-02-15
3 103 2023-01-03

To get all the customers and their corresponding orders (if any), we can use a left outer join on the "Customers" table with the "Orders" table on the "CustomerID" column. The SQL query for this join would be: 

SELECT Customers.Customer_ID, Customers.Customer_Name, Orders.Order_date, Orders.Order_ID FROM Customers
    -> LEFT JOIN Orders
    -> ON Customers.Customer_ID = Orders.ID;


The result of this query would be:

Output

As you can see, this left outer join returns all the customers from the "Customers" table and their corresponding orders (if any) from the "Orders" table. In this case, customer John has two orders, Jane has one, and Parag still needs orders. The NULL values in the last row indicate no match for the customer ID 3 in the "Orders" table.

2. Right outer join

Right outer join

The right outer join is a join that returns all the rows from the right table and the contents of the matching rows from the left table. If there is no match, it returns NULL for the columns of the left table. 

Here's an example of a right outer join:

Consider two tables, "orders" and "customers," with the following data.

Orders Table

Order_id Id Order_date
1 101 2023-01-01
2 102 2023-01-02
3 103 2023-01-03
4 104 2023-01-04

Customers Table

Customer_id Customer_name Customer_email
101 John john@email.com
102 Jane jane@email.com
103 Bob bob@email.com
104 Parag parag@email.com

The following SQL statement can be used to perform a right outer join on these tables.

SELECT *
FROM orders
RIGHT JOIN customers
ON orders.id = customers.customer_id;


The result of this query would be

Output

As you can see, the "customers" table returns all the rows, even if there is no matching customer in the "orders" table. If there is no match, the columns for the "orders" table will be NULL.

3. Full outer join

Full outer join

Full outer join returns all the rows from both tables, with NULL values for non-matching rows.

For example, to retrieve information from two tables, 'Orders' and 'Customers,' where you want to see all data of orders and customers, even those that do not match, you can use full outer join as follows. Since MySQL does not support full outer join, we will be using Union All to get full details of tables ‘Orders' and ‘Customers.'

SELECT Customers.Customer_ID, Customers.Customer_Name,
	customers.customer_email,
	Orders.ID, orders.order_id, Orders.Order_Date
 	FROM Customers 
 	LEFT JOIN Orders ON Customers.Customer_ID = Orders.ID 
 	UNION ALL  
 
 SELECT Customers.Customer_ID, Customers.Customer_Name,
 	Customers.customer_email, 
 	Orders.ID, Orders.order_id , Orders.Order_Date 
 	FROM Customers
  	RIGHT JOIN Orders 
  	ON Customers.Customer_ID = Orders.ID
  	WHERE CUSTOMERS.CUSTOMER_ID is NULL;


The result of the above query is:

Output

Frequently Asked Questions

What are JOINs in DBMS?

Joins are statements that are used to combine the rows of multiple tables based on the related column between them. JOIN clause can be used to join the multiple tables in SQL.

What are the 4 types of database JOINs?

The four types of database JOINs are INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).

What is a full join in DBMS?

A full join in DBMS retrieves all matching rows from both tables and includes unmatched rows, producing a result set with NULL values for non-matching columns.

Conclusion

In conclusion, joins in DBMS are an essential concept in relational databases that allow us to combine data from two or more tables.

When using joins, it is vital to ensure that the columns used to join the tables have the same data type and values and to choose the correct join type for the desired result. Joining tables in DBMS is a powerful tool that can help us unlock the potential of our relational databases. By understanding how joins work and how to use them effectively, we can take full advantage of the data available to us and make better decisions based on that data.

To learn more about DBMS, visit the below links: 


You may refer to our Guided Path on Code Studios to enhance your skill set on DSA and many more. Check out essential interview questions, practice our available mock tests, and more!

Previous article
Interview Questions on Databases
Next article
Conflict Serializability in DBMS
Guided path
Free
gridgp-icon
Dbms - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass