Joins in DBMS

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.

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:

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

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:

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

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

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

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: 

• Fundamentals of Database System: DBMS Tutorial
• Types of DBMS
• Introduction to Outer Joins in DBMS
• Recursive Relationship in DBMS
• DBMS Architecture
• Introduction to DBMS

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!